Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

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


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

Problem

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.

Solution

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
GO
EXEC sp_server_diagnostics 
GO

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


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 03, 2012 - 10:04:02 AM - Scott Back To Top

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?

 


Learn more about SQL Server tools