如何使用SQL Server扩展事件来监控查询性能

在这篇文章中，我们将探讨如何使用SQL Server扩展事件来监控SQL Server的查询性能。

简介

监控查询性能是发现性能不佳的查询的重要诊断方法之一。然而，有时这可能是一个有点困难的活动。因此，我们必须使用适当的工具和方法来监控和分析查询的性能指标。为了监测查询性能，我们可以使用以下工具。

• 查询存储
• 动态管理视图和函数（DMV）
• 扩展事件
• 性能监测器
• 活动监控
• 第三方SQL服务器监控工具
• SQL Server管理工作室（SSMS）报告

在本文的下一节中，我们将重点介绍SQL Server扩展事件的使用细节，以监测查询性能。

SQL Server扩展事件是一个性能监测工具，它有助于收集和监测数据库引擎的动作，以诊断SQL Server中的问题。微软在SQL Server 2008中引入了扩展事件，然后决定退出SQL分析器。在做出这一决定的背后，SQL剖析器存在一些缺点。剖析器的主要问题与性能有关，因为它消耗了许多系统资源，这种情况对数据库的性能有负面影响。SQL Server的扩展事件并不像剖析器那样影响SQL Server的性能，它还提供了许多事件，帮助解决查询性能和其他问题。如要解决死锁问题，它可以是第一选择，根据我的想法，它必须是第一选择。

识别长期运行的查询

SQL Server常见的抱怨问题之一是长期运行的查询。出于这个原因，作为第一步，我们需要检测长期运行的查询，以便对这些查询进行故障排除。要定义一个查询的运行时间是有问题的，必须为相关数据库确定一定的阈值，因为数据库环境的等待容忍度会发生变化。有些系统不能容忍微秒级的等待，而有些系统对长的等待时间有更高的阈值。正如我们所说，我们可以使用不同的工具来找出表现不佳的查询。然而，使用扩展事件可以是诊断我们数据库引擎中这些查询的有效方法之一。在这一点上，令人迷惑的问题将是使用哪个事件，因为SQL Server提供了许多事件。sql_statement_completed事件捕获了所有在数据库中执行的查询，它涉及以下指标。

• CPU时间
• 查询执行的时间
• 逻辑读取的数量
• 物理读数的数量
• SQL文本和语句
• 写入的数量
• 客户端主机名
• 客户端应用程序名称

创建一个扩展事件会话来识别长期运行的查询

我们可以使用SQL Server管理工作室（SSMS）来创建一个SQL Server扩展事件会话。首先，我们展开管理文件夹，右键点击会话。

在菜单上，我们点击新建会话 ...选项，创建一个新的扩展事件会话，然后会出现新建会话窗口。在这个窗口中，我们给我们的扩展事件会话起一个名字，并选中以下选项。

• 在服务器启动时启动该事件会话
• 在会话创建后立即启动事件会话。

这两个选项允许在数据库服务启动时自动启动扩展事件会话，也允许在会话创建后自动开始收集数据。

在给会话命名后，我们跳到事件标签。事件是当一个特定动作发生时被触发的对象。在这个屏幕上，我们可以找到所有的事件并可以过滤sql_statement_completed事件。找到sql_statement_completed事件后，我们通过右箭头按钮把它送出到选定的事件列表中。

我们点击配置按钮来选择全局事件字段。事件字段是事件的元素，它给出了事件的特定部分的信息。如，要找到触发事件的应用程序名称，我们需要选择客户端应用程序名称字段。我们在全局字段（Action）列表中选择客户端应用程序名称和客户端主机名。

我们可以在**过滤器（谓词）**标签上过滤任何特定的事件字段。对于我们的示例SQL Server扩展事件会话，我们只过滤数据库名称，这样我们将只跟踪在该数据库中执行的SQL语句。

同时，如果我们已经为表现不佳的查询设置了一个阈值，我们可以过滤持续时间。这个选项可以帮助我们排查查询性能问题。

在 "数据存储"选项卡上，我们将选择SQL Server扩展事件将收集的数据存储在哪里。我们选择事件文件，将收集的数据写入XEL文件。 在选择事件_文件类型后，我们需要定义文件位置和文件的最大尺寸。当启用文件滚动选项被选中时，在扩展事件XEL文件达到这个最大文件大小限制后，将创建一个新的XEL文件，文件名相同，但在文件名后面附加一个序列号。

在高级选项卡上，我们可以找到一些详细的设置。我们可以在事件存储模式选项中设置我们对创建的会话的数据丢失的容忍度。我们将选择 "无事件损失"，但这个选项可能会影响系统性能，对工作负荷大的数据库不利。另一个选项是 "最大调度延迟"，这个选项规定了事件在被写入目标之前可以在缓冲区中保留的最大时间。最后，我们单击 "确定"按钮并创建扩展事件。

