SQL Server性能调优技巧 Extended Events

Extended Events是从SQL Server 2008之后具有的轻量级信息跟踪和排查工具，

非常适合进行性能调优。

这里是一个写的很棒、很有条理的入门文章 

https://www.mssqltips.com/sqlservertip/2144/an-overview-of-extended-events-in-sql-server-2008/

  简单的说，Extend Events是一个轻量级的跟踪框架， Events表示要跟踪的事件，Action表示当某个事件发生后需要采取的动作，

Targets表示动作处理指向目标，比如是文件还是内存， Predicates表示断言即筛选什么样的Events, Session表示监听的会话。

例子一：查看最近的死锁事件

 

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

 得到死锁的XML文件后，根据associate objectid查询死锁的对象信息

SELECT OBJECT_NAME(i.[object_id]) , i.name
    FROM sys.partitions as p
    inner join sys.indexes as i on i.object_id = p.object_id 
    and i.index_id = p.index_id
    WHERE partition_id in =[associate objectid]

例子二  监听对某个表的某个字段的修改

如希望监听对某个字段的修改，字段名【ColumnName】，则单独创建一个监听会话，监听SQL修改中包含有

【ColumnName】关键字的，这段语句使用了sql_statement_completed事件

注：以下语句只适用于window server 2012或更高版本，因为2008不支持一些action和predicate.

从实战上讲，2008用sql trace更有效

CREATE EVENT SESSION [my_session] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%[ColumnName]%'))) 
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(512000))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

启动会话

ALTER EVENT SESSION [my_session] ON SERVER  
STATE = START;  
GO 

监听后查询结果，查询监听中有对 [ColumnName]做更新操作的sql语句
select XEvent.value('@timestamp','datetime2') as eventtime,
 XEvent.value('(data[@name = "statement"]/value)[1]','nvarchar(max)') as sqltext,
 XEvent.query('.') as eventtext
from (select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'order_tech') as data
cross apply TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
where XEventData.XEvent.value('@name','varchar(32)') = 'sql_statement_completed'
 and XEvent.value('(data[@name = "statement"]/value)[1]','nvarchar(max)') like '%update%'

 

参考链接 

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017

http://www.sqlservercentral.com/articles/deadlocks/65658/

https://stackoverflow.com/questions/22797023/how-to-figure-out-what-object-is-represented-by-associatedobjectid-during-blocki