CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Why Should I Use Extended Events in SQL Server 2008?…

Why Should I Use Extended Events in SQL Server 2008?…

  • Comments 2

You may or may not have heard of a new diagnostic technology in SQL Server 2008 called Extended Events (XEvent). I thought I would post an example of why this technology can do things nothing else we have can when you deploy SQL Server 2008.

Someone internally contacted me about a problem they were having with unexplained page splits. They would see their page splits/sec counter in perfmon spike every 15 minutes and could not figure out exactly what queries were causing the problem. While there are several ways to induce what could be causing this, there is a way with extended events to find out exactly what queries are running that cause the split.

Extended Events in SQL Server 2008 is the answer. Today, the interface to create events and sessions is pure T-SQL (although I’ve heard someone in the community has created a GUI for this). Consider the following T-SQL script:

-- xevent\perfmon\xe_page_splits.sql
drop event session pagesplits on server
create event session pagesplits on server
add event sqlserver.page_split
add target package0.ring_buffer
with (max_dispatch_latency=1seconds)
alter event session pagesplits on server state = start

If you run this, now you are capturing information about page splits on your server. When you see the spike, you could then query the results of this session with this query:

select CAST(xet.target_data as xml) from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where xe.name = 'pagesplits'

You would see an XML result that looks something like the following:

<RingBufferTarget eventsPerSec="0" processingTime="0" totalEventsProcessed="140" eventCount="140" droppedCount="0" memoryUsed="104720">
  <event name="page_split" package="sqlserver" id="71" version="1" timestamp="2008-06-25T20:49:05.391Z">
    <data name="file_id">
      <type name="uint16" package="package0" />
      <text />
    <data name="page_id">
      <type name="uint32" package="package0" />
      <text />
    <action name="callstack" package="package0">
      <type name="callstack" package="package0" />
      <text />
    <action name="session_id" package="sqlserver">
      <type name="uint16" package="package0" />
      <text />
    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>-- Now let's put in odd numbers
declare @x int
set @x = 1
declare @y varchar(10)
while (@x &lt; 10000)
    set @y = CAST (@x as varchar(10))
    insert into imgoingtosplit values (@x, @y)
    set @x = @x + 2
      <text />
    <action name="client_app_name" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>Microsoft SQL Server Management Studio - Query</value>
      <text />

There are all types of things you could do here to make the output easier to read. You could shred the XML to put this in a more row like format.

Go back and look at this part of the syntax when I created the session:

add target package0.ring_buffer

This syntax says that my event session data will be stored in memory which is why I queried a DMV to get the results. XEvent supports other “targets” including a file target if you want to store the results on disk (and a system function in T-SQL to get the data out into XML from the file). Check our documentation for the syntax to use these other targets.

There are many other events available with XEvent in SQL Server 2008. As I run into more unique usage for this technology I will post them to our blog.

Bob Ward

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
Page 1 of 1 (2 items)