Get Ready for SQL Server 2008

Technorati Tags: SQL Server 2008,Extended Events

Someone who contributes to this blog posted back in April that after the Europe PASS conference they would start in May creating some posts about SQL Server 2008 New Features especially ones related to diagnostics. I don't know who posted that but it is now July and no posts have been seen (who was that guy?). So I guess I'll step in and start creating these posts given that we are marching towards a release of SQL Server 2008 in Q3 of this year.

The first post of this series is to tell you about a new feature for diagnostics in the SQL Server Engine called Extended Events. I've personally spent time working on this feature with the product team and just recently completed some training information for our CSS team on this topic, so this seems like a good time to talk about it.

What is Extended Events? It is a new diagnostic system for the engine that can be used to track interesting places in the code, collect data about them, and even take some action for that event. It is not a replacement for SQLTrace although there is certainly some similarities in how you can use both features.

First, to get primed, take a stroll through the Books Online on this topic at https://msdn.microsoft.com/en-us/library/bb630354(SQL.100).aspx. This gets you a feel for this feature and basics of how it can be used. But if that didn't sync in, let me show you can example T-SQL script to create a session for a particular purpose (NOTE: I recommend you install the latest release candidate to try any of this):

drop event session dumptrigger on server go create event session dumptrigger on server add event sqlserver.error_reported ( action ( sqlserver.session_id, sqlserver.create_dump_all_threads, sqlserver.sql_text ) where error = 602 and package0.counter <= 3 ) add target package0.ring_buffer with (max_dispatch_latency=1seconds) go alter event session dumptrigger on server state = start go

I wrote this script because I found a 602 error in my ERRORLOG file but I wasn't sure what query was running that encountered the error. Furthermore, when I called CSS they said they needed a userdump when this problem occurred to help me.

So if I run this script, I get a new Extended Events session that will only produce data and collect a dump file if the engine encounters another 602 error and only do this up to 3 times as long as this event is running.

If I want to look at the data from this session without using the dump file, I can do that in XML format by looking at  Dynamic Management View (DMV) called sys.dm_xe_session_targets. I can cast the target_data column to XML like this:

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 = 'dumptrigger'

The resulting XML will look something like this:

<RingBufferTarget eventsPerSec="125" processingTime="8" totalEventsProcessed="1" eventCount="1" droppedCount="0" memoryUsed="319">   <event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2008-04-30 16:11:18.602">     <data name="error">       <type name="int32" package="package0" />       <value>602</value>       <text />     </data>     <data name="severity">       <type name="int32" package="package0" />       <value>21</value>       <text />     </data>     <data name="state">       <type name="int32" package="package0" />       <value>30</value>       <text />     </data>     <data name="user_defined">       <type name="boolean" package="package0" />       <value>false</value>       <text />     </data>     <data name="message">       <type name="unicode_string" package="package0" />       <value>Could not find an entry for table or index with partition ID 491897996509184 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB </value>       <text />     </data>     <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>select * from badtable</value>       <text />     </action>   </event> </RingBufferTarget>

This is a fairly advanced diagnostic scenario but the above script could be applied to any error (even ones you raise yourself). This is just an example and doesn't begin to touch on the power of Extended Events. So for my next post, I'll talk more about Extended Events including an event session we ship with the product that is always on helping you look at problems when they occur the first time (including the one I just showed you).

Bob Ward
Microsoft

clip_image001