Capture Diagnostic Data and Health Information in SQL Server 2012 using sp_server_diagnostics System Stored Procedure

By:   |   Updated: 2012-03-30   |   Comments (1)   |   Related: More > Monitoring


SQL Server 2012 has a lot of new features to offer and one of these features is the new system stored procedure SP_SERVER_DIAGNOSTICS which can be used to capture diagnostic data and SQL Server Health information.  In this tip we take a look at this new system stored procedure.


In this tip we will discuss how to use SP_SERVER_DIAGNOSTICS a new system stored procedure which is available in SQL Server 2012.

A user with VIEW SERVER STATE permission on the server can use SP_SERVER_DIAGNOSTICS to capture diagnostic data and health information for a SQL Server to detect potential failures.

Here is sample code to run this command:

Use master
EXEC sp_server_diagnostics 

The above stored procedure also accepts a parameter @repeat_interval with a default value of 0. If you want the stored procedure to run continuously then the minimum value for the @repeat_interval parameter should be 5 seconds as it takes at least 5 seconds to return the complete result set.

In the below snippet you can see the output once the stored procedure is executed.

Capture Diagnostic Data and Health Information in SQL Server 2012

Using the SP_SERVER_DIAGNOSTICS stored procedure one can gather System, Resource, Query Processing, IO Sub System and Events information.

Let's go through each of these components in detail to know what one should expect once the SP_SERVER_DIAGNOSTICS stored procedure is executed successfully.

System: - It collects information with respect to CPU Usage, Page Faults, Non Yielding Tasks, Latches, Access Violations, Dumps and Spinlock Activity.
Resource: - It collects data such as Physical and Virtual Memory, Page Faults, Cache, Buffer Pools and other relevant memory related objects.
Query Processing: - It collects data with respect to Query Processing such as Wait Types, Tasks, Worker Threads, CPU Intensive Requests and Blocking tasks etc.
IO Subsystems: - It collects data with respect to IO such as IO Latch Time outs, Interval Long IO's, Longest Pending Requests etc.
Events: -It collects data such as ring buffer exceptions, ring buffer events about memory broker, buffer pool, spinlocks, security, out of memory, scheduler monitor etc.

In addition to the above, the stored procedure result set also indicates the health of each component using State and State Description columns. The below table describes each state and its description as listed in MSDN.

State State Description
0 Unknown
1 Clean
2 Warning
3 Error
Next Steps

Last Updated: 2012-03-30

get scripts

next tip button

About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the community since 2009 with over 60 tips.

View all my tips
Related Resources

Comments For This Article

Tuesday, April 3, 2012 - 10:04:02 AM - Scott Back To Top (16753)

This looks liek there would be a lot of overlap witht he SQL Server Data Collection routines.  Is this meant to replace the Data Collection, supplement it or simply run in addition to it?



Recommended Reading

Determining space used for all tables in a SQL Server database

Killing a SPID and Checking Rollback progress in SQL Server

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

Different techniques to identify blocking in SQL Server

get free sql tips
agree to terms