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.

 

Leave a Reply

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