Find Most Blocked Queries
-
Posted on May 31, 2009 by Derek Dieter
-
2
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).
[cc lang=”sql”]
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
[/cc]
- Comments (RSS)
- Trackback
- Permalink
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.
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
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.
