Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used

Question

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
...

Answer 1

Aside from what was mentioned in the comments / chat discussion regarding index tuning and query tuning based on what your execution plan says, alternatively you may be able to leverage the Query Store's Regressed Queries Feature to force the execution plan you prefer that uses your nonclustered index.

The one caveat is the plan you want to force has to have been cached by the Query Store already:

To force a plan, select a query and plan, then select Force Plan. You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

This would definitely be a workaround solution, but the Query Store is a very helpful feature for monitoring and troubleshooting performance issues with queries.