SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Performance 1


T-SQL Performance 1

Author
Message
OzYbOi d(-_-)b
OzYbOi d(-_-)b
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 778
good question to round out the week - tks
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 3648
SQL Kiwi (2/17/2012)
L' Eomot Inversé (2/17/2012)
I guess FORCESEEK always works, though (?).

Nothing always works :-). FORCESEEK will result in a plan with a seek operation on the hinted table (or index + columns in the extended syntax after R2 SP1), or in query compilation failure (error 8622). It cannot be used to force the seek that occurs on a partitioned heap with no indexes though (shame, it would have made for a classic QotD: "can FORCESEEK be used successfully on a table with no indexes at all..."). There is a related FORCESCAN hint (again, introduced in R2 SP1).

Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".

Oh I don't know. Anything is dangerous in the wrong hands; it's just another hint to be used when all else fails, and with due care.


Interesting but I don't think I want my developers to discover the FORCESEEK hint.
Britt Cluff
Britt Cluff
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 253
Good straightforward question. Thanks for submitting.

http://brittcluff.blogspot.com/
DBA_Dom
DBA_Dom
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 81110
I like it. I ran into this exact same issue when tuning some users queries.

It is a little interesting how poorly this implicit conversion performs since most all data used in system views, tables and procedures is nvarchar but a good portion of existing user data is still varchar.
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15684 Visits: 11355
Cliff Jones (2/17/2012)
Interesting but I don't think I want my developers to discover the FORCESEEK hint.

This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7821 Visits: 25280
SQL Kiwi (2/17/2012)
Cliff Jones (2/17/2012)
Interesting but I don't think I want my developers to discover the FORCESEEK hint.

This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.

Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is

+ 100

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
DBA_Dom
DBA_Dom
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 81110
bitbucket-25253 (2/17/2012)
SQL Kiwi (2/17/2012)
Cliff Jones (2/17/2012)
Interesting but I don't think I want my developers to discover the FORCESEEK hint.

This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.

Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is

+ 100

I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 3648
DBA_Dom (2/17/2012)
bitbucket-25253 (2/17/2012)
SQL Kiwi (2/17/2012)
Cliff Jones (2/17/2012)
Interesting but I don't think I want my developers to discover the FORCESEEK hint.

This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.

Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is

+ 100

I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.

Actually I agree also, my post was a bit tongue in cheek. But with 50 developers sometimes bad practices get copied around like a virus before you have a chance to educate or remind. Code reviews would help if only we did them more than we currently do.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14298 Visits: 12197
Cliff Jones (2/18/2012)
DBA_Dom (2/17/2012)
bitbucket-25253 (2/17/2012)
SQL Kiwi (2/17/2012)
Cliff Jones (2/17/2012)
Interesting but I don't think I want my developers to discover the FORCESEEK hint.

This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.

Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is

+ 100

I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.

Actually I agree also, my post was a bit tongue in cheek. But with 50 developers sometimes bad practices get copied around like a virus before you have a chance to educate or remind. Code reviews would help if only we did them more than we currently do.

I'm sure that making sure developers understand what features are available, where those features are most likely to be beneficial, what potential issues there are with these features, and how they should go about evaluating the pros and cons of using a feature in a situation where they are considering it is the only sensible way to go (this applies to any language, not just SQL dialects); if the time taken to educate developers about new features is sufficiently long that they may go and misuse them before the education happens, the thing to do is to fix the working system so that the education happens in a more timely fashion. Yes there need to be warnings about some features (but perhaps not as extreme as the labelling suggested in my earlier post :-D - ) - in fact as DBA_Dom suggests developers have to be told that using hints to override the optimiser is a last resort, but a new query hint (which is what we have here) hardly needs new guidance since some query hints have been around for a long time and if the developers don't yet know that query hints are a last resort that little piece of education is more than a decade overdue so something is seriously wrong.

Tom

Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 3648
L' Eomot Inversé (2/18/2012)
Cliff Jones (2/18/2012)
DBA_Dom (2/17/2012)
bitbucket-25253 (2/17/2012)
SQL Kiwi (2/17/2012)
Cliff Jones (2/17/2012)
Interesting but I don't think I want my developers to discover the FORCESEEK hint.

This is completely the wrong approach, in my opinion. I prefer to work with developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.

Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is

+ 100

I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.

Actually I agree also, my post was a bit tongue in cheek. But with 50 developers sometimes bad practices get copied around like a virus before you have a chance to educate or remind. Code reviews would help if only we did them more than we currently do.

I'm sure that making sure developers understand what features are available, where those features are most likely to be beneficial, what potential issues there are with these features, and how they should go about evaluating the pros and cons of using a feature in a situation where they are considering it is the only sensible way to go (this applies to any language, not just SQL dialects); if the time taken to educate developers about new features is sufficiently long that they may go and misuse them before the education happens, the thing to do is to fix the working system so that the education happens in a more timely fashion. Yes there need to be warnings about some features (but perhaps not as extreme as the labelling suggested in my earlier post :-D - ) - in fact as DBA_Dom suggests developers have to be told that using hints to override the optimiser is a last resort, but a new query hint (which is what we have here) hardly needs new guidance since some query hints have been around for a long time and if the developers don't yet know that query hints are a last resort that little piece of education is more than a decade overdue so something is seriously wrong.

Yes, I think you hit the nail on the head. I work at a very fast paced and very successful software company and I don’t think anyone in our organization would use a Query Hint without checking with me to be sure it was appropriate. When you are dealing with a large development team sometimes that is the level at which you have to train. You can spend a lot of time training 50 dot net programmers how to write SQL but only a percentage will listen and only a percentage will remember everything. Would I spend 10 minutes of valuable training time to discuss the FORCESEEK Hint? No. Would I spend 30 minutes showing them why they shouldn’t try to out-guess the Optimizer with query hints? Yes. So it is easy to say give everyone all the details but sometimes it just not that simple.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search