Using NOLOCK to Resolve SQL Server Blocking Issues

By: Jan Potgieter   |   Updated: 2023-09-05   |   Comments (3)   |   Related: > Locking and Blocking

Problem

A customer contacted complaining they were getting timeouts on their web application and asked if I could investigate and find out what is causing the problem. He sent me a screen-print with the error message: "The server is currently very busy. Please wait a few minutes and then resubmit."

Solution

In this tip, we will explore a few simple steps I took to get to the root of the problem that the customer was experiencing.

I began by connecting to the Windows server and looked at Task Manager, but there was no apparent high workload. I also looked at Resource Monitor, but all was running normally.

The next step was to connect to SQL Server using SQL Server Management Studio. I checked the SQL Server version (Microsoft SQL Server 2016 – Standard Edition) and also looked at what processes were running. To make this a little easier, I used the sp_WhoIsActive stored procedure written by Adam Machanic to monitor currently running activities in SQL Server. For more information about this SP, refer to this article SP_WhoIsActive Data Collection and Analysis. I have used this stored procedure extensively, but recently, it proved its worth.

The easiest way to use this is to execute the stored procedure as follows EXEC master.dbo.sp_WhoIsActive.

Alternatively, it can be executed to return fewer columns by running the following query:

EXEC master.dbo.sp_WhoIsActive
@get_plans = 1,
@get_outer_command = 1,
@output_column_list = '[dd hh:mm:ss.mss][session_id][sql_text][login_name][wait_info][blocking_session_id][query_plan][database_name][open_tran_count][host_name]'

When running it, I found a View causing blocking on the server. The image below shows that session_id 54 is blocking other queries.

I did a SELECT * FROM vw_WorkOrderAll and it took 70 seconds to return the dataset, about 600 records.

I decided to check out the view, vw_WorkOrderAll that was causing the problems. Below is the code in the view.

SELECT *
FROM dbo.vw_WorkOrder b
INNER JOIN TableA a on a.id = b.WorkOrderId
INNER JOIN TableB c on c.Id = b.ID

Notice that the above view joins another view, vw_WorkOrder, along with some other tables. The above code looked pretty straightforward, so I looked at the definition of vw_WorkOrder. I found a huge SELECT with nearly 50 joins, see the code below (note: I obfuscated the table names, so we can focus on the joins).

The problem was that to run SELECT * FROM vw_WorkordersAll, it takes about 70 seconds to return the data. By default, a SELECT will use a shared lock for the data of a table, ensuring the latest correct data from the database. If there are other processes that need exclusive locks this could cause a blocking situation like we have in this example.

Since it was not critical to have completely accurate data returned from this view, I added the with (nolock) clause in the view as seen below.

This immediately resolved the problem, and the users had their responsive web application running normally again.

Notes for Using WITH (NOLOCK)

• When selecting data from SQL Server, the default transaction isolation level is READ COMMITTED, which means that retrieving changing data will not be allowed and will be blocked until the changes have been committed.
• When using the WITH (NOLOCK) table hint, you effectively tell SQL Server to override the default transaction isolation level of the tables involved in the query.
• Effectively, you stand the chance of getting "DIRTY DATA," meaning that you could get data that changes because of a roll-back.

So, be aware of the consequences when using the WITH (NOLOCK) table hint.

Refer to these related articles:

• Understanding the SQL Server NOLOCK hint
• SQL Server NOLOCK Anomalies, Issues and Inconsistencies

In this case, it solved the end-users' headaches of getting time-outs on the web application but this may not always be the best solution for every situation. Make sure you understand the impact before implementing NOLOCK hints.

Next Steps

Here are related articles:

• SP_WhoIsActive Data Collection and Analysis
• SP_WhoIsActive SQL Server Monitoring
• Understanding the SQL Server NOLOCK hint
• SQL Server NOLOCK Anomalies, Issues and Inconsistencies

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips