3

The following query runs across ~60 databases in parallel. Without hints there are numerous spills and non-optimal plans in at least 10% of the DBs.

Using a larger DB as a guide, the query was locked down with hints (~75ms on 1 CPU) to the reduce variance in runtimes since 1 bad plan causes kills overall runtime. We are mostly opposed to letting each DB adjust its plan freely as some DB will likely catch fire in the long run on the production platform. We are perfectly happy with a nearly-optimal plan for larger DBs that may be sub-optimal for smaller DBs.

A few (~5) of the smaller databases still exhibit small Level 1 spills (see plan) even after adding statistics w/full scan. Runtime is still ok (125ms) but would like to eliminate the spill.

This is Sql Server 2019. Should the adaptive grant feature (2017) be adjusting the grant due to the spill? Running it repeatedly in SSMS and viewing plan seems to indicate no change.

select top (@pMax)
           aig.ObjectId,  
           iif((@pA in (1, 2, 3, 4, 5, 6, 9, 11, 12) and ttm.ObjectId is not null) or
               (@pA in (7, 8, 10, 13, 14, 15)), 1.0, 0.0) as Rank
      from oav.value aig               
      inner merge join Pub.CachedObjectHierarchyAttributes coha
        on coha.ObjectId = aig.ObjectId
       and coha.IsActiveForPublisher = 1
       and coha.IsToolItem = 1
      inner merge join Oav.ValueArray v897
        on v897.PropertyId = 897
       and v897.ObjectId = aig.ObjectId
       and v897.[Value] = @pBrandId
      left hash join oav.valuearray ttm      
        on ttm.ObjectId = aig.ObjectId
       and ttm.PropertyId = 11131  
       and ttm.[Value] = @pToolTypeMapId 
     where aig.PropertyId = 2573        
       and aig.[Value] = @pA
     order by ttm.[Value] desc -- to put TTM matches at the top
     option (maxdop 1); -- limit to 1 cpu since it runs across all pubs

The row estimates from the 3 index seeks at the right match within <1% of the actual rows.

enter image description here

However the estimate for the first merge of the 2 rightmost seeks is off quite a bit and then carries through causing the spills. With perfect estimates from the 2 previous stages, what remains to affect that estimate?

enter image description here

Spill detail:

enter image description here

5
  • Please share the full plan via brentozar.com/pastetheplan Oct 31, 2021 at 10:58
  • Might be worth getting rid of the sort entirely by using some kind of union all style query and forcing a merge concatenation to keep the two halves in the right order Oct 31, 2021 at 11:08
  • @Charlieface - Org: brentozar.com/pastetheplan/?id=SJHbs2aUF With MGP=.2: brentozar.com/pastetheplan/?id=HJ8_o26UY
    – crokusek
    Nov 1, 2021 at 19:09
  • Can you change the IX_ValueArray_PropValObj_IncSeq index so that its key columns are (PropertyId, Value, ObjectId) then you can do a left merge join like the others Nov 1, 2021 at 20:08
  • @Charlieface, that IX already started with those ordered 3 (plus Sequence). Merge did eliminate spill on that stage. Small spill still on sort stage but exec time is better. Also checked large DB runtime was unaffected so will keep this change. Done with this issue but headline of question not really answered in general for next time. brentozar.com/pastetheplan/?id=Hy_GkB1PY
    – crokusek
    Nov 2, 2021 at 22:47

1 Answer 1

5

If you're already at the point where you're adding a lot of hints to the query and don't want to give SQL Server a choice in the situation, then I'd be inclined to add a MIN_GRANT_PERCENT hint to eliminate the spill. The query plan only has two memory consuming operations so that type of hint is likely to be effective here.

The current memory grant looks to be pretty small - maybe 3 MB? Making it 30 MB instead isn't likely to cause an issue, right? Tracking down and resolving cardinality estimate issues like this in some cases can take hours. It might even take hours for you to gather and anonymize all of the information needed for someone to attempt to answer your stated question. Is it really worth the time to do that?

5
  • @AaronBertrand I've seen a query run in the cloud that took 30 seconds to spill a very amount of small data to tempdb. If adding another hint gives OP peace of mind then I'm all for it. I definitely wouldn't do a big investigation into cardinality estimates to make the 125 ms query faster.
    – Joe Obbish
    Oct 30, 2021 at 2:19
  • 1
    Upvoted especially for your final comments. At one point in my career I felt like performance problems due to cardinality estimate issues were one of the easiest things to solve (mental checklist: is it a bad predicate? If so, which of the 7 things that make predicates bad could it be? If not, is it a statistics problem? etc). I literally made a flowchart to help debug cardinality estimate issues. In my recent career, I'm finding that there are just some cardinality estimate issues that are so hard to debug, and don't fit the norm that I've previously been accustomed to, it drives me bonkers.
    – J.D.
    Oct 30, 2021 at 2:57
  • MIN_GRANT_PERCENT seems great until you realize that it is based on the total memory granted to the instance which varies by platform/hardware and makes it difficult to maintain and easy to set to the high side. Why on earth wouldn't they offer a hint that is relative to the estimate so I can simply "boost" the estimate by 200%. And can anyone offer any comment on why the adaptive 2017 engine doesn't adjust for cases like this? Tested the hint at MGP=0.2 and it runs in 40ms using 8MB. This helps a lot since its x60 dbs. Can't lock too low an MGP because the bigger DBs may exceed it.
    – crokusek
    Oct 30, 2021 at 6:48
  • @AaronBertrand Well that's reassuring 🙃. If you have any resources you could recommend on keeping up with the optimizer changes, especially with tackling cardinality estimation issues that are outside the normal pattern, I'm all ears.
    – J.D.
    Oct 30, 2021 at 12:24
  • 1
    @crokusek Regarding this part: "Can't lock too low an MGP because the bigger DBs may exceed it.", the hint is a minimum, not a maximum. The query optimizer can choose to grant more memory than what you specify as the hint. Adding that hint will never cause a query to start spilling to tempdb that wasn't before. As long as 0.2% doesn't cause any concurrency issues with memory grant waits then there should be no problem for your scenario.
    – Joe Obbish
    Oct 30, 2021 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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