Information About Waits Stats in SQL Server synchronization Waits – Part 6

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

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

EE_PMOLOCK
Occurs during synchronization of certain types of memory allocations during statement execution.

EE_SPECPROC_MAP_INIT
Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.

ERROR_REPORTING_MANAGER
Occurs during synchronization of multiple concurrent error log initializations.

EXCHANGE
Occurs during synchronization in the query processor exchange iterator during parallel queries.

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

EXECUTION_PIPE_EVENT_INTERNAL
Occurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.

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

FCB_REPLICA_READ
Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.

FCB_REPLICA_WRITE
Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.

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 *