THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

Nearly any SQL topic presented at times in a slightly eclectic manner.

TechEd 2007 from the cheap seats :)

So I've been at TechEd this week working as a Technical Learning Guide (helping out in the Hands-On Labs area). While there are good opportunities at times to share what I know (and l learn what I don't know), there's been a fair amount of time answering more basic questions than I'd like (such as "Where can I sit"). Don't get me wrong, I love doing this stuff, but I'm jealous of Kalen and Kevin as such :)

Speaking of Kalen, I attended her "birds-of-a-feather" discussion on concurrency. Very interested, especially when the discussion touched snapshot isolation. As Kalen will probably point out in detail, one of the items that many people seemed to be keen on was using the NO LOCK hint within queries. It turns out that isn't the best thing for the Lock Manger (and performance in general).

I usually describe the Lock Manager as a sort of Hindu diety, with multiple arms juggling a number of factors to try to maintain the highest level of concurrency and consistency it can. NO LOCK essentially tells the Lock Manager to go take a break - which means the Lock Manager can't factor NO LOCK queries into the entire locking equation (Hindu diety with a pair or two of handcuffs on them).

To make things worse, using Snapshot Isolation and NO LOCK hints is basically a waste of resources - NO LOCK bypasses even Snapshot Isolation (as Kalen put it - "All bets are off" when using NO LOCK). (NOTE: Kalen, please feel free to beat me with a stick if I've misworded).

 The bottom line I try to impress on my clients and students is to fully understand locking, concurrency and consistency as they pertain to SQL Server. Other DB platforms use different locking models - that doesn't necessarily mean they're better, just different. Be sure to understand the locking technique you are working with before writing even a single SELECT statement.

All in all TechEd has been fun, met lots of people I haven't seen in ages, hear stories I'd rather forget (like falling down a flight of stairs in Denmark), and hearing questions and situations that I've not yet considered. If you haven't been to a TechEd, I highly recommend it. It can be both an edifying experience as well as a humbling one.

More on Snapshot Isolation, lock hints and other items to follow soon!

Published Wednesday, June 6, 2007 10:09 PM by James Luetkehoelter



Matt said:

NOLOCKs can be very useful: They show you where to focus your cleanup efforts when brought into an existing project - to bring it back from the dead ;)

June 7, 2007 9:40 AM

Adam Machanic said:

Used VERY selectively I find NOLOCK hints to be a godsend... SNAPSHOT isolation is great but it is a bit high-impact for many projects.  

One thing that many people are surprised by is that NOLOCKs can actually slow your query down.  I've never nailed down an exact reason, but I've found certain queries that will be 10-15% slower with a NOLOCK hint than without.  Then again, a major blocking problem can easily overshadow 10-15%, so again it's a question of very careful application.

June 7, 2007 10:33 AM

Hugo Kornelis said:

Adam wrote: "NOLOCKs can actually slow your query down.  I've never nailed down an exact reason"

A *possible* explanation might be that these queries use an unordered index scan in the execution plan. If the execution plan calls for an unordered scan but row or page level locks are taken, the engine will still execute an ordered scan, which accesses only the leaf pages; if an unordered scan is used, the query engine will switch to accessing pages through the IAM, which means that the rooot and intermediate level pages of the index are brought in as well. The cost of bringing in these (relatively few) extra pages is usually offset by the pages being read in order, minimizing head movement - but if all pages are already in cache, or if other processes access data on other parts of the disk at the same time, you lose this advantage and still need to read the intermediate and root level pages.

June 8, 2007 4:51 PM
New Comments to this post are disabled

About James Luetkehoelter

I am passionate about what I do - which is DBA, development, IT and IT business consulting. If you don't know me, haven't met me or have never heard me speak, I'm a little on the eccentric side. One attendee recently described me as being "over the top". Yup, that about says it - because I only speak on topics that I'm passionate about.
Privacy Statement