EXECSYNC Wait Type Explained

EXECSYNC waits manifest when SQL Server's query processor coordinates parallel worker threads during non-exchange operations. The scheduler must synchronize threads when they encounter shared resources that cannot be processed independently, such as large binary objects exceeding 8KB, bitmap filters in hash joins, or temporary spool structures.

During parallel execution, the query processor creates multiple worker threads that typically operate independently on different data partitions. However, certain operations require coordination points where threads must wait for each other. The EXECSYNC wait specifically tracks synchronization outside the standard exchange iterator mechanism used for data redistribution between parallel operators.

LOB processing triggers EXECSYNC waits because SQL Server cannot efficiently partition LOB data across threads. When a parallel scan encounters VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX) columns, worker threads must coordinate access to the LOB pages through the buffer pool manager. This coordination happens through internal synchronization primitives that generate EXECSYNC waits.

Bitmap filters in hash joins also generate EXECSYNC waits when multiple threads build or probe the same bitmap structure. The memory manager must ensure thread-safe access to the shared bitmap, creating coordination points.

SQL Server 2019's intelligent query processing reduced EXECSYNC waits through improved parallel operator implementations. SQL Server 2022 enhanced LOB handling in parallel plans, reducing synchronization overhead in workloads with large text columns.

-- Current EXECSYNC waits with session details
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_text,
    r.cpu_time,
    r.logical_reads,
    r.granted_query_memory * 8 AS granted_query_memory_kb
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'EXECSYNC'
ORDER BY r.wait_time DESC;

-- Historical EXECSYNC wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'EXECSYNC';

-- Queries with high parallelism causing EXECSYNC
SELECT TOP 10
    qs.query_hash,
    qs.execution_count,
    qs.total_worker_time,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.total_worker_time > qs.total_elapsed_time -- Indicator of parallel execution
ORDER BY qs.total_worker_time DESC;

-- Tables with LOB columns that may cause EXECSYNC waits
SELECT 
    t.name AS table_name,
    c.name AS column_name,
    c.system_type_id,
    ty.name AS data_type,
    c.max_length
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE ty.name IN ('varchar', 'nvarchar', 'varbinary', 'text', 'ntext', 'image')
    AND (c.max_length = -1 OR ty.name IN ('text', 'ntext', 'image'))
ORDER BY t.name, c.name;

-- Current parallel plans with high worker thread usage
SELECT 
    r.session_id,
    r.dop,
    r.parallel_worker_count,
    r.granted_query_memory * 8 AS granted_query_memory_kb,
    p.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.dop > 1
    AND r.parallel_worker_count > 0;

Reduce MAXDOP for LOB-heavy queries This script identifies and forces lower parallelism for queries frequently accessing LOB columns to minimize EXECSYNC coordination overhead.

-- Create plan guide to limit MAXDOP for specific LOB query
-- Test thoroughly in development first
EXEC sp_create_plan_guide 
    @name = N'Force_MAXDOP_2_LOB_Query',
    @stmt = N'SELECT * FROM LargeDocumentTable WHERE DocumentContent LIKE @SearchTerm',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION(MAXDOP 2)';

-- Verify plan guide creation
SELECT name, scope_type_desc, is_disabled 
FROM sys.plan_guides 
WHERE name = N'Force_MAXDOP_2_LOB_Query';

Adjust cost threshold for parallelism Increases the cost threshold to reduce unnecessary parallelism for queries that generate high EXECSYNC waits.

-- Increase cost threshold for parallelism
-- Current default is 5, consider 25-50 for OLTP workloads
-- Monitor performance impact closely
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism', 25;
RECONFIGURE;

-- Verify change
SELECT name, value, value_in_use 
FROM sys.configurations 
WHERE name = 'cost threshold for parallelism';

Create filtered statistics on LOB columns Improves cardinality estimation to help query optimizer make better parallelism decisions for LOB-containing queries.

-- Create filtered statistics for non-null LOB values
-- Replace with actual table and column names
CREATE STATISTICS STAT_DocumentContent_NonNull 
ON DocumentTable (DocumentContent) 
WHERE DocumentContent IS NOT NULL
WITH SAMPLE 20 PERCENT;

-- Update statistics to reflect current data distribution
UPDATE STATISTICS DocumentTable STAT_DocumentContent_NonNull;

Enable query store and force serial plans Forces problematic parallel plans to execute serially when EXECSYNC waits consistently exceed acceptable thresholds.

-- Enable Query Store if not already enabled
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

-- Force serial execution for specific query
-- First, identify the query_id from sys.query_store_query
EXEC sp_query_store_force_plan 
    @query_id = 12345, -- Replace with actual query_id
    @plan_id = 67890;  -- Replace with serial plan_id

-- Monitor forced plan performance
SELECT 
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    rs.avg_duration,
    rs.avg_cpu_time
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.is_forced_plan = 1;

Configure cost threshold for parallelism between 25-50 for OLTP workloads to prevent excessive parallelization of queries with moderate complexity. Set MAXDOP to the number of physical cores per NUMA node, typically 4-8 in most production environments.

Implement columnstore indexes for analytical queries on tables containing LOB data. Columnstore compression reduces I/O and eliminates most EXECSYNC waits by enabling efficient parallel processing without LOB coordination overhead.

Monitor queries accessing LOB columns through extended events or Query Store. Create plan guides with MAXDOP hints for consistently problematic queries that show high EXECSYNC wait times exceeding 1000ms.

Partition large tables containing LOB columns by date or other logical boundaries. This reduces the data volume each parallel worker processes and minimizes synchronization points during execution.

Enable intelligent query processing features in SQL Server 2019+ including adaptive joins and interleaved execution. These features automatically optimize parallel execution patterns and reduce unnecessary EXECSYNC waits.

Avoid storing large binary data directly in SQL Server tables when possible. Consider file system storage with FILESTREAM or external blob storage solutions for documents exceeding 1MB to eliminate LOB-related EXECSYNC waits entirely.