on December 22, 2016
I’m a big fan of the built-in Blocked Process Report in SQL Server. It’s come in handy for troubleshooting blocking situations for me many times.
I wanted a friendly way to share code to configure and manage the Blocked Process Report, so I’ve created a gist on GitHub sharing TSQL that:
- Enables the Blocked Process Report (BPR)
- Collects the BPR with an Extended Events trace
- Collects the BPR using a Server Side SQL Trace (in case you don’t care XEvents or are running an older version of SQL Server)
- Lists out the Extended Events and SQL Traces you have running, and gives you code to stop and delete traces if you wish
View or download the code from GitHub, or get it below.
Tools to help decode the Blocked Process Report after you collect it
- Michael J. Swart’s Blocked Process Report Viewer on Codeplex - this makes it easy to see the lead blocker and the shape of the blocking chain. Note: you don’t have to run this on the production server itself, you can copy the files to your desktop or a test server.
- Once you’re looking at individual reports, my post “Decoding Key and Page WaitResource for Deadlocks and Blocking” will help you dig into the nitty gritty details