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


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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


get scripts

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





Comments For This Article




Tuesday, April 03, 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?

 



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools