June 30, 2011 at 7:44 am
How often would you say you have to use index and locking hints to force SQL server to do what you want?
Here's my situation:
I've finally gotten the go-ahead to upgrade our old SQL 2000 server to SQL 2008 on some shiny new hardware. Unfortunately I won't be able to set my compatibility level to 100 just yet. One of the changes to T-SQL (in 2005 actually) is that you now have to use the keyword WITH in conjunction with index and locking hints whereas it wasn't required before. We've got index hints and (nolock) scattered throughout our codebase and they all have to be changed before I can update the compatibility level.
So I'm curious: what do you find is the normal balance between letting the query optimizer do its job and having to bang it over the head with your own hints? Is common use of those constructs a symptom of bad index choices, out-of-date stats, poorly written queries, all of the above... or is it to be expected?
June 30, 2011 at 7:47 am
you should never use them, unless you really really really know what you are doing and have exhausted every other possible method of tuning.
June 30, 2011 at 7:54 am
dean.g (6/30/2011)
How often would you say you have to use index and locking hints to force SQL server to do what you want?
Index hints: very rarely. There is usually a way to write the query that avoids naming an index specifically. I use join hints more regularly.
Isolation level hints: Never. At least not the NOLOCK or READUNCOMMITTED hints. I do use READCOMMITTEDLOCK occasionally.
Locking: There are good reasons to use granularity hints like TABLOCK now and again, and UPDLOCK can also be very useful.
Code sprinkled with index name hints and (nolock) in particular is rarely good code, in my experience at least.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 8:57 am
SQLkiwi (6/30/2011)
Code sprinkled with index name hints and (nolock) in particular is rarely good code, in my experience at least.
That was my gut feeling. We have members of the team who are utterly convinced they need to use (nolock) with every single query they write or we'll get too much table locking. As a result, nearly every SELECT written includes the (nolock) hint. In most cases we aren't worried about getting dirty or uncommited data back, so I suppose that's how the line of thinking for using (nolock) was introduced.
With respect to the index hints, however, I have seen many occasions where the query optimizer in SQL 2000 chooses a really bad index. Many times I do have to add an index hint to keep it from choosing to use the clustered index on a column not even in the query versus using an index on a column directly used in the query. It typically is the difference between a query that runs in a few seconds and one that takes several minutes. Should I expect better from the optimizer in SQL 2008, or is this something I can attribute to bad statistics, indexes, etc.?
June 30, 2011 at 9:59 am
dean.g (6/30/2011)
We have members of the team who are utterly convinced they need to use (nolock) with every single query they write or we'll get too much table locking. As a result, nearly every SELECT written includes the (nolock) hint. In most cases we aren't worried about getting dirty or uncommited data back, so I suppose that's how the line of thinking for using (nolock) was introduced.
How about getting back duplicates of groups of records? How about missing groups of records?
Look at removing that hint from all places and using one of the snapshot isolation levels when you can. No read locks and consistent data.
Should I expect better from the optimizer in SQL 2008, or is this something I can attribute to bad statistics, indexes, etc.?
Yes and yes. Most of the time that's bad stats, bad query or an index that the person applying the hint thought was best, but wasn't (tipping point, predicate selectivity, etc)
Generally if the optimiser isn't picking an index you think is best you want to investigate why before just applying a hint (and when applying a hint, test frequently as data changes and so does the most optimal index)
As a guess, if you remove all the index hints you may very well get an increase in performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2011 at 10:29 am
dean.g (6/30/2011)
That was my gut feeling. We have members of the team who are utterly convinced they need to use (nolock) with every single query they write or we'll get too much table locking. As a result, nearly every SELECT written includes the (nolock) hint. In most cases we aren't worried about getting dirty or uncommited data back, so I suppose that's how the line of thinking for using (nolock) was introduced.
If that's the case, the isolation level should be explicitly set to READ UNCOMMITTED rather than sprinkling NOLOCK hints everywhere. There's nothing wrong with using this isolation level (each level has its trade-offs and missed data and duplicate reads are possible at any non-versioned level below SERIALIZABLE), but using NOLOCK hints is a poor practice.
With respect to the index hints, however, I have seen many occasions where the query optimizer in SQL 2000 chooses a really bad index. Many times I do have to add an index hint to keep it from choosing to use the clustered index on a column not even in the query versus using an index on a column directly used in the query. It typically is the difference between a query that runs in a few seconds and one that takes several minutes. Should I expect better from the optimizer in SQL 2008, or is this something I can attribute to bad statistics, indexes, etc.?
Better, yes. Perfection, no. A lot of query tuning comes down to giving the optimizer good information, having a reasonably-designed (i.e. roughly relational) schema, and writing good SQL.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 11:12 am
Thanks for the feedback, much appreciated.
June 30, 2011 at 2:29 pm
dean.g (6/30/2011)
SQLkiwi (6/30/2011)
Code sprinkled with index name hints and (nolock) in particular is rarely good code, in my experience at least.That was my gut feeling. We have members of the team who are utterly convinced they need to use (nolock) with every single query they write or we'll get too much table locking. As a result, nearly every SELECT written includes the (nolock) hint. In most cases we aren't worried about getting dirty or uncommited data back, so I suppose that's how the line of thinking for using (nolock) was introduced.
With respect to the index hints, however, I have seen many occasions where the query optimizer in SQL 2000 chooses a really bad index. Many times I do have to add an index hint to keep it from choosing to use the clustered index on a column not even in the query versus using an index on a column directly used in the query. It typically is the difference between a query that runs in a few seconds and one that takes several minutes. Should I expect better from the optimizer in SQL 2008, or is this something I can attribute to bad statistics, indexes, etc.?
What this tells me is that your team members have no clue what happens when NOLOCK is used and are only concerned with the lack of locks aspect. Short sighted and naive. My experience shows that NOLOCK is rarely to be used, and yes there are reasons to use it but it is ALWAYS evaluated carefully, NEVER just thrown on there cavalierly. you sacrifice a lot when you use it.
If you are using a lot of hints it indicate problems elsewhere, such as bad statistics. How often are you reindexing?
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply