Details about EXECSYNC Wait Type in SQL Server

SQLServerF1

When a query is submitted to SQL Server for execution, SQL Server will generate a optimal query plan based on the statistics and indexes. Any query will need to read the data from memory or write to memory and in between it has to read from disk or write to disk. In this whole process, there are CPU, Memory, Disk and Network resources utilized and depending up on the amount of data involved and the resource speed, SQL Server will have to wait for the task to be completed by the respective resource and until then SQL Server may end up in waiting for it to complete. SQL Server provides with the wait type it is waiting on, to help us identify where it is waiting on, so that we can look into improving the speed of the processing with by improving the hardware or by tuning the queries. There are many wait types defined in SQL Server and can be found by querying sys.dm_os_wait_stats DMV.

Recently we encountered an issue, where we observed EXECSYNC wait type while running a reindex job. Interestingly, this wait type was encountered when we tried to stop the job, as it was running long and was causing blocking to user queries, but the job did not stop immediately and was waiting on this wait type EXECSYNC. This is not a popular wait type, so checking the description about this wait type, found this Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state. If we check from sys.sysprocessess, we can notice that the state of the request was in KILL/ROLLBACK state. We monitored and few minutes, the rollback of the reindex job completed and the wait type and the session ended. Up on further research, found that this wait type can be ignored, instead we need to focus on the reindex job on why it is running long and need to run it during off hours when there are least number of users using the database.

For more information regarding wait types in SQL Server, they are mainly categorized into below types.
Resource waits – Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects
Queue waits – Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks. These tasks will wait for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.
External waits – External waits occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish. When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Information About Waits Stats in SQL Server Extended Events 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 Extended Events related waits.

XE_BUFFERMGR_ALLPROCESSED_EVENT
Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.

XE_BUFFERMGR_FREEBUF_EVENT
Occurs when either of the following conditions is true:
An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned.
Audits experience a delay. This can indicate a disk bottleneck on the drive where the audits are written.

XE_DISPATCHER_CONFIG_SESSION_LIST
Occurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following:
An Extended Events session is registering with a background thread pool.
The background thread pool is calculating the required number of threads based on current load.

XE_DISPATCHER_JOIN
Occurs when a background thread that is used for Extended Events sessions is terminating.

XE_DISPATCHER_WAIT
Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.

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

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

XE_PACKAGE_LOCK_BACKOFF
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.

 

Information About Waits Stats in SQL Server In-Memory OLTP and Transactions 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 Hekaton related waits or In-Memory OLTP related waits, SQL Server Transactions related waits .

WAIT_FOR_RESULTS
Occurs when waiting for a query notification to be triggered.

WAIT_XTP_CKPT_CLOSE
Occurs when waiting for a checkpoint to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_CKPT_ENABLED
Occurs when checkpointing is disabled, and waiting for checkpointing to be enabled.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_CKPT_STATE_LOCK
Occurs when synchronizing checking of checkpoint state.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_GUEST
Occurs when the database memory allocator needs to stop receiving low-memory notifications.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_HOST_WAIT
Occurs when waits are triggered by the database engine and implemented by the host.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_OFFLINE_CKPT_LOG_IO
Occurs when offline checkpoint is waiting for a log read IO to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_OFFLINE_CKPT_NEW_LOG
Occurs when offline checkpoint is waiting for new log records to scan.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_PROCEDURE_ENTRY
Occurs when a drop procedure is waiting for all current executions of that procedure to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TASK_SHUTDOWN
Occurs when waiting for an In-Memory OLTP thread to complete.
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TRAN_COMMIT
Occurs when execution of a natively compiled stored procedure is waiting for an XTP transaction to commit (waiting for transactions dependent on for instance).
Applies to: SQL Server 2014 through SQL Server 2014.

WAIT_XTP_TRAN_DEPENDENCY
Occurs when waiting for transaction dependencies.
Applies to: SQL Server 2014 through SQL Server 2014.

XTPPROC_CACHE_ACCESS
Occurs when for accessing all natively compiled stored procedure cache objects.
Applies to: SQL Server 2014 through SQL Server 2014.

XTPPROC_PARTITIONED_STACK_CREATE
Occurs when allocating per-NUMA node natively compiled stored procedure cache structures (must be done single threaded) for a given procedure.
Applies to: SQL Server 2014 through SQL Server 2014.

WAITFOR
Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.

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

WAITSTAT_MUTEX
Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.

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

WORKTBL_DROP
Occurs while pausing before retrying, after a failed worktable drop.

WRITE_COMPLETION
Occurs when a write operation is in progress.

WRITELOG
Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

XACT_OWN_TRANSACTION
Occurs while waiting to acquire ownership of a transaction.

XACT_RECLAIM_SESSION
Occurs while waiting for the current owner of a session to release ownership of the session.

XACTLOCKINFO
Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.

XACTWORKSPACE_MUTEX
Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.

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 Transaction Synchronization 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 Transaction Synchronization related waits.

TRAN_MARKLATCH_DT
Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_EX
Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_KP
Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

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

TRAN_MARKLATCH_SH
Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRAN_MARKLATCH_UP
Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.

TRANSACTION_MUTEX
Occurs during synchronization of access to a transaction by multiple batches.

