Using NOLOCK to Resolve SQL Server Blocking Issues

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


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."


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.

sp_WhoIsActive output

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

FROM dbo.vw_WorkOrder b
INNER JOIN TableA a on = 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).

query text

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.

query text

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:

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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jan Potgieter 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

Article Last Updated: 2023-09-05

Comments For This Article

Tuesday, September 5, 2023 - 10:10:40 AM - Tim Back To Top (91533)
If this is just reading static, reference data, this can be an effective solution. However, don't discount that you are reading either dirty data or data that may not be there (inserted and the transaction was then rolled back). Other alternatives are using the READPAST query hint or even changing the isolation level to READ_COMMITTED_SNAPSHOT

Tuesday, September 5, 2023 - 9:36:33 AM - Jeff Moden Back To Top (91532)
Commenting just to be notified of comments on this thread because it's interesting to me that someone has a view in production that returns every column of 48 tables. Heh... it lends new meaning to the term "Star Schema". :D

Tuesday, September 5, 2023 - 8:36:35 AM - Andre Speek Back To Top (91531)
Since the original guy/gal did a good job on nicely formating the query, it was easy to add the with (nolock) for all these lines... using the Alt+Shift+Arrow up/down key to type on multiple lines... But wouldn't it be easier to use Read Uncommited here?

Also, the use of select * with so many joins is also an accident waiting to happen... 😉

get free sql tips
agree to terms