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?
union allstyle query and forcing a merge concatenation to keep the two halves in the right order
IX_ValueArray_PropValObj_IncSeqindex so that its key columns are
(PropertyId, Value, ObjectId)then you can do a
left merge joinlike the others