Information About Waits Stats in SQL Server Query Execution 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 Query Processing and Query Execution related waits.

QPJOB_KILL
Indicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.

QPJOB_WAITFOR_ABORT
Indicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.

QRY_MEM_GRANT_INFO_MUTEX
Occurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.

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

QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN
Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.

QUERY_NOTIFICATION_MGR_MUTEX
Occurs during synchronization of the garbage collection queue in the Query Notification Manager.

QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX
Occurs during state synchronization for transactions in Query Notifications.

QUERY_NOTIFICATION_TABLE_MGR_MUTEX
Occurs during internal synchronization within the Query Notification Manager.

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

QUERY_OPTIMIZER_PRINT_MUTEX
Occurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.

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

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

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 *