Databases Reference
In-Depth Information
Once you have started the session and the ring buffer has started having event data written to it, it's
possible to query its content using a DMV to extract its content in a meaningful format, as shown in
the following query:
select events.value('(event/@timestamp)[1]', 'datetime2') as [Event_Time_UTC],
events.value('(event/action[@name="nt_username"]/value)[1]',
'varchar(100)') as [NT_Username],
events.value('(event/action[@name="client_hostname"]/value)[1]',
'varchar(100)') as [Client_Hostname],
events.value('(event/action[@name="client_app_name"]/value)[1]',
'varchar(100)') as [Client_Appname]
from (select event_data.query('.') as events
from
(select cast(target_data as xml) as target_data
from sys.dm_xe_session_targets xt
join sys.dm_xe_sessions xs
on xs.address = xt.event_session_address
where xs.name = 'Logins_rb'
and xt.target_name = 'ring_buffer' ) as data
cross apply target_data.nodes ('RingBufferTarget/event') as
results(event_data)) as tab (events)
order by [Event_Time_UTC]
The results the query returns are the same as those for the other targets you've previously queried;
the difference is their source isn't a physical i le anymore.
As you can see from the preceding query, you're now getting to levels of complexity far beyond
what a newcomer to Extended Events would be dealing with. However, if that's something you're
interested in investigating further, then I recommend reading the blog posts of the SQL Server com-
munity's Extended Events expert Jonathan Kehayias. Not only has Jonathan published some great
detailed articles about Extended Events but also some invaluable scripts as well; in fact the previous
query was based on one of Jonathan's.
SUMMARY
This chapter discussed a number of aspects of Extended Events, and at this point you should know
enough to start using them and making them a central part of your troubleshooting toolkit.
Extended Events are rapidly becoming Microsoft's preferred troubleshooting tool for SQL Server,
replacing the SQL Trace and Proi ler tools, which will be retired in a future version. Even now, while
all these tools are available, Extended Events are rapidly becoming the preferred choice to trouble-
shoot production systems, especially those with the highest workloads. The load using Extended
Events is minimal, indeed almost zero when compared to tools that use the SQL Trace feature.
Additionally, Microsoft has begun exposing more in-depth diagnostic data to end users through
Extended Events. Events that might have required development debugging tools a few years ago can
now be accessed as easily as the most high-level event data generated by SQL Server.
Search WWH ::
Custom Search