Multi AZ SQL Server Performance Insights always with SP_SERVER_DIAGNOSTICS_SLEEP wait type

Hi,

SP_SERVER_DIAGNOSTICS_SLEEP is a wait type when the background system health monitor thread is waiting between checking whether to run the sp_server_diagnostics procedure. This was added for SQL Server 2012 to aid with automatic detection of failures to allow automatic failover with availability groups.

"sp_server_diagnostics" was introduced in SQL Server 2012 and it provides a quick assessment of a SQL instance by capturing and returning health and event related information that is conveniently categorized for us. As per the below given documentation[1], it 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" is run by an internal SQL server account that the Windows cluster uses to check the status of the SQL server.

For more details on SP_SERVER_DIAGNOSTICS_SLEEP - This wait time is accumulated while there is no data to be captured by the server diagnostics program. Server Diagnostics captures configuration changes and certain audit events. This is an idle wait, so you can safely ignore it.

In addition to this, you cannot kill the spid for process generating 'SP_SERVER_DIAGNOSTICS_SLEEP' wait as it is an internal program. When you try to kill the spid, you will get the below error:

==> Query to get the spid of process for "SP_SERVER_DIAGNOSTICS_SLEEP":

select * from sys.sysprocesses where lastwaittype ='SP_SERVER_DIAGNOSTICS_SLEEP'

==> Error:

Msg 6107, Level 14, State 1, Line 3 Only user processes can be killed.

References:

[1] https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-server-diagnostics-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15