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.

Category: Uncategorized

About admin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.