Find Blocking Processes Using SQL Server Profiler


By:   |   Updated: 2016-09-27   |   Comments (1)   |   Related: More > 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.

Event Selection tab in SQL Server Profiler

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


Last Updated: 2016-09-27


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, February 07, 2020 - 10:10:24 AM - Mike Jansen Back To Top

How do you change isolation level in the connection string?  I have not been able to find this.  In fact, all I have found is statements that you can't do it.



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools