This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one script to find a list of queries which are blocked by other running queries of SQL Server.
DBAs are always seeking for long running queries, most resources consumed query. Using this script DBA can find list of waiting queries which are not under any type of execution.
DBAs can use this query to prevent the deadlock also because long running block transactions can occur a deadlock situation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT dowt.session_id ,dowt.wait_duration_ms ,dowt.wait_type ,dowt.blocking_session_id ,dese.host_name as HostName ,der.command ,der.percent_complete ,der.cpu_time ,der.total_elapsed_time ,der.reads ,der.writes ,der.logical_reads ,der.row_count ,dest.text AS QueryText ,dest.dbid AS DatabaseID ,deqp.query_plan ,der.plan_handle FROM sys.dm_os_waiting_tasks as dowt INNER JOIN sys.dm_exec_sessions as dese ON dowt.session_id = dese.session_id INNER JOIN sys.dm_exec_requests as der ON dese.session_id = der.session_id CROSS APPLY sys.dm_exec_sql_text(der.plan_handle) as dest CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) as deqp WHERE dowt.session_id > 50 |
Here, You can also access few related articles:
SQL Server: Script to find TOP 20 most used or executed SQL Queries
SQL Server: Script to find top 20 running query, which are utilizing more CPU
SQL Server: 8 different ways to Detect a DeadLock in a Database
