Performing a SQL Server Health Check for a new customer is one of the highlights of my workday. It’s always an exciting challenge to dive into their system, uncover the root causes of performance issues, and help them achieve smoother, faster operations.
Performance issues in SQL Server can stem from a variety of sources, but blocking is one of the most frequent culprits I encounter. When that happens, I always reach for Michael J Swart’s excellent Blocked Process Viewer script.
This tool takes SQL Server’s blocked process information and formats it in a really useful way so you can quickly identify the sessions, queries, and objects that are the root cause of the blocking.
In this blog post, I’ll show you how to install this tool, run it, and understand the output. I’ll also offer some advice on reducing blocking issues. It is not intended to detail every aspect of how to use the tool (see the link above for that).
Step 1 - Enable to blocked process report
SQL Server has a built-in blocked process report which you must enable (it’s off by default). When you enable this report you’re effectively saying to SQL Server:
“If any queries are blocked for more than [n] seconds, I want you to record the details of the sessions, queries, and objects involved”.
Firstly, check if the process is already enabled:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold';
Example output:
- config_value: The value that will be used after a restart or RECONFIGURE.
- run_value: The value currently in effect.
If the threshold is set to zero, the blocked process report feature is disabled, so you’ll need to enable it (no restart required). In the example below, I’m setting the threshold to 10 seconds, but you’ll need to decide what the right threshold for your server is. On a very busy server, I tend to start high so I only capture the worst instances of blocking:
sp_configure 'blocked process threshold', 10;
GO
RECONFIGURE;
Step 2 - Create an Extended Event to capture
Now that we’ve told SQL Server to record when severe blocking occurs, we need to create an Extended Event to capture and store the details:
CREATE EVENT SESSION [Blocked_Process_Report]
ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file
(SET filename = N'C:\XEvents\Blocked-Process-Report.xel')
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION [Blocked_Process_Report] ON SERVER STATE = START
Note – Once you’ve captured the data you need, it’s a good idea to disable the Extended Event session so you’re not unnecessarily consuming resources.
Step 3 - Install and run the Blocked Process Viewer
Head over to Github or Michael J Swart’s website to grab the script and install it.
Running it is very straightforward:
EXEC sp_blocked_process_report_viewer @Source = 'Blocked_Process_Report'
, @Type = 'XESESSION'
Example output:
As you can see, the output is nicely formatted into a Blocking Tree so we can clearly see which sessions are causing the blocking, and which sessions are suffering as a result.
If you find that you’re not getting any results, then it’s likely for one of two reasons:
- The threshold you defined in step 1 has not been met. In which case, you could consider lowering it.
- The Extended Event is not running or is configured incorrectly.
Understanding the output
The XML captured contains lots of useful information for figuring out what resources are involved in the blocking chain (both the blocked process and the blocking process). Unfortunately, this isn’t well documented by Microsoft, but I’ll explain the parts I tend to focus on.
I usually start by looking at the XML for the process immediately after the lead blocker (session 302 in the example above).
blocked-process -> waitresource
This is the resource (e.g. table, index page, row) that the blocked query is trying to access, but can’t. This is obviously a critical piece of information, but you’ll need to decode the resource value to figure out what it is. Fortunately, Kenda Little has an excellent blog on this.
blocked-process -> waittime
The time, in milliseconds that the blocked process has been waiting.
blocked-process -> lockMode
This value indicates the type of lock that the blocked process is trying to obtain on the wait resource e.g. Exclusive (X). You can learn more about the possible lock modes here.
blocked-process -> isolationlevel
The Isolation Level of a transaction has a major impact on the locks it takes. The more restrictive the isolation level is, the more likely blocking will occur. For example, If you have transactions running under the SERIALIZABLE transaction level, then this can cause significant blocking problems.
inputbuf
The SQL statement that is being blocked or causing the blocking. Alternatively, you may see an object id, which can be easily decoded:
SELECT OBJECT_NAME(YourObjectID)
executionstack
Each individual frame within the execution stack is listed. The sqlhandle attribute is a token that uniquely identifies the batch that executed. This may help you identify where in your application code the blocking query is coming from. For example, it may be a stored procedure that is called when a user performs a specific action, or it may be an overnight maintenance routine.
To find out what it is, run this query:
SELECT text AS [SQL_Text]
FROM sys.dm_exec_sql_text(YourSQLHandleHere);
For more information on the sys.dm_exec_sql_text DMV, see the Microsoft documentation.
Step 4 - Fixing the blocking issues
By gathering the information above, you should be able to identify which SQL statements and objects are involved in the blocking chain. From there, the next step is to try and reduce this blocking.
Unfortunately, there’s no single fix that I can write in a blog, but here’s some general recommendations that will help:
1. Optimise queries
- Indexing: Ensure that your queries are using appropriate indexes. Missing or inefficient indexes can lead to table scans, which increase the likelihood of blocking.
- Query Design: Write efficient queries that minimise the amount of data being processed. Avoid using SELECT * and instead, specify only the columns you need.
2. Minimise transaction scope
- Short Transactions: Keep transactions as short as possible. Long-running transactions hold locks for extended periods, increasing the chance of blocking.
- Batch Processing: Break large operations into smaller batches. This reduces the lock duration and helps in managing resources better. For example, if you have a cleanup routine that deletes millions of rows in one go, re-write it to delete it in smaller batches of 5,000 rows.
3. Use appropriate isolation levels
- Read Committed Snapshot Isolation (RCSI): Consider using RCSI to reduce blocking caused by read operations. This isolation level uses row versioning to provide a consistent view of the data without holding locks.
- Avoid the SERIALIZABLE Isolation where possible: The SERIALIZABLE isolation level can cause blocking because it places range locks on the data set, preventing other transactions from inserting, updating, or deleting rows within the locked range until the transaction is complete. This ensures the highest level of isolation but significantly reduces concurrency.
Further resources
More detail on how you can use the Blocked Process Viewer can be found on the authors website and github repository. Kendra Little has also has a great video on how to configure the Blocked Process Report.
Thanks for reading!
