Post reply

Troubleshooting Blocking

  • June 19, 2013 at 7:46 am

    #294879

    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

  • June 19, 2013 at 8:20 am

    #1625499

    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, MVP, M.Sc (Comp Sci)
    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

  • June 19, 2013 at 8:22 am

    #1625501

    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

  • June 19, 2013 at 8:25 am

    #1625505

    Thanks all

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply