| 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 |
|