SQL Server 2008使用扩展事件进行高级故障排除

全世界的 SQL Server DBA 都有一个似乎永远无法解决的难题：故障排除，其中所执行的绝大多数故障排除都是为了查找某种性能问题。即使是最精心构建并经过测试的应用程序系统都会随着时间的推移发生变化，进而导致可能出现重大的性能问题。

例如，工作负载可能会发生变化（如并发用户的数量、需要执行的查询数以及需要运行的新月末报告）、需要处理的数据量可能会增加、运行系统的硬件平台可能会发生变化（如处理器内核数量、可用的服务器内存数量以及 I/O 子系统容量），另外还可能引入新的并发工作负载（如事务复制、数据库镜像以及更改数据捕获）。

但这些并不是问题的全部。在设计和测试应用程序系统时，设计中往往会出现很多意外的问题，这些也都需要进行故障排除。显然，无论在应用程序生命周期的哪个阶段发现问题，都必须通过故障排除来找出原因并给出解决方案。

在一个复杂的应用程序系统中，可能会有许多需要进行分析的硬件和软件组件，但我关注的只是 SQL Server。抛开各种性能故障排除方法不谈（这些属于本文以外的内容），您对 SQL Server 进行故障排除时都需要哪些工具呢？

SQL Server 2005 中的故障排除

在最近的几个 SQL Server 版本中，可用于性能故障排除的工具大为增加。SQL Server 包含大量的 DBCC（数据库控制台命令）命令，可用于深入了解数据库引擎各个部分的当前状况。此外还包括 SQL Server Profiler，它可以通过编程的方式使用底层 SQL Trace 机制。

尽管 SQL Server 为进行故障排除不断加以改进，但这些选择依然存在某些问题。DBCC 输出的后处理显得有些笨拙，因为必须先将输出结果转储到一个临时表中然后才能对其进行操作。而且，如果配置不当，在运行 SQL Trace/Profiler 时可能会导致性能急剧降低（例如在某个繁忙系统中跟踪所有“Lock:Acquired”（锁定:获得）和“Lock:Released”（锁定:释放）事件但却忘记筛选该事件的 DatabaseId 和 ObjectId 列）。图 1 中的屏幕快照显示的是用于配置筛选器以进行新跟踪的对话框。

图 1 在 SQL Server 2008 Profiler 中配置筛选器

SQL Server 2005 添加了动态管理视图和函数（统称为 DMV）帮助从数据库引擎获取信息。DMV 废弃了某些 DBCC 命令、系统表和存储过程，同时公开了许多新的引擎工作区域。这些 DMV 是一些功能强大且可以组合的命令，可用在复杂的 T-SQL 语句中以筛选 DMV 结果并进行后处理。

例如，图 2 中所示的代码只返回数据库中所有索引的叶级碎片和页密度（均已圆整），它还包括一个碎片级筛选器。使用原来的 DBCC SHOWCONTIG 命令则很难实现这一点。（有关 DMV 的详细信息，请参阅“动态管理视图和函数 (Transact-SQL)。”此外，SQL Server 2005 还添加了大量可用于故障排除的其它功能，包括 DDL（数据定义语言）触发器和事件通知。图 2 使用 DMV 获取功能强大的结果

SELECT 
  OBJECT_NAME (ips.[object_id]) AS 'Object Name',  
  si.name AS 'Index Name',  
  ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',  
  ips.page_count AS 'Pages',  
  ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density' 
FROM sys.dm_db_index_physical_stats (  
  DB_ID ('SQLskillsDB'), NULL, NULL, NULL, 'DETAILED') ips  
CROSS APPLY sys.indexes si  
WHERE 
  si.object_id = ips.object_id  
  AND si.index_id = ips.index_id  
  AND ips.index_level = 0 -- only the leaf level  
  AND ips.avg_fragmentation_in_percent > 10; -- filter on fragmentation  
GO 

Microsoft 内部的各种团队也提供了许多有用的性能故障排除工具，如 SQLdiag 实用工具、SQL Server RML 实用工具、SQL Server 2005 性能仪表板报告和DMVStats。此外还有用于 SQL Server 2005 的Windows 事件跟踪 (ETW) 提供程序，它可将 SQL Trace 事件与 Windows 其它部分中的事件集成在一起。

