Intra-Query Parallel Deadlock: When One Query Blocks Itself

An intra-query parallel deadlock is one query whose own threads end up waiting on each other in a circle. No second session is involved. Most of the time you never even get an error. SQL Server quietly breaks the circle, and the query just runs slower than it should.

One query, no second session

A junior DBA once asked me a fair question. “The sales report takes ten seconds on some runs and one second on others. There is no blocking. There is only one session, and all its threads are waiting. Waiting on what?” On each other.

A parallel query splits work across threads. Parallelism operators, also called exchanges, pass rows between them. When the plan needs rows sorted, an exchange must see the next row from every thread before it passes one on. If one thread has nothing ready, the exchange waits. The other threads fill their buffers and wait too. Nobody can move. That is an intra-query parallel deadlock.

Build a query that waits on itself

The demo uses 300 stores with 1,000 sales each. The notes columns are wide on purpose, so the buffers fill quickly. The whole script takes about half a minute.

DROP TABLE IF EXISTS #Sales;
DROP TABLE IF EXISTS #Stores;

CREATE TABLE #Stores (StoreId int NOT NULL PRIMARY KEY,
                      StoreNotes char(400) NOT NULL);
CREATE TABLE #Sales (StoreId int NOT NULL, SaleId int NOT NULL,
                     SaleNotes char(400) NOT NULL,
                     PRIMARY KEY (StoreId, SaleId));

INSERT #Stores WITH (TABLOCK) (StoreId, StoreNotes)
SELECT value, 'store' FROM GENERATE_SERIES(1, 300);

INSERT #Sales WITH (TABLOCK) (StoreId, SaleId, SaleNotes)
SELECT s.value, n.value, 'sale'
FROM GENERATE_SERIES(1, 300) AS s
CROSS JOIN GENERATE_SERIES(1, 1000) AS n;

SELECT COUNT(*) AS SalesRows FROM #Sales;

Watch the deadlock monitor

SQL Server has a background deadlock monitor that wakes up every few seconds and looks for circles of waiting. This event session records each of its passes, plus any deadlock report. The demo creates a server-level event session and removes it at the end. The short wait lets the monitor log one quiet pass first.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'DeadlockMonitorDemo')
    DROP EVENT SESSION DeadlockMonitorDemo ON SERVER;

CREATE EVENT SESSION DeadlockMonitorDemo ON SERVER
    ADD EVENT sqlserver.deadlock_monitor_perf_stats,
    ADD EVENT sqlserver.xml_deadlock_report
    ADD TARGET package0.ring_buffer
    WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);

ALTER EVENT SESSION DeadlockMonitorDemo ON SERVER STATE = START;
WAITFOR DELAY '00:00:06';

Run the report

The report numbers every sale by store. The variables keep SSMS from drawing 300,000 rows. Two hints force a parallel merge join, the plan shape that keeps rows in order between threads. MAXDOP 4 needs at least four cores.

DECLARE @Rows bigint, @Notes char(400);

SELECT @Rows = MAX(x.RowNum), @Notes = MAX(x.SaleNotes)
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY s.StoreId) AS RowNum, s.SaleNotes
    FROM #Sales AS s
    INNER MERGE JOIN #Stores AS st ON st.StoreId = s.StoreId
) AS x
OPTION (MAXDOP 4, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

It finishes without any error. Now ask the monitor what it saw while the report ran.

WAITFOR DELAY '00:00:06';
DECLARE @Events xml =
    (SELECT CAST(t.target_data AS xml)
     FROM sys.dm_xe_sessions AS s
     JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
     WHERE s.name = N'DeadlockMonitorDemo');

SELECT MIN(v.Found) AS PassesWithDeadlockBefore, MAX(v.Found) AS PassesWithDeadlockAfter,
       @Events.value('count(//event[@name="xml_deadlock_report"])', 'int') AS DeadlockReports
FROM @Events.nodes('//event[@name="deadlock_monitor_perf_stats"]') AS n(e)
CROSS APPLY (SELECT n.e.value('(data[@name="count_cycles_with_deadlock"]/value)[1]',
                             'bigint') AS Found) AS v;

SELECT qs.last_dop AS LastDop, qs.last_spills AS LastSpills,
       qs.last_elapsed_time / 1000 AS LastMs
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE N'%MAXDOP 4%' AND t.text LIKE N'%#Stores%'
  AND t.text NOT LIKE N'%dm_exec_query_stats%';

What the monitor saw

The first two columns come from a server-wide running count of monitor passes that found a deadlock. Ignore the numbers and look at the difference. On my quiet test server it went up by several while one report ran. So the monitor found deadlocks again and again, and the only thing running was our query.

DeadlockReports is 0. No deadlock graph, no error 1205, nothing in the application log. That is why these are so hard to spot.

The second result shows the cost. The query ran at DOP 4, took seconds, and LastSpills shows several hundred pages or more. Each time the monitor found the circle, SQL Server broke it by writing buffered rows to tempdb, an exchange spill. Then the threads moved on, until they got stuck again. That waiting is the slowness the junior DBA saw.

A narrow fix for one statement

The same report on a single thread has no exchanges, so there is nothing to wait on. I run it and compare the two versions from the plan cache.

DECLARE @Rows bigint, @Notes char(400);

SELECT @Rows = MAX(x.RowNum), @Notes = MAX(x.SaleNotes)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s.StoreId) AS RowNum, s.SaleNotes
      FROM #Sales AS s INNER MERGE JOIN #Stores AS st ON st.StoreId = s.StoreId) AS x
OPTION (MAXDOP 1);
GO
SELECT CASE WHEN t.text LIKE N'%MAXDOP 1)%' THEN 'MAXDOP 1' ELSE 'MAXDOP 4' END AS Version,
       qs.last_dop AS LastDop, qs.last_spills AS LastSpills,
       qs.last_elapsed_time / 1000 AS LastMs
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE N'%INNER MERGE JOIN #Stores%'
  AND t.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY Version;

The MAXDOP 1 version shows zero spills and finished in under a second on my server. One thread beat four, because the four spent most of their time waiting. A hash join hint is another candidate, so test both.

What to check on your own server

The usual mistake is to drop the server-wide MAXDOP to 1 because one report misbehaved. That punishes every other query. Fix the statement instead.

Look for parallel queries with a high last_spills and no sort or hash warnings. Check their plans for Parallelism operators that keep order, often under a merge join or a stream aggregate. Test a hint on that one statement, compare results and duration, and keep it only if it wins. Clean up the demo when you are done.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'DeadlockMonitorDemo')
    DROP EVENT SESSION DeadlockMonitorDemo ON SERVER;
DROP TABLE IF EXISTS #Sales;
DROP TABLE IF EXISTS #Stores;

When a parallel query is slow for no clear reason, ask whether its threads are waiting on each other.

A deadlock is not always two sessions, it is sometimes one query waiting on itself.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.