I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment experiencing the issue.

Is there any way for me to obtain statistics on how long this individual query is blocked, without using an external profiler?


(If you have access to DMVs then look into sp_whoisactive with @find_block_leaders = 1. Just tell you DBA (if you are not) to deploy it and grant you execute permission.)

SQL server dynamic management views are your best friend :

Below are several ways to find out blocking :

--Ref: https://sqlserverperformance.wordpress.com/category/diagnostic-queries/
select t1.resource_type as 'lock type'
    ,db_name(resource_database_id) as 'database'
    ,t1.resource_associated_entity_id as 'blk object'
    ,t1.request_mode as 'lock req'
    ,--- lock requested
    t1.request_session_id as 'waiter sid'
    ,t2.wait_duration_ms as 'wait time'
    ,-- spid of waiter  
        select [text]
        from sys.dm_exec_requests as r -- get sql for waiter
        cross apply sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id
        ) as 'waiter_batch'
        select substring(qt.text, r.statement_start_offset / 2, (
                        when r.statement_end_offset = - 1
                            then LEN(CONVERT(nvarchar(max), qt.text)) * 2
                        else r.statement_end_offset
                        end - r.statement_start_offset
                    ) / 2)
        from sys.dm_exec_requests as r
        cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
        where r.session_id = t1.request_session_id
        ) as 'waiter_stmt'
    ,-- statement blocked
    t2.blocking_session_id as 'blocker sid'
    ,-- spid of blocker
        select [text]
        from sys.sysprocesses as p -- get sql for blocker
        cross apply sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
        ) as 'blocker_stmt'
from sys.dm_tran_locks as t1
inner join sys.dm_os_waiting_tasks as t2 on t1.lock_owner_address = t2.resource_address;

A more deeper look into blocking :

-- Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
-- Waiter and Blocking Report
SELECT -- blocked
    er.session_id AS blocked_spid
    ,ot.task_state AS [status]
    ,owt.wait_type AS blocked_spid_wait_type
    ,owt.wait_duration_ms AS blocked_spid_wait_time_ms
    -- Check sys.dm_os_waiting_tasks for Exchange wait types in http://technet.microsoft.com/en-us/library/ms188743.aspx.
    -- Wait Resource e_waitPipeNewRow in CXPACKET waits – Producer waiting on consumer for a packet to fill.
    -- Wait Resource e_waitPipeGetRow in CXPACKET waits – Consumer waiting on producer to fill a packet.
    owt.resource_description AS blocked_spid_res_desc
        WHEN owt.pageid = 1
            OR owt.pageid % 8088 = 0
            THEN 'Is_PFS_Page'
        WHEN owt.pageid = 2
            OR owt.pageid % 511232 = 0
            THEN 'Is_GAM_Page'
        WHEN owt.pageid = 3
            OR (owt.pageid - 1) % 511232 = 0
            THEN 'Is_SGAM_Page'
        WHEN owt.pageid IS NULL
            THEN NULL
        ELSE 'Is_not_PFS_GAM_SGAM_page'
        END AS blocked_spid_res_type
        SELECT qt.TEXT AS [text()]
        FROM sys.dm_exec_sql_text(er.sql_handle) AS qt
        FOR XML PATH('')
        ) AS [blocked_batch]
    ,es.last_request_start_time AS blocked_last_start
    ,LEFT(CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)
            WHEN 0
                THEN '0-Unspecified'
            WHEN 1
                THEN '1-ReadUncommitted(NOLOCK)'
            WHEN 2
                THEN '2-ReadCommitted'
            WHEN 3
                THEN '3-RepeatableRead'
            WHEN 4
                THEN '4-Serializable'
            WHEN 5
                THEN '5-Snapshot'
            ELSE CONVERT(VARCHAR(30), er.transaction_isolation_level) + '-UNKNOWN'
            END, 30) AS blocked_tran_isolation_level
    ,er.total_elapsed_time / 1000 AS total_elapsed_time_sec
    -- blocker
    er2.session_id AS blocker_spid
        -- blocking session is either not blocked or has open trans
        WHEN owt.waiting_task_address IN (
                SELECT owt2.blocking_task_address
                FROM sys.dm_os_waiting_tasks owt2
            AND (
                er2.session_id IS NULL
                OR owt.blocking_session_id IS NULL
                OR owt.[blocking_task_address] IS NULL
            THEN 1
        ELSE 0
        END AS is_head_blocker
        SELECT qt2.TEXT AS [text()]
        FROM sys.dm_exec_sql_text(er2.sql_handle) AS qt2
        FOR XML PATH('')
        ) AS [blocker_batch]
    ,es2.last_request_start_time AS blocker_last_start
    ,LEFT(CASE COALESCE(er2.transaction_isolation_level, es2.transaction_isolation_level)
            WHEN 0
                THEN '0-Unspecified'
            WHEN 1
                THEN '1-ReadUncommitted(NOLOCK)'
            WHEN 2
                THEN '2-ReadCommitted'
            WHEN 3
                THEN '3-RepeatableRead'
            WHEN 4
                THEN '4-Serializable'
            WHEN 5
                THEN '5-Snapshot'
            ELSE CONVERT(VARCHAR(30), er2.transaction_isolation_level) + '-UNKNOWN'
            END, 30) AS blocker_tran_isolation_level
    -- other data
    DB_NAME(er.database_id) AS DBName
    ,es.host_name AS blocked_host
    ,es.program_name AS blocked_program
    ,es.login_name AS blocked_login
        WHEN es.session_id = - 2
            THEN 'Orphaned_distributed_tran'
        WHEN es.session_id = - 3
            THEN 'Deffered_recovery_tran'
        WHEN es.session_id = - 4
            THEN 'Unknown_tran'
        ELSE NULL
        END AS blocked_session_comment
    ,es.is_user_process AS [blocked_is_user_process]
    ,es2.host_name AS blocker_host
    ,es2.program_name AS blocker_program
    ,es2.login_name AS blocker_login
        WHEN es2.session_id = - 2
            THEN 'Orphaned_distributed_tran'
        WHEN es2.session_id = - 3
            THEN 'Deffered_recovery_tran'
        WHEN es2.session_id = - 4
            THEN 'Unknown_tran'
        ELSE NULL
        END AS blocker_session_comment
    ,es2.is_user_process AS [blocker_is_user_process]
    --In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
    --waiting for several different threads.  This will cause that thread to show up in multiple rows
    --which is irrelevant.  Use ROW_NUMBER to select the longest wait for each thread
    SELECT [waiting_task_address]
            WHEN [wait_type] LIKE 'PAGE%'
                AND [resource_description] LIKE '%:%'
                THEN CAST(RIGHT([resource_description], LEN([resource_description]) - CHARINDEX(':', [resource_description], LEN([resource_description]) - CHARINDEX(':', REVERSE([resource_description])))) AS INT)
            ELSE NULL
            END AS pageid
        ,ROW_NUMBER() OVER (
            PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC
            ) AS row_num
    FROM sys.dm_os_waiting_tasks --ORDER BY session_id
    ) owt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = owt.waiting_task_address
LEFT OUTER JOIN sys.dm_exec_requests er ON er.session_id = ot.session_id
    AND er.request_id = ot.request_id
LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = er.session_id
LEFT OUTER JOIN sys.dm_exec_sessions es2 ON es2.session_id = owt.blocking_session_id
LEFT OUTER JOIN sys.dm_exec_requests er2 ON er2.session_id = owt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE owt.row_num = 1
    AND es.session_id <> @@SPID
    AND es.is_user_process = 1
ORDER BY blocked_spid
    ,is_head_blocker DESC
    ,blocked_spid_wait_time_ms DESC

Using Extended Event or BLOCKED_PROCESS_REPORT, you can find and get alerted when blocking occurs.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.