Database Reference
In-Depth Information
,Data.value('((/event[1]/data[@name="writes"]/value/text())[1])'
,'int') as [Writes]
,Data.value('((/event[1]/data[@name="row_count"]/value/text())[1])'
,'int') as [Rows]
,Data.value('((/event[1]/data[@name="statement"]/value/text())[1])'
,'nvarchar(max)') as [Statement]
,Data.value('xs:hexBinary(((/event[1]/action[@name="plan_handle"]/value/text())[1]))'
,'varbinary(64)') as [PlanHandle]
,File_Name
,File_Offset
from
TargetData
)
select
ei.[Event], ei.[Event Time]
,ei.[CPU Time] / 1000 as [CPU Time (ms)]
,ei.[Duration] / 1000 as [Duration (ms)]
,ei.[Logical Reads], ei.[Physical Reads], ei.[Writes]
,ei.[Rows], ei.[Statement], ei.[PlanHandle]
,ei.File_Name, ei.File_Offset, qp.Query_Plan
from
EventInfo ei
outer apply sys.dm_exec_query_plan(ei.PlanHandle) qp
Further steps depend on your objectives. In some cases, you can see the obvious optimization targets when
you analyze raw event data. In other situations, you need to perform additional analysis and look at the frequency of
executions aggregating data based on query_hash or query_plan_hash actions data.
You may also consider creating a process that runs based on the schedule, extracting newly collected data and
persisting it in a table. This approach increases the chances of capturing query plans if they are still in plan cache. You
can use ring_buffer rather than event_file as the target in such an implementation.
Summary
Extended Events is a lightweight and highly scalable monitoring and debugging infrastructure that will replace SQL
Traces in future versions of SQL Server. It addresses the usability limitations of SQL Traces, and it places less overhead
on SQL Server by collecting only the information required and by performing predicate analysis at a very early stage of
event execution.
SQL Server exposes new Extended Events with every new release. Starting with SQL Server 2012, all SQL Trace
events have corresponding Extended Events. Moreover, new SQL Server features do not provide any SQL Trace
support, relying on Extended Events instead.
Extended Events provides data in XML format. Every event type has its own schema, which includes specific data
columns for that event type. You can add additional information to event data with a global set of available actions,
and you can apply predicates to event data, filtering out events that you do not need.
Event data can be stored in multiple in-memory and on-disk targets, which allows you to collect raw event data or
perform some analysis and aggregation, such as counting and grouping events or tracking an unmatched pair of events.
The system_health event session provides information about general SQL Server component health, resource
usage, and high severity errors. This session is created and is running by default on every instance of SQL Server. One
of the collected events is xml_deadlock_report , which allows you to obtain a deadlock graph for recent deadlocks
without the requirement of setting up SQL Trace or a T1222 trace flag.
Extended Events is a great technology that allows you to troubleshoot very complex scenarios, which is impossible
to do using other methods. Even though the learning curve is steep, it is very beneficial to learn and use Extended Events.
 
Search WWH ::




Custom Search