(당신이 DMV에에 액세스 할 수있는 경우 다음을 들여다 sp_whoisactive 와 @find_block_leaders = 1. 그냥 당신이하지 않은 경우) 배포 및 실행 권한을 부여 (당신에게 DBA를 말한다.)

SQL Server 동적 관리 뷰는 가장 친한 친구입니다.

다음은 차단을 찾는 몇 가지 방법입니다.

--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, (
                    case
                        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;

블로킹에 대해 더 자세히 살펴보십시오.

-- 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
    ,CASE
        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('')
            ,TYPE
        ) 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
    ,CASE
        -- 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('')
            ,TYPE
        ) 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
    ,CASE
        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
    ,CASE
        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]
FROM (
    --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]
        ,[session_id]
        ,[wait_duration_ms]
        ,[wait_type]
        ,[blocking_task_address]
        ,[blocking_session_id]
        ,[resource_description]
        ,CASE
            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
    ,blocker_spid
GO

확장 이벤트 또는 BLOCKED_PROCESS_REPORT를 사용 하면 차단이 발생할 때이를 찾아 경고를받을 수 있습니다.

답변