Information About Waits Stats in SQL Server Security, SLEEP and Networking Related Waits


SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to Security related waits, SQL Server SLEEP related waits, SQL Server Networking related waits and SQL Server HTTP waits.

Occurs after a failed attempt to drop a temporary security key before a retry attempt.

Occurs when there is a wait for mutexes that control access to the global list of Extensible Key Management (EKM) cryptographic providers and the session-scoped list of EKM sessions.

Occurs while a new sequential GUID is being obtained.

Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.

Occurs while a shutdown statement waits for active connections to exit.

Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.

Occurs during database startup while waiting for all databases to recover.

Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.

Occurs when SQL Trace waits for the msdb database to complete startup.

Occurs during the start of a background task while waiting for tempdb to complete startup.

Occurs when a task sleeps while waiting for a generic event to occur.

Occurs while a task waits for tempdb to complete startup.

Occurs during internal synchronization within SQL Server networking components.

Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.

Occurs while waiting for non-uniform memory access (NUMA) nodes to update state change. Access to state change is serialized.

Occurs when there is a wait for all tasks to finish during a NUMA node state change.

Occurs while waiting for an HTTP network read to complete.

Occurs while waiting for an HTTP network write to complete.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Leave a Reply

Your email address will not be published. Required fields are marked *