Find Most Blocked Queries

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(objectid)
    ,BlockTime = total_elapsed_time - total_worker_time
    ,execution_count
    ,total_logical_reads
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY total_elapsed_time - total_worker_time DESC

3 comments
Nikos 29 Aug 2015 at 6:26 am

Remuneration is considered only for vailatuon of Goodwill. It may not have been paid in that year. Goodwill vailatuon is to be based on Normal profits earned by business.Remuneration to partners is Appropriation of profit only if there is no agreement, bu virtue of specific agreement it can become charge against profits.

RICHARDOT Patrick 07 Jul 2014 at 9:14 am

Use this for content :

SELECT
OBJECT_NAME(objectid),
qt.text AS SQL_TEXT
,BlockTime = total_elapsed_time – total_worker_time
,execution_count
,total_logical_reads
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY total_elapsed_time – total_worker_time DESC

syed 02 Feb 2013 at 2:49 pm

I ran this query… But First column does not have Name & it is showing NULL.
I got nearly 17000 rows…
What does this mean? Plz reply.

Featured Articles

 Site Author

  • Thanks for visiting!