Slow Parallel SQL Server query, almost instant in serial I have a SQL Server query as follows (obfuscated):
UPDATE  [TABLE1]
SET     [COLUMN1] = CAST('N' AS CHAR(1))
FROM    [TABLE1]
WHERE   (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR 
        (COLUMN1 &lt;&gt; 'N' AND (
                                    SELECT  COUNT(*)
                                    FROM    TABLE2 wle
                                            JOIN TABLE3 wl 
                                                    ON wl.COLUMN3 = wle.COLUMN3
                                    WHERE   TABLE1.COLUMN4 = wle.COLUMN4 AND 
                                            (wl.COLUMN5 = '1' OR wl.COLUMN6 = '1') AND 
                                            wle.COLUMN7 = (
                                                            SELECT  MIN(alias.COLUMN7)
                                                            FROM    TABLE2 AS alias
                                                            WHERE   TABLE1.COLUMN4 = alias.COLUMN4
                                                            )
                                ) &gt; 0
                                            
        )

We have just upgraded our (test) server to SQL Server 2016 SP2 from SQL Server 2014 SP3.
The performance of the query above appears to have fallen off a cliff as a result of this.
When the server was on SQL Server 2014, the database compatibility level was 120. Now it is on SQL Server 2016, the compatibility level for the database is still 120, however I have tried the query in 110,120 and 130, all with the same result.
When I run sp_whoisactive, I can see the wait_info is (48847425ms)CXCONSUMER suggesting that the query has been waiting for CXCONSUMER for the last 48847425ms (814 minutes) The query has currently been running for 13:34:07.587.
The wait info suggests that the query has been waiting on CXCONSUMER for the majority, if not all of its execution time.
This to me suggests some issue with paralellism so I ran the query with the hint OPTION (MAXDOP 1) and it finished in an acceptable time (around 30 seconds)
The plan shape is as follows.

The plan shape for the MAXDOP 1 query is as follows:

(the same plan with no parallelism operators)
When I run the parallel query with the live execution plan enabled, the operator highlighted in green (Clustered Index scan on TABLE2) shows 100% but it's execution time continues clocking up.
The operator highlighted in red (Clustered Index Scan on TABLE1) gets &quot;stuck&quot; on 4 rows of 180,215.
What could be causing this problem? In my head am I thinking it is parallelism skew (uneven workload) but given that the serial query finishes in less than a minute I would have thought even if the query went parallel but only used one thread it would still complete in a time close to the serial query.
Also, given that the live plan appears to show the red clustered index scan not progressing at all I am unsure what is happening.
Processor and I/O affinity are set to to Automatic
I have found this article that describes similar behavior though the purpose of the article appears to be showing that CXCONSUMER is not necessarily a benign wait and doesn't say how / if it can be fixed.
In terms of fixing it, I know the code can be re-written in a more efficient way (both the COUNT and MIN subqueries could be selected into variables) but unfortunately changing the query isn't an option I have.
I could force a MAXDOP hint but again, this means changing the code, perhaps I could force it with a plan guide, though such practice of forcing the opimizer usually isn't advisable?
What causes this problem?
Why is the query running slow in SQL Server 2016?
Why is the Clustered Index Scan on TABLE1 getting &quot;stuck&quot; on 4 rows of 180,215?
Is there a way to fix this without changing the code?
Is there a way to fix this without changing the code?

Create the following index to eliminate the eager index spools:
-- Give this index a better name
CREATE INDEX i ON dbo.TABLE2 (COLUMN4, COLUMN7) INCLUDE (COLUMN3);

With that in place, you should get a plan similar to:

The other problems described in the question will almost certainly disappear once you give the optimizer good indexes.
It is possible (though unlikely) your query suffers from an undetected intra-query parallelism deadlock (IQPD). A very detailed investigation would be needed to confirm that. The vast majority of IQPDs are detected and can then be resolved by spilling exchange buffers to tempdb. An undetected IQPD will result in the query being stuck forever.