尽管 SQL Server 2005 在增强 DBA 对数据库引擎进行故障排除的能力方面取得了长足进步，但仍存在许多 DBA 几乎无法有效进行故障排除的情况。一个经常被引用的示例就是某些查询会使用过量的 CPU 资源，但 DMV 却无法提供足够的信息来确定究竟是哪些查询导致了这些问题。但与 SQL Server 2005 不同，SQL Server 2008 可通过一个名为 SQL Server 扩展事件的新功能来突破此类限制。

扩展事件

扩展事件系统的功能远远超出了 SQL Server 在以前提供的任何事件跟踪和故障排除机制。在我看来，扩展事件系统具有如下特色：

事件同步触发，但可同步或异步进行处理。

任何目标都可以消耗任何事件，而任何动作都可以与任何事件配对，从而能够更深入地监控系统。

“智能”谓词允许您使用布尔逻辑来构建复杂的规则。

可以使用 Transact-SQL 对扩展事件会话进行全面控制。

可以监控性能关键代码而不会对性能产生影响。

在深入讲述之前，我先花一些时间来定义一些新术语。

事件 事件是指代码中定义的点。此类示例包括：T-SQL 语句完成执行时的点或结束获取锁定时的点。每个事件都有一个定义的负载（该事件返回的列的集合），它是使用 ETW 模型（其中每个事件都返回一个通道和关键字作为负载的一部分）来定义的，以便能够与 ETW 集成。SQL Server 2008 最初提供 254 个定义的事件，预计在今后还会增加。

使用下列代码可以查看这些定义事件的列表：

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
  AND xo.[object_type] = 'event' 
ORDER BY xp.[name]; 

使用下列代码可以查找某个特定事件的负载：

SELECT * FROM sys.dm_xe_object_columns  
  WHERE [object_name] = 'sql_statement_completed';  
GO 

请注意，扩展事件系统包含一组说明性的 DMV，用来描述所有事件、目标等。有关详细内容，请参阅“SQL Server 扩展事件动态管理视图。”

谓词 谓词是指在事件消耗前利用一组逻辑规则来筛选事件的方法。谓词可以很简单，如检查事件负载中的其中一个返回列是否为某个特定值（例如，通过对象 ID 来筛选“锁定-获得”事件）。

它们还具有一些高级功能，如统计会话期间某个特定事件发生的次数、仅允许事件在发生一次后消耗，或者动态更新谓词本身以抑制包含类似数据的事件的消耗。

谓词可以使用布尔逻辑来编写，以使其能够尽可能走捷径。这使得只需执行最少数量的同步处理即可确定是否需要消耗事件。

动作 动作是指在消耗某个事件前同步执行的一组命令。任何动作都可以被链接到任何事件。它们通常会收集大量数据并追加到事件负载中（如 T-SQL 堆栈或查询执行计划）或执行某个被追加到事件负载中的计算。

由于执行这些动作可能需要极高的代价，因此事件的动作仅在所有谓词都计算完毕后才执行，如果在随后确定该事件不会被消耗，则将不会有同步执行某个动作的点。使用下列代码可找到预定义动作的列表：

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
  AND xo.[object_type] = 'action' 
ORDER BY xp.[name]; 

目标 目标只提供一种消耗事件的方法，任何目标都可以消耗任何事件（或至少可以在目标空闲时对事件进行处理 — 如获取非审计事件的审计目标）。目标可以同步（例如，触发事件的代码等待该事件被消耗）或异步消耗事件。

目标的范围从简单的使用者（如事件文件和环缓冲区）直到能够执行事件配对操作的复杂使用者。使用下列代码可以找到可用目标的列表：

有关目标的详细内容，请参阅“SQL Server 扩展事件目标。”

数据包 数据包是一个用于定义扩展事件对象（如事件、动作和目标）的容器。数据包位于它所描述的模块（如可执行程序或 DLL）中，如图 3 所示

图 3 模块、数据包和扩展事件对象之间的关系

使用扩展事件引擎注册数据包时，它所定义的全部对象都可供使用。有关数据包和扩展事件术语完整列表的详细信息，请参阅“SQL Server 扩展事件数据包。”

