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%'
参考链接
http://www.sqlservercentral.com/articles/deadlocks/65658/
