2

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 <> '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
                                                            )
                                ) > 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.

enter image description here

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

enter image description here

(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 "stuck" 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 "stuck" on 4 rows of 180,215?

Is there a way to fix this without changing the code?

4
  • Sounds a lot like intra-query parallel deadlock. Do you see any deadlocks for this query in system_health extended event?
    – rois
    Mar 5 at 12:31
  • 1
    Forcing the optimizers hand is not recommended as your FIRST response to a bad query situation. But if you cannot change the code... (and why not?) then that may be your only option. Just make sure to document what and why, and to keep checking on it to make sure it's still performing adequately. It's not inherently evil, just needs per-query justification rather than being a blanket solution. Mar 5 at 13:36
  • @JonathanFite its vendor code so is baked into an app.
    – SEarle1986
    Mar 5 at 16:33
  • @rois that's an interesting thought, I'll look into that
    – SEarle1986
    Mar 5 at 16:34
5

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:

enter image description here

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.

1
  • Thankyou,This has fixed the issue, the plan still goes paralell but the eager spools have been eliminated. This is a better option that the plan guide I originally thought of as it still gives the optimizer freedom to optimize.
    – SEarle1986
    Mar 8 at 12:11
2

Explanations

the operator highlighted in green (Clustered Index scan on TABLE2) shows 100% but it's execution time continues clocking up.

The percentages there are based on row estimates. It will show 100% once it reaches the estimated numbers of rows expected, but the operator continues running until all of the actual required rows have been read.

Your suspicion that thread skew is at play here is likely correct. An eager index spool causes anything "below" it to run on a single thread (you can read about this in Paul White's article The Eager Index Spool and The Optimizer). So the two clustered index scans are really running serially.

With thread skew established, you mentioned this:

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.

FYI - the "Parallelism (Gather Streams)" operator is the only parallelism operator in the plan, so it is the one generating all of the CXCONSUMER waits - specifically the single thread on the left side of that operator is waiting for a full packet of rows from the right side.

It's possible that the thread skew, and the spools, are just causing it to take a long time for their to be a full packet to send across the exchange.

Solutions

Since you can't change the query, adding a MAXDOP hint via a plan guide is a valid solution. This will get you the working plan that finishes in an acceptable amount of time.

Another option is to try to eliminate the spool, which would probably prevent the skew problem in the first place (and let the query really run in parallel). A spool usually indicates that you are missing an index (as Erik Darling mentions in his post Eager Index Spool Suggestions). Look at the details of the two spools and you should see how they are keyed, and what columns they include. Then build your new index(es) based on that, and see if they help.

0

It sounds to me like you have run into a problem with the Cardinality Estimation changes for SQL Server 2016 and above. This is a common problem, but luckily it is easy to fix or workaround until you can implement a fix.

In the article that I have referenced above, there are steps you can use to assess whether any of your most important queries perform less well under the latest CE. I'd recommend working through them to determine if you need to make changes. The easiest one would be to add the LEGACY_CARDINALITY_ESTIMATION query hint to force the optimiser to use the older CE. I've done this many times to dig clients out of the same situation. The thing you should be glad of though is that you tried this in test first, you would be surprised how many people upgraded production only to find the same issue you are having.

I'd recommend not dropping the compatibility level of the database if you can avoid it as you remove some of the other goodies that you get with a newer version by doing so. Additionally, I would not turn on the legacy cardinality estimator for the entire database unless the problems are widespread - target the queries that cause the most pain with the query hint. That being said, if you can't change the code this may be your best option.

You can use Query Store to track how the query performs with the various hints and database options set and then use it to force a plan for a particular query that is causing problems.

7
  • Thanks for your reply, I wasn't clear on this in my question, but the query ran in database compatibility level 120 when we were on SQL Server 2014 and the performance problem I am now experiencing in SQL Server 2016 happens when using database compatibility level 120, 130 or even 110 (uses legacy optimizer)
    – SEarle1986
    Mar 4 at 22:41
  • Also, were there further changes to CE in 2016 above those in 2014? I couldn't find any reference to this in the article you linked. i thought there were just literally two versions of the optimizer - old and new?
    – SEarle1986
    Mar 4 at 22:47
  • There were definitely changes to the CE in 2016. Mar 4 at 22:49
  • Thanks, I wasn't aware of that, this seems to suggest these changes are only applicable if the DB is in 130?
    – SEarle1986
    Mar 4 at 22:53
  • 1
    interesting, so thats database compatibility 130 with OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) ?
    – SEarle1986
    Mar 4 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.