SQL服务器扩展事件因果追踪

吴欣
569 阅读10分钟

本文探讨了SQL Server扩展事件会话(XEvents)中的因果关系跟踪选项，以及它在排除性能问题方面的作用。

SQL Server Extended Events(XEvent)简介

SQL Server扩展事件（XEvent）是一个轻量级的工具，用于收集数据进行故障排除。你可以使用XEvent来收集信息，以图形方式查看，并分析它以检测任何性能问题或系统瓶颈。此外，它可以将来自操作系统和SQL Server进程的数据进行关联。扩展事件取代了SQL跟踪和SQL Server分析器。

下面的术语有助于理解扩展事件。

  • 会话状态。它代表了一个扩展事件会话中的不同状态。
    • 创建事件会话
    • alter event session, state=start
    • alter event session, state=stop
    • 删除事件会话
  • 会话内容和特征。它表示一个XEvent会话的内容。例如，目标事件连同它们在一个或多个会话中的相关性。

SQL Server Extended Events

图片参考。微软文档

多个XEvent会话是独立于其他配置的。这些会话不与正在多个会话中使用的任何事件或目标对接。

  • 注意：我建议你使用SQLShack的文章来探索这些扩展事件的功能和配置

使用模板配置SQL Server扩展事件（XEvent）会话

SQL Server包括一些类似于Profiler的预定义模板，供你使用，而不需要选择特定的事件。在SSMS中连接到SQL Server，并导航到管理->扩展事件。

它给出了SQL Server实例中预先配置的XEvent会话的列表。

  • Alwayson_health
  • System_health
  • 遥测_xevents

pre-configured SQL Server Extended Event sessions

要创建一个新的会话，右键单击会话并选择新的会话。

choose New Session

在新建会话窗口中，我们指定以下输入。

  • SQL服务器扩展事件（XEvent）会话名称
  • 模板。这些模板类似于SQL Server剖析器模板。模板描述给出了关于特定模板的目的的简要信息

在这个例子中，我从下拉菜单中选择了标准模板。它捕获了所有交易的SQL批次和存储过程，用于监控一般的数据库活动。

  • 时间表。我们可以将XEvent会话配置为在服务器启动时和配置后立即启动。如果你想在XEvent查看器上观看实时数据，请在捕获时在屏幕上观看实时数据上打勾。
  • 因果关系跟踪。通常情况下，我们会跳过在XEvent会话中配置因果关系跟踪功能。本文将探讨什么是因果关系跟踪，以及我们如何利用它来监控数据库活动。

要打开因果关系跟踪，请勾选 - 跟踪事件之间的关系。

turn on the causality tracking in SQL Server Extended Event

点击事件，它就会根据选择的模板弹出扩展事件。下图显示了标准模板的事件。

  • ***注意：*我们不会在本文中详细介绍各个扩展事件。你可以探索微软的文档或SQLShack的文章来了解详细信息

individual extended event

点击脚本，生成配置的SQL Server扩展事件的等效T-SQL。如下图所示，它包括TRACK_CASUALITY=ON参数。

equivalent T-SQL of the configured XEvent

为了使事情更简单，我使用下面的T-SQL脚本配置 "TestCasualityEvents"。它包括以下的XEvents。

  • sp_statement_completed。这个事件表示一个存储过程中的T-SQL语句已经完成。
  • sql_statement_completed:这个事件表明一个T-SQL语句已经完成。
  • 它使用参数TRACK_CAUSALITY=ON打开因果关系跟踪。
  • 它将捕获的事件数据存储在一个名为TestCasualityEvents的事件文件中。由于我们没有指定任何目录，它将事件文件存储在实例的LOG目录中。因此，你可以导航到LOG目录，查看扩展名为XEL的XEvent文件

CREATE EVENT SESSION TestCasualityEvents

ON SERVER

