Information About Waits Stats in SQL Server SQLCLR and Trace 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 SQLCLR related waits, SQL Server SORT related waits, SQL Server Trace related waits, SQL Server TempDB and Log waits.

SQLCLR_APPDOMAIN
Occurs while CLR waits for an application domain to complete startup.

SQLCLR_ASSEMBLY
Occurs while waiting for access to the loaded assembly list in the appdomain.

SQLCLR_DEADLOCK_DETECTION
Occurs while CLR waits for deadlock detection to complete.

SQLCLR_QUANTUM_PUNISHMENT
Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.

SQLSORT_NORMMUTEX
Occurs during internal synchronization, while initializing internal sorting structures.

SQLSORT_SORTMUTEX
Occurs during internal synchronization, while initializing internal sorting structures.

SQLTRACE_BUFFER_FLUSH
Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.

SQLTRACE_FILE_BUFFER
Occurs during synchronization on trace buffers during a file trace.

SQLTRACE_SHUTDOWN
Occurs while trace shutdown waits for outstanding trace events to complete.

SQLTRACE_WAIT_ENTRIES
Occurs while a SQL Trace event queue waits for packets to arrive on the queue.

SRVPROC_SHUTDOWN
Occurs while the shutdown process waits for internal resources to be released to shutdown cleanly.

TEMPOBJ
Occurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.

THREADPOOL
Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.

TIMEPRIV_TIMEPERIOD
Occurs during internal synchronization of the Extended Events timer.

TRACEWRITE
Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.

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 *