UTIL_PAGE_ALLOC
Occurs when transaction log scans wait for memory to be available during memory pressure.

VIA_ACCEPT
Occurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.

VIEW_DEFINITION_MUTEX
Occurs during synchronization on access to cached view definitions.

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 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.

 

Information About Waits Stats in SQL Server SOS or SQL Server Scheduler 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 SOS related waits or SQL Server Scheduler related waits.

SOS_CALLBACK_REMOVAL
Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.

SOS_DISPATCHER_MUTEX
Occurs during internal synchronization of the dispatcher pool. This includes when the pool is being adjusted.

SOS_LOCALALLOCATORLIST
Occurs during internal synchronization in the SQL Server memory manager.

SOS_MEMORY_USAGE_ADJUSTMENT
Occurs when memory usage is being adjusted among pools.

SOS_OBJECT_STORE_DESTROY_MUTEX
Occurs during internal synchronization in memory pools when destroying objects from the pool.

SOS_PHYS_PAGE_CACHE
Accounts for the time a thread waits to acquire the mutex it must acquire before it allocates physical pages or before it returns those pages to the operating system. Waits on this type only appear if the instance of SQL Server uses AWE memory.

SOS_PROCESS_AFFINITY_MUTEX
Occurs during synchronizing of access to process affinity settings.

SOS_RESERVEDMEMBLOCKLIST
Occurs during internal synchronization in the SQL Server memory manager.

SOS_SCHEDULER_YIELD
Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

SOS_SMALL_PAGE_ALLOC
Occurs during the allocation and freeing of memory that is managed by some memory objects.

SOS_STACKSTORE_INIT_MUTEX
Occurs during synchronization of internal store initialization.

SOS_SYNC_TASK_ENQUEUE_EVENT
Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.

SOS_VIRTUALMEMORY_LOW
Occurs when a memory allocation waits for a resource manager to free up virtual memory.

SOSHOST_EVENT
Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.

SOSHOST_INTERNAL
Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.

SOSHOST_MUTEX
Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.

SOSHOST_RWLOCK
Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.

SOSHOST_SEMAPHORE
Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.

SOSHOST_SLEEP
Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.

SOSHOST_TRACELOCK
Occurs during synchronization of access to trace streams.

SOSHOST_WAITFORDONE
Occurs when a hosted component, such as CLR, waits for a task to complete.

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 Security, SLEEP and Networking 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 Security related waits, SQL Server SLEEP related waits, SQL Server Networking related waits and SQL Server HTTP waits.

SEC_DROP_TEMP_KEY
Occurs after a failed attempt to drop a temporary security key before a retry attempt.

SECURITY_MUTEX
Occurs when there is a wait for mutexes that control access to the global list of Extensible Key Management (EKM) cryptographic providers and the session-scoped list of EKM sessions.

SEQUENTIAL_GUID
Occurs while a new sequential GUID is being obtained.

SERVER_IDLE_CHECK
Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.

SHUTDOWN
Occurs while a shutdown statement waits for active connections to exit.

SLEEP_BPOOL_FLUSH
Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.

SLEEP_DBSTARTUP
Occurs during database startup while waiting for all databases to recover.

SLEEP_DCOMSTARTUP
Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.

SLEEP_MSDBSTARTUP
Occurs when SQL Trace waits for the msdb database to complete startup.

SLEEP_SYSTEMTASK
Occurs during the start of a background task while waiting for tempdb to complete startup.

SLEEP_TASK
Occurs when a task sleeps while waiting for a generic event to occur.

SLEEP_TEMPDBSTARTUP
Occurs while a task waits for tempdb to complete startup.

SNI_CRITICAL_SECTION
Occurs during internal synchronization within SQL Server networking components.

SNI_HTTP_WAITFOR_0_DISCON
Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.

SNI_LISTENER_ACCESS
Occurs while waiting for non-uniform memory access (NUMA) nodes to update state change. Access to state change is serialized.

SNI_TASK_COMPLETION
Occurs when there is a wait for all tasks to finish during a NUMA node state change.

SOAP_READ
Occurs while waiting for an HTTP network read to complete.

SOAP_WRITE
Occurs while waiting for an HTTP network write to complete.

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 Replication and Resource 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 Replication related waits, SQL Server Resource related waits.

RECOVER_CHANGEDB
Occurs during synchronization of database status in warm standby database.

REPL_CACHE_ACCESS
Occurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.

REPL_SCHEMA_ACCESS
Occurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence.
This wait type is also used by log reader agent to synchronize memory access. If a publisher has large number of very active published databases and log reader agents, contention can be seen on this wait type.

REPLICA_WRITES
Occurs while a task waits for completion of page writes to database snapshots or DBCC replicas.

REQUEST_DISPENSER_PAUSE
Occurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.

REQUEST_FOR_DEADLOCK_SEARCH
Occurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.

RESMGR_THROTTLED
Occurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.

RESOURCE_QUEUE
Occurs during synchronization of various internal resource queues.

RESOURCE_SEMAPHORE
Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

RESOURCE_SEMAPHORE_MUTEX
Occurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.

RESOURCE_SEMAPHORE_QUERY_COMPILE
Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

RESOURCE_SEMAPHORE_SMALL_QUERY
Occurs when memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries.

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 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.

 
1 2 3