2011-08-02 02:43 | 只看楼主
树型| 收藏| 小 中 大 1

用扩展事件捕获阻塞

用扩展事件捕获阻塞


--王成辉整理，转贴请注明出自微软BI开拓者www.windbi.com




创建扩展事件


CREATE EVENT SESSION FindBlockers ON SERVER
ADD EVENT sqlserver.lock_acquired
    (action
        ( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,
        sqlserver.plan_handle, sqlserver.session_id,sqlserver.username,sqlserver.client_app_name)
    WHERE ( resource_0!=0)
    ),
ADD EVENT sqlserver.lock_released
    (WHERE ( resource_0!=0 ))
ADD TARGET package0.pair_matching
    ( SET begin_event='sqlserver.lock_acquired',
            begin_matching_columns='database_id, resource_0, resource_1, resource_2, transaction_id, mode',
            end_event='sqlserver.lock_released',
            end_matching_columns='database_id, resource_0, resource_1, resource_2, transaction_id, mode',
    respond_to_memory_pressure=1)
WITH (max_dispatch_latency = 1 seconds)


启动会话
ALTER EVENT SESSION FindBlockers ON SERVER
STATE = START


停止会话
ALTER EVENT SESSION FindBlockers ON SERVER
STATE = STop


删除
drop EVENT SESSION FindBlockers ON SERVER





从扩展事件中提取需要的数据

drop table #unmatched_locks   


SELECT
objlocks.value('(action/value)[5]', 'int')
        AS session_id,
    objlocks.value('(data/value)[5]', 'int')
        AS database_id,
    objlocks.value('(data/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data/value)[9]', 'bigint')
        AS resource_0,
    objlocks.value('(data/value)[10]', 'bigint')
        AS resource_1,
    objlocks.value('(data/value)[11]', 'bigint')
        AS resource_2,
    objlocks.value('(data/text)[2]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action/value)[4]', 'varchar(MAX)') AS xml)
        AS plan_handle,   
    CAST(objlocks.value('(action/value)[3]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
    objlocks.value('(action/value)[6]', 'varchar(100)')
        AS username,       
objlocks.value('(action/value)[7]', 'varchar(100)')
        AS application_name       
INTO #unmatched_locks
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)


SELECT distinct blocking_session_id,d.database_id,d.resource_type,object_name(d.resource_0,d.database_id) as objectname1,
d.mode,d.sql_text,d.username,d.application_name,a.start_time,
a.session_id,
--e.mode,
e.username as username2,e.application_name as application_name2,e.sql_text as sql_text2,
object_name(e.resource_0,e.database_id) as objectname2
FROM sys.dm_exec_requests a
cross apply (SELECT b.* FROM #unmatched_locks b where a.blocking_session_id=b.session_id) d
cross apply (SELECT c.* FROM #unmatched_locks c where a.session_id=c.session_id) e
WHERE blocking_session_id != 0 and d.resource_type='object'
and d.resource_0=e.resource_0
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。