I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent" means to forbid/avoid selection of the UCL entirely - independent of any statistics SQL server may have.
Important update/preamble: This SomeStatusVIEW "table" is a view with a
UNION ALL of several tables***, sharing the same columns, cluster definition, and secondary index definition. However, during the plan view expansion / constant folding all but one of the tables is elided from the QP due to the constant filter on TableId.
The table is clustered (UCL_UclKey) and there is an index (IX_IndexKey). From domain knowledge, it is known that the result of this query is "at most a few thousand rows", out of billions. There is no good domain distribution on IndexKey (consider it a SHA value).
select l.UclKey, m.IndexKey, max(l.NotInIndex) -- m contains very few rows with a very small selectivity over IndexKey from #SomeMapping m inner loop join SomeStatusVIEW l with (forceseek) on l.IndexKey = m.IndexKey where 1=1 and l.TableId = 1 -- **this following predicate (as a literal) spans the range of UclKey almost exactly -- even though it has ALMOST NO BEARING on the domain-knowledge expected results -- this is the likely culprit, which will be verified to-morrow on production and l.UclKey between '00000000-0000-E000-7000-23EC00000000' and '00000000-0000-E000-7000-2DEA7FFFFFFF' group by l.UclKey, m.IndexKey option (force order)
SQL Server can use both the clustered index and secondary (IX_IndexKey) to answer this query and fulfill the forceseek hint and nested loop. On most environments it use the IX_IndexKey which is a very efficient query for the domain data. However, on the environment with the most data (by several factors), it incorrectly selects the UCL_UclKey which results in a very sub-par query.
How can I ensure that SQL Server always uses IX_IndexKey in the "most flexible" manner?
I could add
INDEX(2), as this is the only secondary index and
INDEX(1) is the UCL. However, can this be done in a fashion that does not make this index order assumption? In addition, SomeStatusVIEW joins several different tables (all with the good index as the only non-UCL index, yet all with a different index name) so I cannot use a named index as a query hint.
Ideally, I would imagine something like
with (forceseek, avoid index(1)). However, this desired table hint does not exist.
**The UclKey range filter in the query is because the table (and IX_IndexKey) are partitioned over UclKey. This shown value selects about 7 of 30 partitions, and the original inclusion was to hopefully allow SQL Server to prune the partitions directly in the QP instead of during execution. This was/is possibly a premature 'optimization'.
***The "table" allows for eliding all non-used base tables using this structure, such that only one of the base tables ever appears in the query plan.
select 1 as TableId, * from table1 union all select 2 as TableId, * from table2 ...