使用扩展事件记录 SQL 执行历史

http://tommwq.tech/blog/%e4%bd%bf%e7%94%a8%e6%89%a9%e5%b1%95%e4%ba%8b%e4%bb%b6%e8%ae%b0%e5%bd%95-sql-%e6%89%a7%e8%a1%8c%e5%8e%86%e5%8f%b2/

扩展事件（XEvent）是 SQL Server 从 2008 版本开始提供的一种记录系统运行事件的机制。使用扩展事件可以了解 SQL Server 的内部执行情况，其中就包括了执行过哪些 SQL 语句。要记录和查看 SQL 语句执行历史，需要执行 4 个步骤：创建扩展事件会话；启动扩展事件会话；读取 xel 文件；关闭扩展事件会话。

1 创建扩展事件会话

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name='test_event_session')
    DROP EVENT SESSION test_event_session ON SERVER;
GO

CREATE EVENT SESSION test_event_session
ON SERVER
ADD EVENT sqlserver.sql_statement_completed 
(
        ACTION (sqlserver.sql_text)
)
ADD TARGET package0.event_file
(
        SET filename=N'Z:\test_event_session.xel'
) 
WITH 
(
        MAX_MEMORY=16MB,
        MAX_EVENT_SIZE=16MB,
        MAX_DISPATCH_LATENCY=1 SECONDS
);
GO

使用语句 CREATE EVENT SESSION 可以创建扩展事件会话。要使用扩展事件记录 SQL 执行历史，首先必须创建扩展事件会话。这里我们选择 test_event_session 作为会话名，并将 sqlserver.sql_statement_completed 事件添加到会话中。sql_statement_completed 是事件名，表示“Transact-SQL 语句已完成时发生。”事件。sqlserver 是包名，事件是从属于某个包的。后面我们会进一步介绍包、事件等概念。扩展事件中定义了很多事件类型，这些事件可以通过下面的 SQL 语句查询：Listing 1: 查询 SQL Server 支持的扩展事件

SELECT packages.name,packages.description,objects.name,objects.description 
FROM sys.dm_xe_objects objects
LEFT JOIN sys.dm_xe_packages packages ON packages.guid=objects.package_guid
WHERE object_type='event'
ORDER BY packages.name,objects.name 

这里我们只关注 SQL 执行历史记录，因此只需要添加 sql_statement_completed 事件。

当事件发生后，事件被发送给目标（target），目标是保存事件信息的地方。这里我们使用 event_file，表示将事件信息保存到文件中。要注意的时，参数 filename 必须以“.xel”结尾。每个目标支持不同的参数，这些参数可以通过下面的 SQL 查询：Listing 2: 查询目标参数

SELECT object_name,name,type_name,column_value,description FROM sys.dm_xe_object_columns WHERE column_type='customizable'

2 启动扩展事件会话

扩展事件会话创建后，我们还需要启动会话，让 SQL Server 开始记录事件。 #+caption 启动扩展事件会话

ALTER EVENT SESSION  test_event_session ON SERVER STATE=START

会话启动之后，我们可以从下面个视图中查询会话的信息。Listing 3: 查询会话信息

SELECT * FROM sys.server_event_sessions;
GO

SELECT * FROM sys.dm_xe_sessions;
GO

3 读取 xel 文件

启动会话后，SQL Server 会将事件信息保存到 xel 文件中。SQL Server 不会向我们设置的 filename 文件中写入数据，实际的文件名是在 filename 中添加序号和时间戳得到的。这么做是为了进行文件轮转。在我的服务器（SQL Server 2014 SP2）上，默认情况下 SQL Server 为每个事件会话保留最多 5GB 事件数据。这是可以配置的，方法参考上一节。

由于实际文件名不是 filename 参数值，我们需要查询文件名。Listing 4: 查看会话 xel 文件实际名称

SELECT
    n.value('(File/@name)[1]', 'nvarchar(max)') AS xel_filename
FROM
(
    SELECT CAST(targets.target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets AS targets
    JOIN sys.dm_xe_sessions AS xe_sessions ON xe_sessions.address=targets.event_session_address
    JOIN sys.server_event_sessions se_sessions ON se_sessions.name=xe_sessions.name
    WHERE xe_sessions.name='test_event_session'
) td
CROSS APPLY td.target_data.nodes('EventFileTarget') AS q(n) 

得到 xel 实际文件名之后，我们可以查询其中记录的时间信息。这里我们只关心 SQL 执行历史。Listing 5: 从 xel 文件查询事件信息

SELECT
    n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
    n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') AS statement
FROM 
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('Z:\test_event_session_0_132871334271260000.xel', null, null, null)
) ed
CROSS APPLY ed.event_data.nodes('event') AS q(n)

4 停止会话

查询完毕后不要忘了停止会话，以免产生性能损耗。Listing 6: 停止会话

ALTER EVENT SESSION test_event_session ON SERVER STATE=STOP

会话停止以后，对 SQL Server 没有任何性能影响。当然如果不再需要使用这个会话，最好在会话停止后删除会话。Listing 7: 删除会话

DROP EVENT SESSION test_event_session ON SERVER;