sp_server_diagnostics

SQL Server “Denali” has introduced a new way to capture SQL Server health and diagnostic information with the sp_server_diagnostics stored procedure:

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

The stored procedure runs in “repeat mode” and periodically returns results.  Specifying 0 as the @repeat_interval causes it to run only once.

This is a great way to get a lot of diagnostic information very quickly.  The procedure returns five columns and multiple rows.  The columns are:

• creation_time – the time stamp of the row creation
• component – what data is being returned (system, resource, query_processing, io_subsystem, events), discussed below
• state – the health status of the component
• state_desc – a description of the health status.  The “events” component will always return a value of 0 (Unknown).
• data – the data specific to the component; this is the real “meat” of the procedure

The five components contain a ton of great data.  Here is a brief description of the data that is collected about each component:

• system – spinlocks, severe processing conditions, non-yielding tasks, page faults, and CPU usage
• resource – physical and virtual memory, buffer pools, pages, cache and other memory objects
• query_processing – worker threads, tasks, wait types, CPU intensive sessions, and blocking tasks
• io_subsystem – data on IO
• events – data on the errors and events of interest recorded by the server, including ring buffer exceptions, ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool, spinlocks, security and connectivity.

You can execute this query interactively and view the results on the screen, or you can save those results to a table or a file (see Books Online for details).  Microsoft recommends creating an extended session to save the output to a file so that you can still access it outside of SQL Server if there is a failure.  For systems that are not suffering problems that dramatic, you can capture the data to a table and query it easily.

This is a great way to get a quick snapshot of the system to narrow down the problem quickly.  If you work with remote clients that call you when there is a problem, this is also a very quick and simple way to have them gather information for you.  Thank you, Microsoft, for making this data so easy to capture!

Detailed information about sp_server_diagnostics is available in Books Online.

Note:  this information is based on pre-release documentation.  I will update this post and the links as new “Denali” versions are released.