Some of the beauty of being a database professional is the opportunity to deal with our friend NOLOCK. For one reason or another this query directive (yes I am calling it a directive* and not a hint) is loved and idolized by vendors, applications, developers, and upper management alike. The reasons for this vary from one place to the next, as I have found, but it always seems to boil down to the perception that it runs faster.
And yes, queries do sometimes run faster with this directive. That is until they are found to be the head blocker or that they don’t run any faster because you can write good TSQL. But we are not going to dive into those issues at this time.
A gem that I recently encountered with NOLOCK was rather welcome. Not because of the inherent behavior or anomalies that can occur through the use of NOLOCK, but rather because of the discovery made while evaluating an execution plan. Working with Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) , I came across something that I would rather see more consistently.
If you would like to finish reading this article, please read here.
This Post Has 2 Comments
Hi Jason, I think that organizations start using NO LOCK because it is seen as a quick fix. I tend to recommend using sp_recompile. It seems to me that when someone is looking for a quick performance fix adding the NO LOCK to a stored procedure causes it to recompile as they recreate it. This in turn causes a new plan to be created and gives the impression of a greater gain than the NO LOCK itself affords.
I agree. Bad code, lots of blocks, lots of locks and it is a quick way to band-aid the perceived problem. It would be nice if they would spend more time tuning and adjusting the real problem rather than the symptom.