Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Troubleshooting Blocking Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 7:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 1,380, Visits: 2,705
Hi All

I am trying to figure out which queries have caused blocking on my system, or which queries have been victims of blocking.

I have the below script. Correct me if I am wrong, the results of this script is not a definitive list of queries that have been blocked. The difference between the elapsed time and the worker time could be because of any wait type, right?

SELECT TOP 10 
[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time - total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;

Is there a way by digging into the plan cache to check for queries that have caused blocking?


Thanks
Post #1465175
Posted Wednesday, June 19, 2013 8:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
SQLSACT (6/19/2013)
The difference between the elapsed time and the worker time could be because of any wait type, right?


Correct, and you could miss queries that have waited, because of parallelism

Is there a way by digging into the plan cache to check for queries that have caused blocking?


No. What's in the plan cache are the plans that the optimiser created for the query and the optimiser doesn't plan for queries to wait or be blocked.

Poll sys.dm_exec_requests, poll sys.dm_os_waiting_tasks, use the blocked process report, set up extended events.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1465201
Posted Wednesday, June 19, 2013 8:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:47 PM
Points: 727, Visits: 1,434
If you are still using 2005 and don't have Xevents, you could also use profiler. Brad McGehee has an excellent article here:

https://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/

Fraggle
Post #1465203
Posted Wednesday, June 19, 2013 8:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 1,380, Visits: 2,705
Thanks all
Post #1465207
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse