sp_server_diagnostics

 

/*

Captures diagnostic data and health information about SQL Server to detect potential failures.

The procedure runs in repeat mode and sends results periodically. It can be invoked from either a regular or a DAC connection.

 

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

 

descriptions of the five components:

 

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.

 

From a failure perspective, the system, resource, and query_processing components will be leveraged for failure detection while the io_subsystem and events components will be leveraged for diagnostic purposes only.

 

 

The following table maps the components to their associated health states.

 

Components	Clean (1)	Warning (2)	Error (3)	Unknowns (0)
system	x	x	x
resource	x	x	x
query_processing	x	x	x
io_subsystem	x	x
events				x

The (x) in each row represents valid health states for the component. For example, io_subsystem will either show as clean or warning. It will not show the error states.

 

 

*/

 

 

exec sp_server_diagnostics