Database Reference
In-Depth Information
,respond_to_memory_pressure = 0
)
with
(
max_dispatch_latency=10 seconds
,track_causality=on
);
■
In some cases, depending on the sQL Client library, for example adO.net, you need to capture
rpc_starting
and
rpc_completed
events in addition to the
sql_statement_starting
and
sql_statement_completed
events during
query timeout troubleshooting.
Note
You can examine
pair_matching
data through the
event_data
column in the
sys.dm_xe_session_targets
view.
Listing 28-24 illustrates such an approach.
Listing 28-24.
Examining
pair_matching
target data
;with TargetData(Data)
as
(
select convert(xml,st.target_data) as Data
from sys.dm_xe_sessions s join sys.dm_xe_session_targets st on
s.address = st.event_session_address
where s.name = 'Timeouts' and st.target_name = 'pair_matching'
)
select
t.e.value('@timestamp','datetime') as [Event Time]
,t.e.value('@name','sysname') as [Event]
,t.e.value('(action[@name="session_id"]/value/text())[1]','smallint')
as [SPID]
,t.e.value('(data[@name="statement"]/value/text())[1]','nvarchar(max)')
as [SQL]
from
TargetData cross apply
TargetData.Data.nodes('/PairingTarget/event') as t(e)
System_health and AlwaysOn_Health Sessions
One of the great features of the Extended Events framework is the
system_health
event session, which is created and
is running on every SQL Server installation by default. This session captures various types of information about the
status and resource usage of SQL Server components, high severity and internal errors, excessive waits for resources
or locks, and quite a few other events. The session uses
ring_buffer
and
event_file
/
asynchronous_file_target
targets to store the data.
The
system_health
session is started on SQL Server startup by default. It gives you an idea of what recently
happened in a SQL Server instance as you begin troubleshooting. Moreover, recent critical events have already been
collected without requiring you to set up any monitoring routines.
One such example is deadlock troubleshooting. The
system_health
session collects the
xml_deadlock_report
event. Therefore, when customers complain about deadlocks, you can analyze already collected data without waiting
for the next deadlock to occur.
