Share via

Supporting SQL Server 2008: The system_health session

  • 07/15/2008

In my first post on SQL Server 2008, I introduced a new feature called Extended Events (we like to call this Xevent internally). While we were working with the XEvent development team, it dawned on us that we could perhaps build an event session that was "always-on" (in fact internally we were calling this the "Always-On" session). In this session, we could add some events that were perhaps important for troubleshooting certain problems with the SQL Server Engine.

Thus was born the system_health Extended Events session. So if you install SQL Server 2008 RC0, an run the following query:

select * from sys.dm_xe_sessions

you will find a session that is already running on your server called system_health. The target for this session is a ring_buffer so when you want to see the results in XML you would run a query 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 = 'system_health'

Since this is a ring_buffer based target, when the buffer fills up we will overwrite the oldest entries.

This session has events we at Microsoft defined (in fact, our CSS team were the ones that proposed to the development team what should go in this session). If you want to see the details of what events are in the session, you can see the actual script we use to build it after you install SQL Server 2008 in the INSTALL directory in a file called u_tables.sql (this is important because if you ever accidentally drop the session you can always create it again from this script).

To give you a feel for what we put in this session, here is a summary:

  • The sql_text and session_id for any sessions that encounter an error with severity >=20
  • the sql_text and session_id for any sessions that encounter a "memory" type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
  • A record of any "non-yielding" problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
  • Any deadlocks that are detected
  • The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
  • The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
  • The callstack, sql_text, and session_id for any session that have waited for an extended period of time for "external" waits or "pre-emptive waits".

So what this means to you is that at any time you could take a look at this system_health session data and find out if you have had any high severity errors (and see immediately what query was run that encountered the error), queries that encountered long waits, or other scenarios as described above.

The events we added to this session were ones we felt comfortable could be turned on always without compromising server engine performance.

So anytime you have are having engine issues with SQL Server 2008, take a peek at this session and see what is inside.

In my next post for SQL Server 2008, I'll explain more what "external" waits or "preemptive waits" are and how this new feature in SQL Server 2008 could help pinpoint some unusual performance issues.

 

Bob Ward
Microsoft

clip_image001_2

Comments