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:
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
Jan has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies. Jan has been working with databases since 1996 when he had his first exposure to Sybase databases. He got involved with SQL Server in the early 2000’s and had exposure to SQL Server 2000, 2005, 2008 and all the rest. Jan likes to find easy and secure ways to host data and has been using Sybase, SQL Server, and MySQL. He has a great passion for working with data and believe the best way to become an expert in a field is by teaching the subject.
- MSSQLTips Awards: Rookie of the Year Contender – 2022
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
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
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… 😉