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

 

Blocking Due to SPID in Sleeping State in SQL Server

SQLServerF1

Blocking is a common issues noticed in any of any relational database management system (RDBMS) which uses lock-based concurrency. In SQL Server, blocking generally occurs when one SPID holds a lock on a specific resource and a another SPID attempts to acquire a conflicting lock type on the same resource. Generally, the time frame which the first SPID holds locks on the resource is very small, so when it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and happens many a times throughout with no noticeable effect on system performance.

It is common for DBAs to notice blocking on various servers and typically the locks held for a short time does not cause any issues, nor is identified by any monitoring tool. Sometimes, blocking can get severe and cause performance issues from timeouts to slowness, etc. DBAs generally deal with blocking issues by checking on the head or lead blocker causing the blocking and try to understand why the head blocker is taking long time to complete and it is not very important, then DBAs kill the SPID. If the blocking issue is persistent then DBAs identify the culprit and tune it by adding indexes or rewriting the query.

However, sometimes there can be strange blocking issues, one of such issue is blocking caused by a SPID which is in sleeping state. In general, a SPID which finishes its task will leave any locks held, so the sleeping sessions are not expected to cause any blocking issues, however in some environments, persistent blocking issues are noticed due to SPID which is in sleeping state still holds the locks and does not release it until the connection is closed from the application of till the SPID is killed by the DBAs. If we try to understand why the blocking occurs with a session in sleeping state, there could be different reasons and some of the are mentioned below.

– Mostly this issue happens when someone uses SQL Server Management Studio(SSMS) query analyzer window, runs some bad queries and leave the session open. The queries which were run either have a explicit open transaction or an implicit transaction which holds locks and does not release the locks because no commit or rollback was issued. We need to identify such sessions, the user who is running queries from that session, what queries are run, what locks are held, from which application are the session from, hostname, etc and then DBAs can go ahead and kill the sessions. Then DBAs need to contact the respective user or management and report the issue to avoid happening in future.

– This can also happen when an application opens a connection with SQL Server, runs some query and then due to some issues, the application connection is lost either due to application crash or hang or some application problems. No signal is sent to SQL Server due to sudden crash, so SQL Server still assumes that the application may still send additional queries and keeps the session available. This is due to bad application design and needs to fix the application code.

– This can also happen if application has bad code, which opens transactions and fails to commit or rollback in case of query failures or other issues.

– Applications also uses session pooling, where is sessions are reused when required. Periodically applications need to close sessions which have not been reused after sometime.

– We can also suggest developer to use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client. It is important to note that T-SQL statements following the statement which caused the error will not be executed.

– Some older or incompatible client components may cause these kind of issues too, so need to keep them updated and use latest and compatible connectivity components.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
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