Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
Mar 15, 2012
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.
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
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
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
• 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
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...
Geeks who read this article also read…
- How to Back up Database in SQL Server 2012
- How to Restore Database in SQL Server 2012
- Indirect Checkpoint Feature in SQL Server 2012
- Using WITH RESULT SETS Feature of SQL Server 2012
- SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012
- How to Downgrade SQL Server Database from SQL Server 2012 to SQL Server 2008 or SQL Server 2005
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- How to Identify if the database was upgraded from a previous version of SQL Server
- How to Identify Currently Used SQL Server Authentication Mode
- Understanding SQL Server Recovery Models
- Uninstalling SQL Server 2008
- How to Remotely Shutdown, Restart or Log Off Windows Server across the network
- How to Backup an Analysis Services Database in SQL Server 2008 Using SQL Server Management Studio
- How to Restore an Analysis Services Database in SQL Server 2012 Using SQL Server Management Studio
- Read More SQL Server Articles…