SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强

在SQLServer中，利用扩展事件可获取Session级等待信息，而SQLServer 2016对此功能进行了进一步增强。

SQL Server中利用扩展事件获取Session级别等待信息及SQL Server 2016的增强功能解析

技术内容：

在数据库性能调优过程中，了解Session级别的等待信息至关重要，通过获取这些信息，我们可以诊断并解决与数据库性能相关的问题，在SQL Server中，可以使用扩展事件（Extended Events）来捕获Session级别的等待信息，本文将介绍如何在SQL Server中使用扩展事件获取Session级别的等待信息，并探讨SQL Server 2016中对这一功能的增强。

扩展事件简介

扩展事件是SQL Server提供的一种轻量级性能监控工具，用于收集数据库引擎的操作数据，它具有以下优点：

1、性能开销较低：扩展事件在捕获数据时对系统性能的影响较小。

2、灵活性：扩展事件可以自定义事件、数据列和筛选条件，方便用户根据需求进行监控。

3、可扩展性：扩展事件支持多种目标输出，如环形缓冲区、文件、Windows事件日志等。

使用扩展事件获取Session级别等待信息

1、创建扩展事件会话

我们需要创建一个扩展事件会话，用于捕获Session级别的等待信息，以下是一个示例脚本：

-- 创建扩展事件会话
CREATE EVENT SESSION [Session_Wait_Info] ON SERVER 
ADD EVENT sqlserver.session_wait(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(131072)) AND [sqlserver].[is_system]=(0))
),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(131072)) AND [sqlserver].[is_system]=(0))
)
ADD TARGET package0.ring_buffer(SET max_memory=(10240))
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 [Session_Wait_Info] ON SERVER STATE = START
GO

2、查询扩展事件数据

在创建并启用扩展事件会话后，我们可以通过以下方式查询捕获的Session级别等待信息：

-- 查询扩展事件数据
SELECT
    te.name AS EventName,
    te.event_id AS EventID,
    OBJECT_NAME(te.object_id) AS ObjectName,
    te.post_time AS PostTime,
    te.duration AS Duration,
    te.cpu_time AS CPU_Time,
    te.reads AS Reads,
    te.writes AS Writes,
    te.logical_reads AS LogicalReads,
    t.target_name AS TargetName,
    JSON_VALUE(data, '$.client_app_name') AS ClientAppName,
    JSON_VALUE(data, '$.client_hostname') AS ClientHostName,
    JSON_VALUE(data, '$.database_id') AS DatabaseID,
    JSON_VALUE(data, '$.session_id') AS SessionID,
    JSON_VALUE(data, '$.sql_text') AS SQLText
FROM
    sys.dm_xe_session_targets t
JOIN
    sys.dm_xe_sessions s ON t.event_session_address = s.address
JOIN
    sys.dm_xe_session_events e ON s.address = e.event_session_address
JOIN
    sys.dm_xe_package_event_mappings pem ON e.event_name = pem.event_name
JOIN
    sys.dm_xe_objects te ON pem.object_name = te.name
JOIN
    sys.dm_xe_object_columns tec ON te.object_id = tec.object_id
JOIN
    sys.dm_xe_session_event_actions sea ON e.event_id = sea.event_id
JOIN
    sys.dm_xe_session_events dee ON sea.event_session_address = dee.event_session_address
OUTER APPLY
    sys.dm_xe_session_object_columns_data(dee.event_id, tec.column_id, t.target_name) AS data
WHERE
    s.name = 'Session_Wait_Info'
ORDER BY
    te.post_time DESC
GO

3、停止并删除扩展事件会话

在完成性能分析后，我们可以停止并删除扩展事件会话，以避免持续的性能开销：

-- 停止扩展事件会话
ALTER EVENT SESSION [Session_Wait_Info] ON SERVER STATE = STOP
GO
-- 删除扩展事件会话
DROP EVENT SESSION [Session_Wait_Info]
GO

SQL Server 2016中的增强功能

在SQL Server 2016中，微软对Session级别等待信息的功能进行了增强，以下是一些重要的改进：

1、增加了新的等待类型：SQL Server 2016引入了更多等待类型，帮助用户更准确地诊断性能问题。

2、改进了等待信息的列：在扩展事件中，SQL Server 2016增加了一些新的列，如CPU时间、读取次数、写入次数和逻辑读取次数，以便更好地了解资源消耗。

3、等待资源信息：SQL Server 2016扩展事件提供了更多关于等待资源的信息，例如等待的锁类型、等待的数据库文件等。

通过使用扩展事件，我们可以方便地获取SQL Server中Session级别的等待信息，从而诊断并解决性能问题，SQL Server 2016在扩展事件方面进行了增强，提供了更多等待类型和详细信息，帮助用户更好地了解数据库引擎的内部操作，掌握扩展事件的使用和SQL Server 2016中的增强功能，对于数据库性能调优具有重要意义。