会话 会话是一种将多个扩展事件对象链接到一起进行处理的方法 — 事件包含动作，将被目标所消耗。会话可链接任何注册的数据包中的对象，任何数量的会话都可以使用同一个事件、动作等。使用下列代码可查看已定义了哪些扩展事件会话：

SELECT * FROM sys.dm_xe_sessions;  
Go 

可使用 T-SQL 命令来创建、删除、更改、停止和启动会话。可以想见，这将提供很多灵活性，甚至提供通过对会话本身所捕获的数据进行程序化分析来动态更改会话的功能。有关会话的详细内容，请参阅“SQL Server 扩展事件会话。”

性能注意事项

使用 CREATE EVENT SESSION 将扩展事件会话放置在一起时，需要认真正确配置一些设置，因为它们可能会在无意中对性能产生影响。首先需要决定是以同步还是异步方式消耗事件。正如您所料，同步目标对所监控代码的性能产生的影响要大过异步目标。

如前所述，同步消耗某个事件时，触发该事件的代码必须一直等待，直到该事件被消耗为止。显然，如果事件消耗是一个复杂的过程，则这可能会降低代码的性能。

例如，在一个每秒处理数千个小事务的繁忙系统中，同步消耗 sql_statement_completed 事件及捕获查询计划动作很可能会对性能产生负面影响。另外还要记住，谓词始终是同步执行的，因此应注意不要为性能关键代码触发的事件创建过于复杂的谓词。

另一方面，您可能会被强制同步消耗事件。要计算某个特定事件的出现次数，最简单的方法很可能是使用 synchronous_event_counter 目标。

您必须考虑的第二件事情是如果决定使用异步目标，那么该如何配置事件缓冲。事件缓冲的默认可用内存数为 4MB。事件被触发，随后被目标消耗，期间的默认调度延迟为 30 秒。这意味着如果您希望每 10 秒生成一些事件统计数据，则必须对延迟时间进行调整。

对用于缓冲事件的内存进行分区的方法是将其绑定到事件缓冲设置。默认情况下是为整个实例创建一组缓冲区。在 SMP（对称多处理器）和 NUMA（非统一内存访问）计算机中，这可能会使处理器不得不等待访问内存，从而导致出现性能问题。

第三个注意事项是如何处理事件丢失。在定义扩展事件会话时，可指定事件是否可以“丢失”。这意味着如果没有足够的内存来缓冲某个事件，则可将其直接丢弃。默认设置是允许丢弃单个事件，但也可以允许整个事件缓冲区都丢失（适用于事件缓冲区很快就被填满的会话），甚至还可以指定任何事件都不得丢失。使用最后一个选项时应格外注意，因为它会强制触发事件的代码一直等待，直到有足够的缓冲区内存来存储该事件为止。设置此选项几乎肯定会对性能产生不利影响。请注意，如果无意间启用了此选项，服务器仍会拥有足够的响应能力让您能够禁用此设置。

通常，您需要通盘考虑这些选项。实际上我并没有办法为您提供通用的最佳方法，我能做的只是向您强调深入领会它们，否则您可能会遇到性能问题。有关这些设置的详细信息，请参阅“CREATE EVENT SESSION (T-SQL)”。

事件的寿命

定义并启动扩展事件会话后，处理过程将照常进行，直到所监控的代码遇到某个事件为止。图 4 介绍了扩展事件系统所遵循的步骤。具体步骤如下：

1.执行检查以查看是否有任何扩展事件会话正在监控该事件。如果没有，控制权将返给包含该事件的代码，然后继续进行处理。

2.确定事件的负载，将所需的全部信息都收集到内存中 — 换言之，构建事件的负载。

3.如果为该事件定义了任何谓词，则执行它们。此时，谓词结果可能是不应消耗该事件。如果是这种情况，控制权将返给包含该事件的代码，然后继续进行处理。

4.此时系统已得知事件将被消耗，因此将执行链接到该事件的所有动作。现在事件已具有完整的负载，已为消耗做好准备。

5.将事件提供给同步目标（如果有的话）。

6.如果存在任何异步目标，将会缓冲该事件以便随后进行处理。

