I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I used to think were bad practice but since learned they have their place (mostly the ForceSeek and ForceScan hints).
I've finally ran into a query where no matter what I do, I can't seem to get it to use the better index naturally (which is the primary key clustered index).
The query is pretty simple:
SELECT A.Field1, B.Field2 FROM TableA AS A WITH (FORCESEEK) INNER JOIN TableB AS B WITH (FORCESEEK) ON A.PrimaryKeyClusteredIndexedField = B.PrimaryKeyClusteredIndexedField WHERE B.NonClusteredIndexedField = SomeBooleanValue -- SomeBooleanValue = 1 (True)
I run this query across many databases (about 500 - this is just how our schema is designed) and in the relatively smaller databases it does default to using the Primary Key Clustered Index in the query plan. In a couple one-off much larger databases it instead tries to do an Index Seek on the Nonclustered Index field "B.NonClusteredIndexedField" which also then requires a Key Lookup.
Not sure why it would pick the Nonclustered Index over the Primary Key Clustered Index in this case, but the Key Lookup then severely impacts the performance of the query. Using an Index Hint on the TableB so that the code becomes
INNER JOIN TableB AS B WITH (FORCESEEK, INDEX(IX_PrimaryKeyClusteredIndex)) fixes this problem but feels dirty to me. Is it ok to use in one-off cases like this, especially when it's an Index Hint to the Primary Key Clustered Index which is unlikely to change?