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
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings