By: Ben Snaidero | Updated: 2016-09-27 | Comments (1) | Related: > Locking and Blocking
Problem
A lot of the performance issues in SQL Server often relate to slow running queries. In my last tip I showed you a method for finding long running queries using SQL Profiler. What if after you find this long running query you notice that sometimes the query runs quickly, but other times it's really slow and it both cases it's not using a lot of system resources. In this case it might not be the query itself that is causing the issue, but another query that is blocking this one. This tip will show you how you can use SQL Profiler to find blocking queries in a SQL Server instance.
Solution
As mentioned in my last tip and my tutorial I am a big fan of using the SQL Profiler tool to identify or measure query performance. This tip will show how we can use this tool to identify any queries in our SQL Server instance that are causing performance issues by blocking other queries from completing.
Test Code to Create Blocking
For this example we will use the AdventureWorks database for SQL Server 2014. If you don't have a copy of this database you can download it and other samples from the following link, Adventure Works 2014 Sample Databases. In addition to this we will also need a couple of queries to simulate a blocking issue in our SQL Server instance. We will do this by running a simple update statement within a transaction in one session and in another session we will try to select this record we are updating. Note that in order to see the blocking we will not commit the transaction in the first session. Below is the T-SQL for each of these sessions, but don't run this yet until we setup Profiler to capture the data which we walk through below.
--Session 1 in one query window begin transaction update Production.WorkOrder set DueDate='2015-05-02' where WorkOrderID=72585 -- run the rollback statement after you see the blocking in SQL Profiler rollback transaction --Session 2 in a different query window select * from Production.WorkOrder where WorkOrderID=72585
SQL Profiler Setup
As mentioned in the tutorial, you will need to set the blocked process threshold in order for the report to be generated as it is disabled by default. Keep in mind that depending on the amount of blocking in your system you may want to set this a little bit higher to start with as you don't want to be flooded with reports if you set it too low. Since this is just a test system and we don't want to wait a long time for the report to be generated we will set the interval to 2 seconds for this example. Below is the T-SQL to update this configuration.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'blocked process threshold', 2; GO RECONFIGURE; GO
Once the threshold has been configured we are ready to setup our SQL Profiler session. Since we've done this many times before we'll just look at what is required in the "Events Selection" tab in order to capture the blocking sessions in our trace. You can see from the screenshot below of the setup that we don't need to have too many options selected. In fact you could get by selecting only the TextData column as all other information is included in the xml stored in this column. I do include some other columns as they are helpful when just looking at the output window.
Test Execution and Output
Now that all of the setup is complete all that is left to do is start our SQL Profiler trace and then execute each of the above queries in separate sessions. A few seconds after we start the SELECT statement we should get a record in our trace for the blocked process report. This link, BlockedProcessReport.xml, shows the entire xml that you should see in your trace. Below I show a portion of this xml as want to highlight some of the more important data values.
<blocked-process-report monitorLoop="1261">
<blocked-process>
<process waittime="66508" lockMode="S" status="suspended" spid="52" loginname="sa"
isolationlevel="read committed (2)" currentdb="5">
<executionStack>
<frame line="1" stmtstart="16" stmtend="136" sqlhandle="0x..."/>
<frame line="1" stmtend="114" sqlhandle="0x..."/>
</executionStack>
<inputbuf>
select * from Production.WorkOrder where WorkOrderID=72585 </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="54" loginname="sa" isolationlevel="read committed (2)"
currentdb="5">
<executionStack/>
<inputbuf>
begin tran
update Production.WorkOrder set DueDate='2015-05-02' where WorkOrderID=72585 </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
There are 4 main items I'd like to highlight in this XML output.
- First, using the XML tags you can see which process is blocking and which process is blocked.
- Second, under the process tag there are two fairly important attributes. The SPID of the session is contained here in case you need to kill the blocking session or do some additional tracing on these SPIDs to capture other statements involved in the transaction.
- Third, the isolationlevel attribute is also noted here. I've seen cases where developers have unintentionally been creating sessions with the serializable isolation level and end up causing all kinds of extra blocking in the system. Simply changing the isolation level in their connection string fixed the blocking issue.
- Finally, the inputbuf tag gives you each of the queries involved in the blocking which usually ends up being the most important piece of information. In most cases you'll either need to add an index or possibly rewrite one or both of the queries in order to resolve the blocking.
Next Steps
- Read my tutorial on using SQL Server Profiler
- Read more on other methods to identify blocking in SQL Server
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-09-27
