Analyze blocking for SQL Server

Blocking is a normal activity that occurs in SQL Server and it does that to control data integrity and make sure only one resource can change a set of data at one time. The problem with blocking is if SQL Server blocked a session for a long period, this can cause slowdowns and multiple sessions being blocked, creating a blocking chain.

Identifying Blocking Issues in SQL Diagnostic Manager

In SQL Diagnostic Manager, if there are blocking issues, you can discover fast what is occurring now and for previous points in time. The first screen below shows when blocking occurred and if you drill into one of the block reports, you can view the details of the blocked process and the blocking process.

Details of the blocking process

Here are the details of the blocking process and the blocked process. So you have a history of when blocking occurred. But you have the sessions and statements that were causing the blocking.

For more information, please refer to the solution brief “Keep your SQL Servers running smoothly with SQL Diagnostic Manager” by Greg Robidoux from MSSQLTips.

[Download PDF]