SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

创建扩展事件会话

展开“Object Explorer”、“Management”、“Extended Events”、“Sessions”目录,你会发现一到两个预设的会话。默认,在SQL Server 2012包含system_health会话,而根据不同的SQL Server2012的版本,可能有AlwaysOn_health会话。你可以在空闲时检查这些会话,现在,我们来创建一个会话来更好的理解扩展事件是如何工作的。每个会话包含了相同的基础组件。

 

可以通过右键“Sessions”的“New Session Wizard”向导来创建会话,也可以通过T-SQL来创建。这里我们通过右键“Session”的“New Session”,在“General”页,输入会话的名字Check Queries。在这页可以不必配置任何选项,如果没有必要。在“Template”下拉菜单的 “Query Detail Sampling”模板捕获关于T-SQL语句、存储过程和批处理的事件数据。你可以修改预设设置,例如添加或删除事件,或者使用模板设置的配置项。一旦你保存了会话,你就不能使用正在被使用的模板,但你可以按需修改属性设置。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

“General”页的大部分选项都是很明确的。唯一一个不太清晰的的选项是“Casualty tracking”下的“Track how events are related to one another”选项,让你在相关任务之间跟踪事件,当一个任务的工作被另一个做了的时候。

 

这里,我们只输入会话的名称,进入到“Events”页去选择你想监控的事件。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

如果你在“Event library”内选择一个事件,下面会显示它的描述信息。以及相关的事件域的列表。我们添加query_post_execution_showplan和sql_statement_completed事件。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

直接点OK保存。

 

右键点击新建的会话,选择“Start Session”,再次右键点击该会话,选择“Watch Live Data”。运行如下的查询:

 

1
2
3
4
5
6
7
8
9
10
USE AdventureWorks2012
GO
SELECT FROM Production.TransactionHistory
ORDER BY TransactionDate DESC, ReferenceOrderID, ReferenceOrderLineID;
SELECT FROM Person.Person
WHERE BusinessEntityID <> 100
OR BusinessEntityID <> 200
ORDER BY PersonType, Lastname, FirstName;
SELECT FROM Sales.Currency
ORDER BY Name;

 

结果显示如下:

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

该会话对于刚才运行的每一条语句都记录了一个sql_statement_completed事件,每个语句一个query_post_execution_showplan事件,和大量与SQL Server管理相关的query_post_execution_showplan事件。

 

对于query_post_execution_showplan事件,可以看到像duration、estimated_rows和showplan_xml。如果你双击showplan_xml值,该XML将会在新的窗口打开,更具可读性。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

当双击statement的值时,将弹出详细的触发该事件的语句。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

尽管我们只添加了两个事件到我们的会话,我们仍然收到了大量的信息。我们需要添加过滤条件限制返回我们感兴趣的事件。

 

添加过滤条件到会话

过滤条件限制了返回基于特定准则的会话的数量。在添加过滤条件到会话前,先关闭“Live Data”窗口。然后在“Object Explorer”,右键点击该会话选择“Properties”,在“Session Properties”对话框,进入“Events”页点击“Configure”按钮。出现一个让你选择配置过滤条件的域的屏幕。进入到“Filter(Predicate)”窗口,在“Selected events”选择“query_post_execution_showplan”事件。

 

我们对query_post_execution_showplan事件添加了两个过滤条件。第一个过滤条件,针对duration域,大于(>)操作符,值为500000。该事件将返回执行时间大于500000微秒的会话。

 

另一个过滤条件,source_database_id必需等于5,在我的测试环境,AdventureWorks2012数据库的ID为5。我添加该过滤条件,使得该会话只包含与这个数据库相关的query_post_execution_showplan事件。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

再针对sql_statement_completed事件,在duration域,我们添加了相同的过滤条件。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

过滤条件配置完后,点击“OK”关闭会话属性对话框。右键点击“Object Explorer”,选择“Watch Live Data”。再次运行刚才的语句,只有一条语句返回了事件。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

因为只有一条SELECT语句超过了500000微秒。所以,会话只包含一个sql_statement_completed事件和一个query_post_execution_showplan事件。

 

添加域到会话

先关掉“Live Data”窗口,再次打开会话的属性。进入“Events”页点击“Configure”。进入“Global Fields(Actions)”窗口。在全局域,像database_id和database_name域是对多个事件可用。一直以来,添加一个或多个域到事件中用来提供有用的信息。例如,有时候可能去知道哪个query_post_execution_showplan事件与哪个sql_statement_completed事件在结果中是相关的,这比较困难。如果我们添加transaction_id域到每个事件,这就可以更好的关联这些事件。

 

选择“Selected events”中的query_post_execution_showplan事件,在全局域中选择transaction_id域。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

进入“Event Fields”窗口,选择“database_name”域。注意,有些在全局域中列出的域,也会包含在事件域中,像database_name域。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

不论域是在哪里列出的,记住只要你只需要添加必要的域,因为任何你添加的组件都会增加负载。当然,我们不必添加database_name域到这个事件,因为我们的过滤条件只返回针对一个数据库的事件。然后,在这里包含数据库名只是为了解释可选的事件域是如何工作的。

 

现在再针对sql_statement_completed事件重复上述操作,在全局域,选择database_name和transaction_id域。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

现在进入sql_statement_completed事件相关的“Event Field”窗口。你可以选择包含parameterized_plan_handle域和statement域。默认情况下,第一个没有选择,第二个被选择。这里,我们保持默认设置。点击“OK”保存会话关闭会话属性对话框。

 

接下来,打开“Live Data”窗口,运行上述查询。将会看到更详细的信息,点击query_post_execution_showplan事件,可以看到包含数据库名和事务ID,在这里是630312。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

点击sql_statement_completed,也显示了事务ID为630312,以及数据库名。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

可以看到,添加的信息可以帮助我们更好的理解数据的来源,以及这些片段之间的关系。

 

保存会话数据

再次关闭“Live Data”窗口,打开会话属性,进入“Data Storage”页,里面可以选择要保存的格式。

 

SQL Server提供了多种格式来保存会话事件:

l Etw_classic_sync_target:输出事件以Event Tracing for Windows(ETW)格式。

l Event_counter:输出事件到内存记录会话中跟踪事件触发的次数。

l Event_file:输出事件到磁盘上的一个目录的文件。

l Histogram:输出事件到内存基于域和行动分组计数事件。

l Pair_matching:输出事件到内存跟踪基于目标配置没有对应事件的事件。

l Ring_buffer:输出事件到内存跟踪基于目标配置的一定量的事件数据。

 

当选择一种数据存储类型,将会在页面底部有一些选项可供配置如何保存数据。这里,我们选择event_file类型,配置为最大文件为200MB,最大文件数量为10个。点击“OK”保存。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

再次运行上述查询。在SSMS中展开该会话,会看到一个该事件文件的的参照package0.evnet_file,双击该节点。将会打开一个新窗口显示捕获的数据,类似在“Live Data”中显示的。

 

SQL Server扩展事件(Extended Events)-- 使用扩展事件跟踪查询语句

 

当查看完该文件的内容后,关闭该窗口。并右键选择该会话,选择“Stop Session”。当你想再次捕获数据的时候,你再开启。














本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1600824,如需转载请自行联系原作者





上一篇:全球6个最大的数据安全漏洞


下一篇:【原】oracle11G AWR使用及分析