Bookmark and Share









Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012


Mar 15, 2012

Introduction

SQL Server 2012 introduces a new system stored procedure sp_server_diagnostics which can be used by a Database Administrators to quickly gather System, Resource, Query Processing, IO Sub System and Events information of an SQL Server 2012 instance. DBA can execute this stored procedure in a repeat mode using a SQL Server Agent Job to gather the execution results on a periodic basis.

Permission Required

In order to execute sp_server_diagnostics stored procedure a user should have VIEW STATE PERMISSION on the server.

How to Grant Access to SQL Server Dynamic Management Views (DMV)

In order to query a dynamic management view or a function the user requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

In order to grant access to a user the first step will be to create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. This way you can make sure that users are not having unnecessary permission to query all the DMV’s in the SQL Server

USE MASTER;
GO

GRANT VIEW SERVER STATE TO USERNAME;
GO

Syntax sp_server_diagnostics

USE MASTER
GO

EXEC SP_SERVER_DIAGNOSTICS [@REPEAT_INTERVAL =] 'REPEAT_INTERVAL_IN_SECONDS'
GO

How to execute sp_server_diagnostics System Stored Procedure

Database administrator or a user who has permission to execute the sp_server_diagnostics system stored procedure can execute the below query to gather System, Resource, Query Processing, IO Sub System and Events. The sp_server_diagnostics stored procedure accepts @repeat_interval parameter. The default value for @repeat_interval is 0.

Example - Executing sp_server_diagnostics Procedure

USE MASTER
GO

EXEC SP_SERVER_DIAGNOSTICS
GO			
sp_server_diagnostics Capture Diagnostic Data and Health Information in SQL Server 2012

As per MSDN the detailed description of each of the five components is mentioned below for your reference:-

System: Collects data from a system perspective on spinlocks, severe processing conditions, non-yielding tasks, page faults, and CPU usage. This information is produces an overall health state recommendation.

Resource: Collects data from a resource perspective on physical and virtual memory, buffer pools, pages, cache and other memory objects. This information produces an overall health state recommendation.

Query_Processing: Collects data from a query-processing perspective on the worker threads, tasks, wait types, CPU intensive sessions, and blocking tasks. This information produces an overall health state recommendation.

IO_Subsystem: Collects data on IO. In addition to diagnostic data, this component produces a clean healthy or warning health state only for an IO subsystem.

Events: Collects data and surfaces through the stored procedure on the errors and events of interest recorded by the server, including details about ring buffer exceptions, ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool, spinlocks, security, and connectivity. Events will always show 0 as the state.

Moreover, you can quickly understand each components health using State and State Description columns. For a detailed description of State and State Description check the following MSDN link.

How to continuously execute sp_server_diagnostics System Stored Procedure

To continuously gather System, Resource, Query Processing, IO Sub System and Events information use @repeat_interval parameter with a value greater than 5 seconds as the procedure requires a minimum of 5 seconds to return result. Using the below example to capture the output of sp_server_diagnostics to a table in a non-repeat mode. A database administrator can create an SQL Server Agent Job to capture the output and stored it to a table once in every hour for detailed analysis.

Use DBA
GO

/* create table to store execute sp_server_diagnostics information*/
CREATE TABLE SpServerDiagnostics
(
      create_time DateTime,
      component_type sysname,
      component_name sysname,
      state int,
      state_desc sysname,
      data nvarchar(max)
)
GO

/*Insert execute sp_server_diagnostics  information to a table for detailed analysis*/

Use DBA
GO

INSERT INTO SpServerDiagnostics
EXEC SP_SERVER_DIAGNOSTICS
GO

Conclusion

In this article you have seen how one can quickly gather Diagnostic Data and Health Information of SQL Server 2012 for Performance Analysis.

Reference: sp_server_diagnostics (Transact-SQL)



Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."