- Le plus récent
- Le plus de votes
- La plupart des commentaires
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:
Contenus pertinents
- demandé il y a un an
- demandé il y a un an
- Réponse acceptéedemandé il y a un an
- demandé il y a 10 mois
- AWS OFFICIELA mis à jour il y a 10 mois
- AWS OFFICIELA mis à jour il y a 2 ans
- AWS OFFICIELA mis à jour il y a 2 ans
- AWS OFFICIELA mis à jour il y a 2 ans
My only doubt left is why on all other databases this wait time does not consume a "session" and does not even appear in Performance Insight. But for this instance where the only difference is that it is Multi AZ the graph is always showing 1 session with this wait type.
There is some incorrect information provided here. Clearing the wait stats will not remove any waits from Performance Insights.
I agree @adrian-AWS. I did run the command: "DBCC SQLPERF("sys.dm _os_wait_stats",CLEAR);" on a test environment and it didn't make any difference.