Information About Waits Stats in SQL Server Waits – Part 3
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.
May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Occurs while the checkpoint task is waiting for the next checkpoint request.
Occurs at server startup to tell the checkpoint thread that it can start.
Occurs during operations that change the state of a database, such as opening or closing a database.
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.
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.
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.
Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.
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.
Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.
Occurs when a task is currently performing CLR execution and is waiting for a reader lock.
Occurs when a task is currently performing CLR execution and is waiting for a writer lock.
Occurs when a task is currently performing CLR execution and is waiting for a semaphore.
Occurs while waiting for a CLR task to complete startup.
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.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.