1. 扩展事件介绍

1.1 作用

扩展事件体系结构使用户能够收集尽可能多的或尽可能少的数据，以排查或识别SQL Server、Azure SQL数据库和Azure SQL 托管实例中的性能问题。

扩展事件具有高度可配置性、轻量级和很好的缩放能力。

扩展事件替换了已弃用的 SQL 跟踪和SQL Server Profiler功能。

1.2 优点

扩展事件是使用最少性能资源的轻型性能监视系统。 SQL Server Management Studio 提供了一个图形用户界面，供扩展事件用于创建和修改会话以及显示和分析会话数据。 

2.快速入门

2.1 准备工作

实际执行即将进行的演示时，需做好以下准备工作。

1. 下载 SQL Server Management Studio (SSMS) 每个月应安装 SSMS 最新的每月更新。
2. 登录到 Microsoft SQL Server 2014 或更高版本。
3. 确保你的帐户具有更改任意事件会话服务器权限。

如果有兴趣了解有关扩展事件的安全性和权限的更多详细信息，请参阅本文末尾的附录。

2.2 UI 方式

1. 1.与 SSMS 连接。

2. 2.在对象资源管理器中，依次单击“管理” >“扩展事件” >“新建会话” 。 “新建会话” 对话框优于“新建会话向导” ，虽然两者非常相似。

3. 3.在左上角，单击“常规” 页。 然后在“会话名称” 文本框中，键入 YourSession 或者任何你喜欢的名称。 暂时不要按“确定” 按钮，该按钮要在演示结束时按。

在左上角，单击“事件” 页，然后单击“选择” 按钮。

在“事件库” 区域的下拉列表中，选择“仅事件名称” 。

• 在文本框中键入 sql，这可以通过使用“包含” 运算符筛选并缩短可用事件长列表。

• 滚动并单击名为 sql_statement_completed的事件。

• 单击右箭头按钮 > ，将事件移动到“ 所选事件 ”框。

继续留在“事件” 页，单击最右边的“配置” 按钮。

为方便显示，左边已被截掉，在下面的屏幕截图中，你可以看到“事件配置选项” 区域。

1. 单击“筛选器(谓词)” 选项卡。接下来，单击“单击此处可添加子句” ，以便捕获所有具有 HAVING 子句的 SQL SELECT 语句。

2. 在“字段” 下拉列表中，选择“sqlserver.sql_text” 。

   • 在“值” 中，键入 %SELECT%HAVING% 。

   • 在“运算符” 中，选择 LIKE 运算符。
     

1. 在左上角，单击“数据存储” 页。

2. 在“目标”区域中，单击“单击此处添加目标” 。
    - 在“类型” 下拉列表中，选择“event_file” 。
    - 这意味着，事件数据将存储在我们可以查看的文件中。

3. 在“属性” 区域的“服务器上的文件名” 文本框中，键入完整路径和文件名。
    - 文件扩展名必须为 .xel。
    - 我们的小测试需要小于 1 MB 的文件。

1. 在左上角，单击“高级” 页。

   • 将“最大调度滞后时间” 减少至 3 秒。

   • 最后，单击底部的“确定” 按钮。

返回到对象资源管理器，展开“管理” >“会话” ，并查看会话的新节点。

编辑事件会话

在 SSMS 的对象资源管理器中，可以通过右键单击事件会话的节点来编辑该会话，单击“属性” 。 此时将显示相同的多页对话框。

2.3 T-SQL 方式

2.3.1 创建事件会话

你之前使用 SSMS UI 生成了一个创建事件会话的 T-SQL 脚本。 你可以看到生成的脚本，如下所示：

• 右键单击会话节点，依次单击“编写会话脚本为” >“CREATE 到” >“剪贴板” 。

• 粘贴到任意文本编辑器中。

CREATE EVENT SESSION [YourSession]
    ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (
        ACTION(sqlserver.sql_text)
        WHERE
        ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%')
        )
    )
    ADD TARGET package0.event_file
    (SET
        filename = N'C:\Junk\YourSession_Target.xel',
        max_file_size = (2),
        max_rollover_files = (2)
    )
    WITH (
        MAX_MEMORY = 2048 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 3 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
    );
GO

2.3.2 事件会话预删除

在 CREATE EVENT SESSION 语句之前，你可能想要在名称已存在的情况下有条件地发出 DROP EVENT SESSION。

IF EXISTS (SELECT *
      FROM sys.server_event_sessions
      WHERE name = 'YourSession')
BEGIN
    DROP EVENT SESSION YourSession
          ON SERVER;
END
go

2.3.3 启、停事件会话

在创建事件会话时，默认为不自动开始运行该会话。 你可以随时使用以下 T-SQL ALTER EVENT SESSION 语句启动或停止事件会话。

