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: