SQLUSA

Microsoft SQL Server 2005
Performance Tuning Best Practices

How to display blocking and blocked queries?

 

Execute the following script using Dynamic Management View(DMV) and Dynamic Management Function(DMF) to list all blocking and blocked queries(sessions):

USE AdventureWorks
GO

SELECT
BlockedSPID=blocked.session_id ,
BlockedQuery=blockedsql.text,
BlockingSPID=blocking.session_id,
BlockingQuery=blockingsql.text
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking
ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked.sql_handle)
) blockedsql
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking.sql_handle)
) blockingsql
GO

 

 

The Best SQL Server 2005 Training in the World
 
SQLUSA.com Home Page