ALTER EVENT SESSION [YourSession]
      ON SERVER
    --ON DATABASE
    STATE = START;   -- STOP;

你可以选择指示事件会话在 SQL Server 实例启动时自动启动。 请参阅 CREATE EVENT SESSION 上的 STARTUP STATE = ON 关键字。

    - SSMS UI 在“新建会话” >“常规” 页上提供相应的复选框。

2.4 测试事件会话

通过执行以下这些简单步骤来测试事件会话：

1. 在 SSMS 的对象资源管理器中，右键单击事件会话节点，然后单击“启动会话” 。

2. 多次运行SELECT.....HAVING语句。
    - 理想情况下，可能会在两次运行之间将 HAVING Count 值在 2 和 3 之间切换。 这样你就可以看到结果中的差异。

3. 右键单击会话节点，然后单击“停止会话” 。

4. 有关 如何使用 SELECT 查看结果，请阅读下一小节。

SELECT
        c.name,
        Count(*)  AS [Count-Per-Column-Repeated-Name]
    FROM
             sys.syscolumns  AS c
        JOIN sys.sysobjects  AS o
            ON o.id = c.id
    WHERE
        o.type = 'V'
        AND
        c.name like '%event%'
    GROUP BY
        c.name
    HAVING
        Count(*) >= 3   --2     -- Try both values during session.
    ORDER BY
        c.name;

2.5 查看数据

2.5.1 T-SQL

SELECT
        object_name,
        file_name,
        file_offset,
        event_data,
        'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!'
                AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
        CAST(event_data AS XML) AS [event_data_XML]
                -- TODO: In ssms.exe results grid, double-click this xml cell!
    FROM
        sys.fn_xe_file_target_read_file(
            'C:\Junk\YourSession_Target_0_131085363367310000.xel',
            null, null, null
        );

# 这个文件就是之前配置的时候写的路径

上述 SELECT 为你提供两种方法来查看任何给定事件行的完整结果：

• 在 SSMS 中运行 SELECT，然后单击 event_data_XML 列中的单元格。 这种方法非常方便。

• 从 event_data 列的单元格中复制长 XML 字符串。 粘贴到任意简单的文本编辑器中，如 Notepad.exe，并将字符串保存在扩展名为 .XML 的文件中。 然后使用浏览器打开该 .XML 文件。

2.5.2 UI

这里只截图查看文件数据及查看实时数据的方法

查看文件数据

查看实时数据

2.5.3 xml

如果数据中有类似上面这种 xml 的执行计划，则查看方式：

双击该栏位，另存为*.sqlplan 文件 -->> SSMS -- >> 文件 -- >> 打开 -- >> 文件 -- >> 选择刚刚的 sqlplan 后缀的文件即可

2.5.4 xml 转 table

找到文件位置

select s.name as xe_session_name,
    cast(st.target_data as xml) as target_data
from sys.dm_xe_sessions s 
inner join sys.dm_xe_session_targets st 
    on s.address=st.event_session_address
where s.name='xxxxxx';

# xxxx 这里写 扩展事件的名字

执行转换

# 其中文件位置需替换成上一步查到的路径， object_name 需要写你需要查看的扩展事件名称，如果有多个，就用 in 
SELECT 
 DATEADD(hour,8,[XML Data].value('(/event[@name=''sql_batch_completed'']/@timestamp)[1]','DATETIME')) AS [TimeStamp],
 [XML Data].value('(/event/action[@name=''database_name'']/value)[1]','SYSNAME') AS [Database Name],
 [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','SYSNAME') AS [SQL_Text],
 [XML Data].value('(/event/action[@name=''username'']/value)[1]','SYSNAME') AS username,
 [XML Data].value('(/event/action[@name=''task_time'']/value)[1]','BIGINT') AS task_time,
 [XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT') AS [Duration (us)],
 [XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','BIGINT') AS [cpu_time],
 [XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','BIGINT') AS physical_reads,
 [XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','BIGINT') AS logical_reads,
 [XML Data].value('(/event/data[@name=''writes'']/value)[1]','BIGINT') AS writes,
 [XML Data].value('(/event/data[@name=''row_count'']/value)[1]','BIGINT') AS row_count,
 [XML Data].value('(/event/data[@name=''batch_text'']/value)[1]','SYSNAME') AS batch_text
 FROM
    (SELECT CONVERT(XML, event_data) AS [XML Data]
 FROM  sys.fn_xe_file_target_read_file(N'D:\MSSQL\EventLog\slow_query_0_133166686786020000.xel',NULL,NULL,NULL) where OBJECT_NAME='sql_batch_completed') AS v;