How to correct row estimates due to join of 2 well-estimated results

Question

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.

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?

Spill detail:

Answer 1

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?