7.控制权将返给包含该事件的代码，然后继续进行处理。

图 4“扩展事件”事件的寿命（单击图像可查看大图）

如前所述，在创建事件会话时应格外注意，要避免同步动作或异步目标的缓冲对所监控代码的性能产生影响。

使用扩展事件

SQL Server 2008 联机丛书中包括两个扩展事件的使用示例：“如何：确定哪些查询持有锁”和“如何：查找具有最多锁定的对象。”

我将通过一个对扩展事件会话进行设置和对结果进行分析的示例对此加以讲解。当我在 2007 年年末使用扩展事件时，我发现把简单的会话组装起来非常容易（直接使用 T-SQL DDL 语句即可），但对结果进行分析却比较麻烦。

这些结果均以 XML 形式表示，最初这令我很吃惊，但随后我意识到面对可能被收集到单个会话中的大量可能的事件和动作的组合，要想存储此类可扩展架构，可能再也没有比这更可行的选择了。

现在，我已在 SQL Server 存储引擎团队担任开发人员多年，虽然我自认为已经相当精通 C、C++ 和汇编编程程序，但从 XML 数据中通过编程方式找出提取事件负载字段所需的代码仍花了我数小时的时间。我并不是要劝阻您使用扩展事件，相反，我只是告诫您如果还不熟悉 XML 数据的使用，那么最好在查看结果前对学习曲线有所准备。

我的具体情况是这样的：我是一名 DBA，使用 SQL Server 2008 的资源管理器功能在公司的其中一台生产服务器上对各种组合进行沙箱测试。我创建了两个资源管理器资源池（开发部和市场部），用来表示使用该服务器的团队。利用资源管理器可以限制每个池的 CPU 和查询执行内存使用情况，但不能限制它们所使用的 I/O 资源数量。因此，我想根据各个团队在该服务器上的 I/O 使用情况向其开具账单，以建立一种摊销升级到新 SAN（存储区域网络）所花成本的退单机制。

我设想触发捕获 I/O 信息的最佳时机是在任何 T-SQL 语句完成时，并且我还知道数据包 package0 中有一个名为 sql_statement_completed 的事件。那么在事件负载中都收集到了哪些数据呢？

执行下列代码将为我提供所有数据的列表（既包括读取的，也包括写入的）

SELECT [name] FROM sys.dm_xe_object_columns  
  WHERE [object_name] = 'sql_statement_completed';  
GO 

我并不认为这些是实际的读取和写入（这时数据是从磁盘读取或向磁盘写入，而不是仅在缓冲池的内存中），但它们可使我了解各个团队使用的 I/O 资源的比例。

现在，我需要找出究竟是哪个团队执行了哪个特定的 T-SQL 语句，因此需要有一个动作来通知我。执行此代码可以为我提供事件触发时我所能采取的所有动作的列表，其中包括在 sqlserver 数据包中收集 session_resource_pool_id 的动作：

SELECT xp.[name], xo.*  
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp  
WHERE xp.[guid] = xo.[package_guid]  
   AND xo.[object_type] = 'action' 
ORDER BY xp.[name]; 

我可以得到已为资源管理器定义的资源池的列表，并将其与扩展事件会话所收集到的 ID 相关联。现在我已准备好定义会话。请注意，在执行此代码时，将首先检查是否存在相同名称的事件会话。如果发现具有同名的事件会话，它会将其删除。以下是相关代码：

IF EXISTS (  
SELECT * FROM sys.server_event_sessions  
    WHERE name = 'MonitorIO')  
DROP EVENT SESSION MonitorIO ON SERVER;  
GO  
 
CREATE EVENT SESSION MonitorIO ON SERVER  
ADD EVENT sqlserver.sql_statement_completed  
  (ACTION (sqlserver.session_resource_pool_id))  
ADD TARGET package0.ring_buffer;  
GO 

然后它将创建一个包含单个事件 sql_statement_completed 的新会话，同时执行 session_resource_pool_id 动作，将所有内容都记录到环缓冲区中（此时我仍在原型设计阶段）。（在生产中，我很可能会选择使用异步文件目标。）

要启动会话，需要执行下列代码：

ALTER EVENT SESSION MonitorIO ON SERVER  
STATE = START;  
GO 

