Skip to main content

While this query will expose the queries that are most blocked, it is somewhat of an guestimate. What shows up from this query is the procedures whose wait times cannot be explained by looking at the total CPU cycles. While this will mostly infer blocking, it doesn’t necessarily guarantee that’s the issue. It can also show procedures that suffer from other wait types (disk, network, clr, parallelism, etc).

select object_name(dm.objectid) as ObjectName,
    qs.total_elapsed_time - qs.total_worker_time as TimeBlocked,
    qs.execution_count as ExecutionCount,
    qs.total_logical_reads as TotalLogicalReads,
    db_name(dm.dbid) as DatabaseName
    --dm.text as CommandText
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) dm
where object_name(dm.objectid) is not null
order by qs.total_elapsed_time - qs.total_worker_time desc;