3

I've done my best to follow Why is my query suddenly slower than it was yesterday?

The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I have pasted text files to my github instead below:

Here is the DDL and TSQL I am executing.

Here is the bad query plan (times out). PasteThePlan

Here is the good query plan. PasteThePlan

Any idea how I can more reliably get the engine to use a "good" query plan? Thank you!

Charlieface
  • 12,780
  • 13
  • 35
Vince
  • 175
  • 3
  • Apologies that when I first posted I was missing this DDL: CREATE INDEX IX_#tmp_rollup_FallbackResolution ON #tmp_rollup(PartMd5, CartonMd5, FallbackGuideValue) INCLUDE ( DriverRootId, Id ); – Vince Jul 25 '22 at 17:18
  • It seems like the main difference is that the Hash Match on Node ID 10 is "flipped" (i.e. the Build vs. Probe) if that info helps. – Vince Jul 25 '22 at 21:05
  • 1
    PasteThePlan doesn't like SQL Server 2019 plans, need to change the Build attribute – Charlieface Jul 25 '22 at 23:54
  • Thank you @Charlieface for getting pastetheplan working for it (for me it wouldn't validate the xml and Brent said it's because it's Azure SQL) – Vince Jul 25 '22 at 23:55
  • Good/bad plans flipping are almost always actually a problem of not-very-good/bad plans. There is substantial improvements you can make: Most of those tables should be indexed on (PartId, RootId) allowing them to do a merge join, and perhaps should just be EXISTS or IN rather than regular joins. You can reorganize the query and move the join on #tmp_rollup out of the CTE, thereby only referencing it once, this should remove the table spool. – Charlieface Jul 26 '22 at 00:04
  • The tables do have an index on (PartId, RootId) per the DDL. I'm intrigued about the EXISTS or IN comment (as well as the theory behind it). Thanks... – Vince Jul 26 '22 at 00:30
  • Re the spool, interesting... I thought by having #tmp_rollup inside the CTE it would make for a simpler query plan since the join is on its ID column instead of having joins span across the CTE boundary. – Vince Jul 26 '22 at 00:39
  • 1
    Not necessarily: if you place it outside then the compiler can still see that the CTE is a single group per row of the outer #tmp_rollup and therefore there are many optimizations available anyway, querying the table twice is not going to help it. And since the temp table is only referenced once (the reference being modified) it won't need a spool – Charlieface Jul 26 '22 at 00:46

0 Answers0