XEVENT--SQL SERVER 2008R2版本下使用XEVENT捕获死锁和慢查询

测试版本：SQL SERVER 2008 R2

--===================================================
--死锁监控
--执行超过3000ms的查询且数据ID为38的SQL监控
--捕获数据存放在D:\Lock.xel下，最大文件为100MB
--===================================================
--查询数据库ID,并修改[source_database_id]的值
SELECT DB_ID('')
GO
CREATE EVENT SESSION [XE_Lock] 
ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
(
    ACTION(sqlserver.sql_text)
),
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION(sqlserver.sql_text)
    WHERE [duration]>3000
    AND [source_database_id]=38
    
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename=N'D:\Lock.xel',
    max_file_size=200,
    max_rollover_files=5
)
WITH 
(
    MAX_DISPATCH_LATENCY = 5 SECONDS, 
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    TRACK_CAUSALITY=ON
)

GO

--开始XEVENT回话
ALTER EVENT SESSION [XE_Lock] ON SERVER
STATE=START;

--开始XEVENT回话
ALTER EVENT SESSION [XE_Lock] ON SERVER
STATE=STOP;

--删除XEVENT回话
DROP EVENT SESSION [XE_Lock] ON SERVER

 --===========================================

妹子：颜卓灵