We have a long running query and using PA from Quest and the wait type is EXECSYNC. I read the documentation, but I was wondering if I could get a better explanation.
EXECSYNC is definitely not a wait event that is widely discussed in tuning articles. Have you heard people ask whether parallel execution is helping or hurting their application’s execution? When you’re weighing the pro’s and con’s of parallelism in your application environment you need to determine whether your system is CPU-bound (and PA is a GREAT tool for that). If it is, parallelism could actually slow performance and could even lead to resource bottleneck related contention.
The amount of time your SQL Server is spending waiting for EXECSYNC operators (and the fact that you might see this event during one execution of a statement, then not see it during the next) all depends on how it’s able to handle parallel coordination of non-standard data. Basically, you’ll see this wait showing up during parallel queries when SQL Server’s query processor is synchronizing areas not related to the exchange iterator (the exchange iterator is the mechanism that actually handles the partitioning and movement of data between threads during parallel execution). Examples of areas whose synchronization will lead to EXECSYNC waits are are bitmaps, large binary objects (LOBs), and the spool iterator; LOBs frequently use this wait state.
Craig Freedman has a good explanation of parallel queries on his blog that’s worth a read: http://blogs.msdn.com/craigfr/archive/2006/10/11/introduction-to-parallel-query-execution.aspx
