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.