MSQL_DQ Wait Type Explained

MSQL_DQ waits trigger when the Query Processor must synchronize distributed query execution across multiple data sources. The SQL Server scheduler places worker threads in this wait state while the Distributed Query component communicates with OLE DB providers for linked servers, OPENROWSET operations, or OPENDATASOURCE calls.

The wait specifically activates during these scenarios: remote query execution through linked servers, bulk operations against external data sources, and MARS deadlock detection when multiple active result sets compete for distributed resources. The Query Processor uses this wait type to prevent resource conflicts when the same connection attempts concurrent distributed operations.

SQL Server 2016 introduced enhanced MARS deadlock detection that made MSQL_DQ waits more sensitive to connection-level conflicts. SQL Server 2019 improved distributed query performance with PolyBase integration, reducing some MSQL_DQ wait occurrences for specific external data scenarios. SQL Server 2022 added better timeout handling for distributed queries, making these waits more predictable in duration.

The underlying mechanism involves the OLE DB resource pool manager coordinating with remote providers. When network latency exceeds expected thresholds or remote servers experience contention, worker threads accumulate in MSQL_DQ state until the distributed operation completes or times out.

-- Current MSQL_DQ waits with session details
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.command,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    t.text AS current_sql
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'MSQL_DQ'
ORDER BY r.wait_time DESC;

-- Historical MSQL_DQ wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'MSQL_DQ'
    AND waiting_tasks_count > 0;

-- Active distributed queries and their blocking chains
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    DB_NAME(r.database_id) AS database_name,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'MSQL_DQ'
   OR t.text LIKE '%OPENROWSET%'
   OR t.text LIKE '%OPENDATASOURCE%'
   OR r.command LIKE '%DISTRIBUTED%';

-- Linked server configuration
SELECT 
    s.name AS server_name,
    s.product,
    s.provider,
    s.data_source,
    s.is_linked,
    s.connect_timeout,
    s.query_timeout
FROM sys.servers s
WHERE s.is_linked = 1
ORDER BY s.name;

-- Sessions with MSQL_DQ waits and their connection details
SELECT 
    s.session_id,
    s.login_name,
    c.connect_time,
    c.last_read,
    c.last_write,
    c.net_transport,
    c.protocol_type,
    r.wait_type,
    r.wait_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
WHERE r.wait_type = 'MSQL_DQ';

Terminate long-running distributed queries

-- Kill sessions with excessive MSQL_DQ waits (over 5 minutes)
-- WARNING: Test in development first, verify queries are safe to kill
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'KILL ' + CAST(r.session_id AS VARCHAR(10)) + ';' + CHAR(13)
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.wait_type = 'MSQL_DQ'
    AND r.wait_time > 300000  -- 5 minutes
    AND s.is_user_process = 1;

-- Review generated kill commands before executing
PRINT @sql;
-- EXEC sp_executesql @sql;  -- Uncomment after review

Configure linked server timeout settings

-- Set reasonable timeout values for linked servers
-- Adjust timeout values based on network conditions
DECLARE @server_name SYSNAME = 'YOUR_LINKED_SERVER_NAME';

EXEC sp_serveroption 
    @server = @server_name,
    @optname = 'query timeout',
    @optvalue = '300';  -- 5 minutes

EXEC sp_serveroption 
    @server = @server_name,
    @optname = 'connect timeout',
    @optvalue = '30';   -- 30 seconds

-- Verify settings
SELECT name, query_timeout, connect_timeout 
FROM sys.servers 
WHERE name = @server_name;

Enable distributed query optimization

-- Configure instance-level settings for better distributed query performance
-- These changes require sysadmin privileges
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Set remote query timeout (0 = no timeout, use with caution)
EXEC sp_configure 'remote query timeout', 600;  -- 10 minutes
RECONFIGURE;

-- Enable distributed transactions if needed
EXEC sp_configure 'remote proc trans', 1;
RECONFIGURE;

-- Verify configuration
SELECT name, value, value_in_use 
FROM sys.configurations 
WHERE name IN ('remote query timeout', 'remote proc trans');

Configure appropriate timeout values for all linked servers based on expected query duration and network latency. Set query timeout to 300-600 seconds for most scenarios, connect timeout to 15-30 seconds. Monitor these values during peak usage periods.

Implement connection pooling for applications using distributed queries. Avoid MARS when possible with distributed queries since it increases deadlock potential. Use connection strings that explicitly disable MARS (MultipleActiveResultSets=false) for distributed query connections.

Establish monitoring for MSQL_DQ wait accumulation using automated alerts when average wait times exceed baseline thresholds. Create custom performance counters tracking distributed query duration and frequency. Log slow distributed queries for pattern analysis.

Design distributed queries with explicit timeouts in application code. Use asynchronous patterns where possible to prevent connection blocking. Cache frequently accessed remote data locally when feasible to reduce distributed query dependency.

Configure network quality of service (QoS) policies prioritizing SQL Server traffic between linked servers. Implement network monitoring to detect latency spikes affecting distributed query performance. Consider geographic proximity when designing distributed database architectures.