Blocking in SQL Server occurs when a transaction in a database session locks resources that another transaction requires to read or modify. If your application has long term blocking, then you have a problem in your hands to resolve. It is important to understand how the blocked and blocking queries behave. In SQL Server, a blocked process remains blocked indefinitely – until it times out, the process is killed manually, the server goes down or the original transaction completes execution and releases the lock.

Using Activity Monitor within SQL Server Management Studio you can obtain information about the various SQL Server processes running against your server. There are various sections which provides lot of insights and can help you in monitoring your SQL Server instance —

• Processes – Shows information for active user connections.
• Resource Waits – Shows information about wait for resources.
• Data File I/O – Shows information about the database files that belong to that instance.
• Recent Expensive Queries – Shows the most expensive queries running on your instance.

The Overview tab shows graphical diagram about the number of waiting tasks, database I/O and Batch Requests —

You will require VIEW SERVER STATE permission to view the Activity Monitor.
You can open Activity Monitor from Object Explorer within SQL Server Management Studio by doing a right-click on your server name and then selecting Activity Monitor.

Coming back to reviewing blocking instances, you can identify them by going to the ‘Processes’ section within Activity Monitor.

If there are blocking sessions, then the ‘Blocked By‘ column will display the ID of the session that is blocking the task.

Categories: Performance, SQL Server