如何监控和解决SQL Server的阻塞(3) (扩展事件）

来源：互联网发布：复旦在职研究生知乎编辑：程序博客网时间：2024/05/17 14:25

如何监控和解决SQL Server的阻塞(1)　点击打开链接
如何监控和解决SQL Server的阻塞(2)（事件通知）点击打开链接

SQL 2008 R2 以后引入了扩展事件(Extended Event),通过扩展事件可以监控Blocking.　对比SQL Profiler, 扩展事件监控是轻量级的.较少的开销cpu. SQL 2008 R2 你可以通过脚本设置扩展事件。　SQL 2012以后，你可以通过GUI界面设置。　这里我们利用脚本设置来了解扩展事件的内部语义。

如果你需要在SQL 2008 SSMS里象SQL2012一样使用GUI Extended Event，请下载如下插件

SQL Server 2008 Extended Event SSMS插件: 点击打开链接

1. 设置扩展事件Session，收集Blocking信息

CREATE EVENT SESSION BlockingTransactions ON SERVERADD EVENT sqlserver. locks_lock_timeouts (ACTION (sqlserver.sql_text, sqlserver.tsql_stack)),ADD EVENT sqlserver. locks_lock_waits (ACTION (sqlserver.sql_text, sqlserver.tsql_stack))ADD TARGET package0.ring_bufferWITH (MAX_DISPATCH_LATENCY = 30 SECONDS);GOALTER EVENT SESSION BlockingTransactions ON SERVER STATE = START;GO

2.　当blocking发生时，查询扩展事件

WITH BlockingTransactionsAS (SELECT CAST(target_data AS xml) AS SessionXMLFROM sys.dm_xe_session_targets stINNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_addressWHERE name = 'BlockingTransactions')SELECTblock.value('@timestamp', 'datetime') AS event_timestamp,block.value('@name', 'nvarchar(128)') AS event_name,block.value('(data/value)[1]', 'nvarchar(128)') AS event_count,block.value('(data/value)[1]', 'nvarchar(128)') AS increment,mv.map_value AS lock_type,block.value('(action/value)[1]', 'nvarchar(max)') AS sql_text,block.value('(action/value)[2]', 'nvarchar(255)') AS tsql_stackFROM BlockingTransactions bCROSS APPLY SessionXML.nodes ('//RingBufferTarget/event') AS t(block)INNER JOIN sys.dm_xe_map_values mv ON block.value('(data/value)[3]', 'nvarchar(128)') = mv.map_key AND name = 'lock_mode'WHERE block.value('@name', 'nvarchar(128)') = 'locks_lock_waits'UNION ALLSELECTblock.value('@timestamp', 'datetime') AS event_timestamp,block.value('@name', 'nvarchar(128)') AS event_name,block.value('(data/value)[1]', 'nvarchar(128)') AS event_count,NULL,mv.map_value AS lock_type,block.value('(action/value)[1]', 'nvarchar(max)') AS sql_text,block.value('(action/value)[2]', 'nvarchar(255)') AS tsql_stackFROM BlockingTransactions bCROSS APPLY SessionXML.nodes ('//RingBufferTarget/event') AS t(block)INNER JOIN sys.dm_xe_map_values mv ON block.value('(data/value)[2]', 'nvarchar(128)') = mv.map_key AND name = 'lock_mode'WHERE block.value('@name', 'nvarchar(128)') = 'locks_lock_timeouts';

查询如图：

0 0