Information About Waits Stats in SQL Server Waits – Part 5

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 Deadlock waits in SQL Server, Waits related to SQL Server Versioning.

DEADLOCK_ENUM_MUTEX
Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.

DEADLOCK_TASK_SEARCH
Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.

DEBUG
Occurs during Transact-SQL and CLR debugging for internal synchronization.

DISABLE_VERSIONING
Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.

ENABLE_VERSIONING
Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

DISKIO_SUSPEND
Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.

DISPATCHER_QUEUE_SEMAPHORE
Occurs when a thread from the dispatcher pool is waiting for more work to process. The wait time for this wait type is expected to increase when the dispatcher is idle.

DLL_LOADING_MUTEX
Occurs once while waiting for the XML parser DLL to load.

DROPTEMP
Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.

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.

 

Information About Waits Stats in SQL Server related to Database Mirroring waits and parallelism 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 Database Mirroring waits and parallelism waits.

CMEMTHREAD
Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.

CXPACKET
Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism.

CXROWSET_SYNC
Occurs during a parallel range scan.

DAC_INIT
Occurs while the dedicated administrator connection is initializing.

DBMIRROR_DBM_EVENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

DBMIRROR_DBM_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
– Although this is provided for informational purposes, this generally happens with database mirroring in both Synchronous and Asynchronous configuration. This some times can correlate with the huge amount of activity on the principal database or due to contention.

DBMIRROR_EVENTS_QUEUE
Occurs when database mirroring waits for events to process.

DBMIRROR_SEND
Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.

DBMIRROR_WORKER_QUEUE
Indicates that the database mirroring worker task is waiting for more work.

DBMIRRORING_CMD
Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.

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.

 

Information About Waits Stats in SQL Server Waits – Part 3

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 CLR waits and Checkpoint waits.

BUILTIN_HASHKEY_MUTEX
May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.

CHECK_PRINT_RECORD
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

CHECKPOINT_QUEUE
Occurs while the checkpoint task is waiting for the next checkpoint request.

CHKPT
Occurs at server startup to tell the checkpoint thread that it can start.

CLEAR_DB
Occurs during operations that change the state of a database, such as opening or closing a database.

CLR_AUTO_EVENT
Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.

CLR_CRST
Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.

CLR_JOIN
Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.

CLR_MANUAL_EVENT
Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.

CLR_MEMORY_SPY
Occurs during a wait on lock acquisition for a data structure that is used to record all virtual memory allocations that come from CLR. The data structure is locked to maintain its integrity if there is parallel access.

CLR_MONITOR
Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.

CLR_RWLOCK_READER
Occurs when a task is currently performing CLR execution and is waiting for a reader lock.

CLR_RWLOCK_WRITER
Occurs when a task is currently performing CLR execution and is waiting for a writer lock.

CLR_SEMAPHORE
Occurs when a task is currently performing CLR execution and is waiting for a semaphore.

CLR_TASK_START
Occurs while waiting for a CLR task to complete startup.

CLRHOST_STATE_ACCESS
Occurs where there is a wait to acquire exclusive access to the CLR-hosting data structures. This wait type occurs while setting up or tearing down the CLR runtime.

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.

 

Information About Waits Stats in SQL Server Waits – Part 2

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 Service Broker waits.

BAD_PAGE_PROCESS
Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.

BROKER_CONNECTION_RECEIVE_TASK
Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.

BROKER_ENDPOINT_STATE_MUTEX
Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.

BROKER_EVENTHANDLER
Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.

BROKER_INIT
Occurs when initializing Service Broker in each active database. This should occur infrequently.

BROKER_MASTERSTART
Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.

BROKER_RECEIVE_WAITFOR
Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.

BROKER_REGISTERALLENDPOINTS
Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.

BROKER_SERVICE
Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.

BROKER_SHUTDOWN
Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.

BROKER_TASK_STOP
Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.

BROKER_TO_FLUSH
Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.

BROKER_TRANSMITTER
Occurs when the Service Broker transmitter is waiting for work.

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.

 

Information About Waits Stats in SQL Server Waits – Part 1

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 Backup, Network and SQL Server Audit related waits.

ABR
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

ASSEMBLY_LOAD
Occurs during exclusive access to assembly loading.

ASYNC_DISKPOOL_LOCK
Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.

ASYNC_IO_COMPLETION
Occurs when a task is waiting for I/Os to finish.

ASYNC_NETWORK_IO
Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

AUDIT_GROUPCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.

AUDIT_LOGINCACHE_LOCK
Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.

AUDIT_ON_DEMAND_TARGET_LOCK
Occurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.

AUDIT_XE_SESSION_MGR
Occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.

BACKUP
Occurs when a task is blocked as part of backup processing.

BACKUP_OPERATOR
Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.

BACKUPBUFFER
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPIO
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPTHREAD
Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.

PARALLEL_BACKUP_QUEUE
Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

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.

 
1 2 3