ADD EVENT sqlserver.sp_statement_completed(

WHERE ([sqlserver].[is_system]=(0))。

ADD EVENT sqlserver.sql_statement_completed(

WHERE ([sqlserver].[is_system]=(0))

ADD TARGET package0.event_file

(设置 文件名 = N'TestCasualityEvents')

与

(

track_causality = on

);

一旦XEvent被设置好，启动它并执行一个SQL事务，在一个事务中包括更新、选择语句。

  • 注意：我们没有添加go语句来分开更新和选择语句

使用 AdventureWorks2017

去

BEGIN TRAN;

UPDATE [AdventureWorks2017].[HumanResources].[雇员]。

设置 [JobTitle] = 'Senior Database Developer';

where BusinessEntityID=1

SELECT [NationalIDNumber]

,[LoginID]...

,[职位名称]

,[出生日期]

,[婚姻状况],[婚姻状况

FROM [AdventureWorks2017].[HumanResources].[雇员］

where BusinessEntityID=1

回滚 tran。

由于上述事务包括多个SQL语句，因果关系跟踪可以帮助我们确定其执行顺序。展开TestCasualityEvents的XEvent，查看事件数据。

默认情况下，XEvent查看器不显示所有列。然而，你可以根据你的要求定制视图。 因此，点击列并选择所需的列，如下图所示。

customize the view

它捕获了XEventsql_statement_completed的数据。在输出中，看一下下面的列。

  • attach_activity_id.guid。这一列中的值对于一个事务中的所有语句都是相似的。它代表了对一个事务中的所有事件保持相同的准则
  • attach_activity_id.seq：它给出了一个事务中的事件序列。它是一个递增的数字
    • 序列#1：内部SQL Server命令，如选择@@SPID和指定的BEGIN TRAN，使用Adventureworks2017语句
    • 序列#2：更新语句
    • 序列#3：选择语句
    • 序列#4：ROLLBACK TRAN语句

View event sequence

在前面的查询中，我们没有使用Go语句来分隔多个语句。因此，让我们运行下面的修改后的代码并查看XEvent会话输出。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

使用 AdventureWorks2017

去

BEGIN TRAN;

UPDATE [AdventureWorks2017].[HumanResources].[雇员]。

设置 [JobTitle] = "高级数据库管理员

where BusinessEntityID=2;

去

SELECT [NationalIDNumber]

,[LoginID］

,[职位名称]

,[出生日期］

,[MaritalStatus]...

FROM [AdventureWorks2017].[HumanResources].[雇员］

where BusinessEntityID=2

滚回 tran。

在上面的查询中，我们在更新和选择语句之间使用了Go batch分隔符。因此，扩展事件输出中，更新和选择T-SQL都没有类似的GUID。

  • 批次#1
    • 序列#1：内部SQL服务器命令，如选择@@SPID和指定的BEGIN TRAN，使用Adventureworks2017语句
    • 序列#2：更新语句
  • 批次#2
    • 序列#1：选择语句
    • 序列#2：ROLLBACK TRAN语句

Workload with GO separator

使用SQL Server扩展事件跟踪存储过程的因果关系

在SQL Server中，存储过程通常用于优化性能，因为SQL Server会缓存执行计划并在随后的执行中重复使用。

在下面的T-SQL脚本中，我们创建了以下存储过程。

  • 存储过程TestSP1, TestSP2, TestSP3包括选择语句和基于参数@BusinessEntityID的过滤记录。
  • 一个存储过程TestSPFinal在内部调用存储过程TestSP1, TestSP2, TestSP3和一个选择语句。这个存储过程被称为嵌套存储过程。
  • 为了测试的目的，我们执行TestSPFinal，并通过因果关系追踪来捕获扩展的事件数据。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

创建 程序 TestSP1 ( @BusinessEntityID int)

流程

开始

SELECT [NationalIDNumber].

,[LoginID］

,[职位名称］

,[出生日期]

,[婚姻状况]...

FROM [AdventureWorks2017].[HumanResources].[雇员］

where BusinessEntityID=@BusinessEntityID

结束

流程

创建 程序 TestSP2 ( @BusinessEntityID int)

流程

开始

SELECT [BusinessEntityID]

,[rowguid], [ModifiedDate

，[ModifiedDate]。

FROM [AdventureWorks2017].[Person].[BusinessEntity].

where BusinessEntityID=@BusinessEntityID

结束

流程

创建 程序 TestSP3 ( @BusinessEntityID int)

流程

开始

选择 [PersonType], [NameStyle]

,[NameStyle],[Title].

,[标题]

,[名]

,[中名]

,[姓]

,[后缀]

,[电子邮件推广]

,[额外的联系信息],[人口统计学] ,[电子邮件促销]。

,[人口统计学]

,[rowguid]

,[ModifiedDate]...

FROM [AdventureWorks2017].[Person].[Person].

where BusinessEntityID=@BusinessEntityID

结束

流程

创建 程序 TestSPFinal( @BusinessEntityID int)

流程

开始

执行 TestSP1 @BusinessEntityID

执行 TestSP2 @BusinessEntityID

执行 TestSP3 @BusinessEntityID

选择[名称], [产品编号

,[ProductNumber],[MakeFlag

,[MakeFlag］

,[FinishedGoodsFlag],[颜色]。

,[颜色］

,[安全库存水平］

,[重订点],[标准成本] ,[安全库存水平] ,[重订点

,[标准成本］

,[清单价格].

,[尺寸]

,[尺寸单位计量代码],[重量单位计量代码] ,[尺寸单位计量代码]。

,[WeightUnitMeasureCode],[WeightUnitMeasureCode]。

FROM [AdventureWorks2017].[生产].[产品]。

结束

GO

假设用户报告了TestSPFinal存储过程的性能问题。你检查了SP的定义，发现它是一个嵌套的SP。性能问题可能出现在定义中嵌套的任何一个存储过程中。你需要单独评估每个SP，并验证其性能和执行计划。这可能是一个复杂而耗时的任务。因果关系跟踪可以帮助你找出哪个SP在执行中花费更多的时间。

执行下面的程序并查看扩展的事件文件数据。

Exec TestSPFinal @BusinessEntityID=1

  • 所有嵌套的存储过程都在一个批次和事务中运行。因此，GUID值对所有的都是一样的
  • 它给你一个存储过程的执行序列
  • 你可以选择Duration 列并分析结果，以查看哪个SP在执行中花费更多的时间。因此，你可以选择该SP并进一步解决它的问题

Nested stored procedure

禁用和启用现有SQL Server扩展事件（XEvent）会话的因果关系跟踪

你可以轻松地禁用和启用现有XEvent会话的因果关系跟踪。打开XEvent会话属性，因果关系跟踪选项在一般页面上。

目前，因果关系跟踪的选项是灰色的，因为XEvent会话正在运行。

Disable and Enable Causality tracking

要改变因果关系跟踪的状态，请停止XEvent会话，改变配置，并再次启动它。另外，你可以使用下面的T-SQL来禁用[TestCasualityEvents]XEvent会话的因果关系跟踪配置。

ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = STOP;

去

ALTER EVENT SESSION [TestCasualityEvents] ON SERVER

with (track_causality=off)

去

ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = START;

去

同样地，如果因果关系跟踪被禁用，使用下面的脚本来启用。

ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = STOP;

去

ALTER EVENT SESSION [TestCasualityEvents] ON SERVER

with (track_causality=on)

去

ALTER EVENT SESSION [TestCasualityEvents] ON SERVER STATE = START;

进展

结论

SQL Server Extended Events会话中的跟踪因果关系选项有助于排除性能问题。了解SQL Server中交易的事件顺序是非常重要的。此外，你可以利用它来跟踪单个存储过程或来自嵌套存储过程的语句性能（持续时间），正如我们在这篇文章中探讨的那样。你可以从微软的文档中探索更多关于扩展事件的内容。

Rajendra Gupta

你好！我是Rajendra Gupta，数据库专家和架构师，帮助企业快速有效地实施Microsoft SQL Server、Azure、Couchbase、AWS解决方案，修复相关问题，并以超过14年的经验进行性能调试。

我是《DP-300 Administering Relational Database on Microsoft Azure》一书的作者。我在MSSQLTips、SQLShack、Quest、CodingSight和SeveralNines上发表了650多篇技术文章。

我是最大的关于单一主题的免费在线文章集之一的创建者，他的50篇关于SQL ServerAlways On Availability Groups的系列文章。

基于我对SQL Server社区的贡献，我在2019年、2020年和2021年连续被评为SQLShack著名的年度最佳作者（排名第二），并在2020年获得MSSQLTIPS的冠军奖。

个人博客：
www.dbblogger.comI，我一直对新的挑战感兴趣，所以如果你需要咨询帮助，请联系我[：rajendra.gupta16@gmail.com](mailto:rajendra.gupta16@gmail.com)

查看Rajendra Gupta发表的所有文章

Rajendra Gupta

avatar
文章
阅读
粉丝