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

SQLServerF1

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.

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

SECURITY_MUTEX
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.

SEQUENTIAL_GUID
Occurs while a new sequential GUID is being obtained.

SERVER_IDLE_CHECK
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.

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

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

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

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

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

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

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

SLEEP_TEMPDBSTARTUP
Occurs while a task waits for tempdb to complete startup.

SNI_CRITICAL_SECTION
Occurs during internal synchronization within SQL Server networking components.

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

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

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

SOAP_READ
Occurs while waiting for an HTTP network read to complete.

SOAP_WRITE
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.

Thanks,
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 *