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
go
create event session pagesplits on server
add event sqlserver.page_split
(
action
     (package0.callstack,
     sqlserver.session_id,
     sqlserver.sql_text, 
     sqlserver.client_app_name,
     sqlserver.database_id
     )
)
add target package0.ring_buffer
with (max_dispatch_latency=1seconds)
go
alter event session pagesplits on server state = start
go

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" />
      <value>1</value>
      <text />
    </data>
    <data name="page_id">
      <type name="uint32" package="package0" />
      <value>153</value>
      <text />
    </data>
    <action name="callstack" package="package0">
      <type name="callstack" package="package0" />
      <value>0x0000000002762548
0x0000000001F6E5A4
0x0000000001122495
0x000000000112BF16
0x000000000112C18E
0x000000000112C066
0x000000000111F1C1
0x000000000106E926
0x00000000011100CC
0x00000000010807C5
0x000000000107FB57
0x0000000001103126
0x0000000001102F5A
0x0000000001081A6A
0x0000000001083093
0x00000000010843E9</value>
      <text />
    </action>
    <action name="session_id" package="sqlserver">
      <type name="uint16" package="package0" />
      <value>54</value>
      <text />
    </action>
    <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)
begin
    set @y = CAST (@x as varchar(10))
    insert into imgoingtosplit values (@x, @y)
    set @x = @x + 2
end
</value>
      <text />
    </action>
    <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
Microsoft