现在它已启动并开始运行。

在模拟了市场部和开发部的一些活动后，我已做好对会话结果进行分析的准备。下列代码将从环缓冲区中提取数据：

SELECT CAST(xest.target_data AS XML) StatementData  
  FROM sys.dm_xe_session_targets xest  
JOIN sys.dm_xe_sessions xes ON 
  xes.address = xest.event_session_address  
WHERE xest.target_name = 'ring_buffer' 
  AND xes.name = 'MonitorIO';  
GO 

但是，它会将数据作为一个很大的 XML 值提取出来。如果需要将其进一步分解，可使用图 5 所示的代码。 图 5 分解 XML 数据

SELECT 
  Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,  
  Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,  
  Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID  
FROM 
(SELECT CAST(xest.target_data AS XML) StatementData  
  FROM sys.dm_xe_session_targets xest  
  JOIN sys.dm_xe_sessions xes ON 
    xes.address = xest.event_session_address  
  WHERE xest.target_name = 'ring_buffer' 
    AND xes.name = 'MonitorIO') Statements  
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results);  
GO 

这种做法效果不错，但会为捕获的每个事件都生成一行输出。这并不是一种可怕的格式，另外我还希望得到汇总输出，因此我决定使用派生表，如图 6 所示。图 6 获取聚合输出大功告成！其中肯定包含一些复杂的代码，但它们都运行正常。至此我已得到了我所需的结果。看一下有关我的测试数据的本次查询输出，如图 7 所示。

SELECT DT.ResourcePoolID,  
  SUM (DT.Reads) as TotalReads,  
  SUM (DT.Writes) AS TotalWrites  
FROM 
(SELECT   
  Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,  
  Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,  
  Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID  
FROM 
(SELECT CAST(xest.target_data AS XML) StatementData  
  FROM sys.dm_xe_session_targets xest  
  JOIN sys.dm_xe_sessions xes ON 
    xes.address = xest.event_session_address  
  WHERE xest.target_name = 'ring_buffer' 
    AND xes.name = 'MonitorIO') Statements  
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results)) AS DT  
WHERE DT.ResourcePoolID > 255 –- only show user-defined resource pools  
GROUP BY DT.ResourcePoolID;  
GO 

我知道资源池 256 用于市场部，而 257 用于开发部，因此这些数字对于我弄清这些团队在公司中的数据库使用情况很有意义。如果不使用扩展事件，我可能无法轻松得出这些结果。

最后，我将使用下列代码来停止此会话：

要了解此示例中各阶段输出的详细内容，请查看本文随附的屏幕演示。其地址为：technetmagazine.com/video。

system_health 扩展事件会话

实际上 SQL Server 2008 还提供了一个名为 system_health 的预定义会话，它被设置为默认运行。此会话源自产品支持团队的想法，它可以跟踪通常被用来对客户系统进行调试的信息（例如当客户系统发生死锁或出现其它严重错误时）。此会话的创建和启动是 SQL Server 2008 实例安装过程的一部分，它在环缓冲区中跟踪事件，因此不会消耗太多内存.

您可以使用下列代码来查看环缓冲区中包含的内容：

Microsoft PSS SQL 支持博客中包含很多有关此会话跟踪内容的详细信息。

结束语

有人告诉我 SQL Server 团队计划未来向 sqlserver.exe 中添加更多事件。事实上，其数量已从 2007 年二月 CTP（社区技术预览版）中的 165 猛增到 RTM（批量生产版）中的 254。

此外还有许多的确非常有趣的事件，如用于更改数据捕获的事件（我已在 2008 年 11 月《TechNet 杂志》中的“跟踪企业数据库中的更改”中对其做过介绍）、数据压缩事件以及索引页分割事件等。索引页分割看起来有望找出那些导致性能降低碎片的索引，而无需在全部索引中定期运行 sys.dm_db_index_physical_stats DMV。

【编辑推荐】

1. SQL Server 2000删除实战演习
2. SQL Server存储过程的命名标准如何进行？
3. 卸载SQL Server 2005组件的正确顺序
4. 对SQL Server字符串数据类型的具体描述
5. SQL Server数据类型的种类与应用