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. Let’s take a look at this example execution plan:
First is a look at the plan to see if you can see what I saw.
And now, we can see it clear as day. In this particular case, SQL Server decided to remind us that the use of this directive allows uncommitted reads to occur so it throws that directive into the query text of the execution plan as well. This is awesome! In short, it is a visual reminder that the use of the NOLOCK directive, while it may be fast at times, is a direct route to potentially bad results by telling the database engine to also read uncommitted data.
How cool is that? Sadly, I could only reproduce it on this one version of SQL Server so far. If you can reproduce that type of behavior, please share by posting to the comments which version and what you did. For me, database settings and server settings had no effect on this behavior. No trace flags were in use, so no effect there either. One thing of note, in my testing, this did not happen when querying against a table direct but did only occur when querying against a view (complexity and settings produced no difference in effect for me).
* I would like to make it a goal for every database professional to call this a DIRECTIVE instead of a hint. A hint implies that you may have a choice about the use of the option specified. And while NOLOCK does not entirely eliminate locks in the queries, it is an ORDER to the optimizer to use it. Therefor it is more of a directive than a mere suggestion.
