Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

T-SQL Performance 1 Expand / Collapse
Author
Message
Posted Friday, February 17, 2012 12:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
good question to round out the week - tks
Post #1254106
Posted Friday, February 17, 2012 12:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
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.
Post #1254113
Posted Friday, February 17, 2012 12:28 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good straightforward question. Thanks for submitting.

http://brittcluff.blogspot.com/
Post #1254115
Posted Friday, February 17, 2012 2:15 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 568, Visits: 75,414
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.
Post #1254206
Posted Friday, February 17, 2012 9:22 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1254271
Posted Friday, February 17, 2012 9:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1254272
Posted Friday, February 17, 2012 9:52 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 568, Visits: 75,414
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.
Post #1254274
Posted Saturday, February 18, 2012 9:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
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.
Post #1254328
Posted Saturday, February 18, 2012 7:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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 - ) - 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
Post #1254401
Posted Saturday, February 18, 2012 8:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
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 - ) - 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.
Post #1254403
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse