How WITH (NOLOCK) can degrade performance

By | December 11, 2019

I was recently asked by a company to have a technical call for a training requirement. The “Senior Database Architect” insisted I only talk about concurrency issue. I tried to explain that concurrency is just one aspect of tuning and using some features are a double edged sword. As an example I wanted to show a fairly common BAD practice I encounter while consulting.

Often people assume the only penalty of using the NOLOCK hint is the Dirty reads that come with it. In this video I demonstrate how using a hint such as NOLOCK can actually cause a query to run slower. That too without any other process running in the background. In other words we are going to simply use a SELECT statement reading data off a single table to demonstrate how the NOLOCK hint hampers read performance. This video is a cautionary tale on not believing everything you read off the internet and how developers should try things out or themselves.