创建扩展事件会话后，它将被添加到扩展事件列表中。

分析收集的数据

在我们创建扩展事件会话后，它将自动启动并开始收集数据。我们可以使用观察实时数据选项来分析所收集的数据。例如，在Adventureworks数据库中执行以下查询后，这个查询将被扩展事件捕获。

SELECT  SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER  JOIN Sales.SalesOrderDetail SD
ON SO.ModifiedDate = SD.ModifiedDate

然而，使用查询是可以更好地分析查询性能，并能更有助于解决查询性能问题。要做到这一点，首先我们需要找到XEL文件的位置，并在fn_xe_file_target_read_file的帮助下读取该文件。

SELECT s.name, CAST(t.target_data AS XML).value( '(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)' ) AS fileName
FROM sys.dm_xe_sessions AS s
        INNER JOIN
        sys.dm_xe_session_targets AS t
        ON s.address = t.event_session_address
WHERE t.target_name = 'event_file'
        AND name = 'LongRunningQueries';
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file( 'C:\CapturedEvents\LongRunningQueries_0_132741419843740000.xel', NULL, NULL, NULL );

我们可以看到，捕获的数据是以XML格式存储的，我们需要将它们转换为关系格式。下面的查询返回在数据库上产生最多I/O工作量的TOP10查询。

drop table if exists #EX_FilePath
drop table if exists #AnalyzeTable
SELECT s.name, CAST(t.target_data AS XML).value( '(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)' ) AS fileName
INTO #EX_FilePath
FROM sys.dm_xe_sessions AS s
        INNER JOIN
        sys.dm_xe_session_targets AS t
        ON s.address = t.event_session_address
WHERE t.target_name = 'event_file';
DECLARE @EventFileTarget AS NVARCHAR(500);
SELECT @EventFileTarget = fileName
FROM #EX_FilePath
WHERE name = 'LongRunningQueries';
SELECT * INTO #AnalyzeTable
FROM(SELECT n.value( '(@name)[1]', 'varchar(50)' ) AS event_name, 
n.value( '(@package)[1]', 'varchar(50)' ) AS package_name, 
n.value( '(@timestamp)[1]', 'datetime2' ) AS [utc_timestamp],
n.value( '(data[@name="physical_reads"]/value)[1]', 'bigint' ) AS physical_reads, 
n.value( '(data[@name="logical_reads"]/value)[1]', 'bigint' ) AS logical_reads, 
n.value( '(data[@name="writes"]/value)[1]', 'bigint' ) AS writes, 
n.value( '(data[@name="row_count"]/value)[1]', 'bigint' ) AS row_count, 
n.value( '(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)' ) AS client_app_name,
n.value( '(data[@name="cpu_time"]/value)[1]', 'bigint' ) AS cpu_time, 
n.value( '(data[@name="duration"]/value)[1]', 'bigint' ) / 1000 AS duration_ms, 
n.value( '(data[@name="statement"]/value)[1]', 'nvarchar(max)' ) AS sql_text
        FROM
        (
            SELECT CAST(event_data AS XML) AS event_data
            FROM sys.fn_xe_file_target_read_file( @EventFileTarget, NULL, NULL, NULL )
        ) AS ed
        CROSS APPLY
        ed.event_data.nodes( 'event' ) AS q(n)) AS TMP_TBL
 
SELECT TOP 10 sql_text AS Sql_Statment ,COUNT(*) AS NumberOfQueries,
SUM(logical_reads) AS TotalLogicalReads FROM #AnalyzeTable
GROUP BY sql_text
ORDER BY 3 DESC

毫无疑问，我们可以为这个查询创建其他的替代方案，在不同的方面分析查询的性能。

如何用SQL Server扩展事件检测TempDb的溢出情况

查询优化器估计一个查询会返回多少行，然后根据这个估计，它向SQL Server数据库引擎请求内存授予。在某些情况下，优化器会做出不准确的估计，所以内存授予对查询来说是不够的。在这种情况下，SQL Server在执行查询时需要很多内存，它使用tempdb数据库。然而，使用tempdb而不是内存并不是一个好的选择，所以它降低了查询的性能。我们可以使用SQL Server的扩展事件来检测在tempdb中被溢出的查询。溢出显示了在语句溢出数据时被写入的页面数量。例如，我们启用主动执行计划并执行以下查询。

SELECT top 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER  JOIN Sales.SalesOrderDetail SD
ON SO.ModifiedDate = SD.ModifiedDate
ORDER BY SO.AccountNumber DESC
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'),MAXDOP 1);

同时，我们可以看到在SQL Server扩展事件会话中执行查询时有多少页被溢出。

总结

在这篇文章中，我们学习了如何使用SQL Server扩展事件来监控SQL Server中的查询性能。