0

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?

6
  • Do you really need a Non Clustered index on a boolean column? It's going to be either True or False and I've seen horrendous performance from queries that try to use these indexes. – MJH Mar 13 '20 at 14:33
  • Do you use "fullscan" for your statistics ? Usually, SQL is pretty good at getting the best plan when he does have the appropriate information (stats) – Dominique Boucher Mar 13 '20 at 15:04
  • Sounds like the smaller databases are using the clustered index due to the "tipping point" phenomenon. However in the larger databases, the index is probably filtering enough rows to make the nonclustered index useful. – Queue Mann Mar 13 '20 at 15:24
  • @MJH I didn't design the schema so not sure if we truly need the nonclustered index on the boolean field or not (more likely probably not - at least for the field just by itself) but why would that cause terrible performance (just curious)? – J.D. Mar 15 '20 at 20:23
  • I've seen plans in the past that have used an index seek with a CI lookup where the index is on a Y/N column. Imagine that on a table with millions of rows, a CI scan would be more efficient (at least it was in the scenario I saw). – MJH Mar 15 '20 at 21:09
3

It's OK to do this only when a better solution doesn't exist.

As David Browne - Microsoft commented:

If you were writing in another language you wouldn't think twice about telling the computer not just what to do by how to do it. That's all an optimizer hint is.

0
2

On that particular query you could force order or explicitly use loop join. But that case FORCESEEK hint ON TableA has no sense:

SELECT A.Field1, B.Field2
FROM TableA AS A
INNER LOOP JOIN TableB AS B WITH (FORCESEEK)
    ON A.PrimaryKeyClusteredIndexedField = B.PrimaryKeyClusteredIndexedField
WHERE B.NonClusteredIndexedField = SomeBooleanValue

SELECT A.Field1, B.Field2
FROM TableA AS A
INNER JOIN TableB AS B WITH (FORCESEEK)
    ON A.PrimaryKeyClusteredIndexedField = B.PrimaryKeyClusteredIndexedField
WHERE B.NonClusteredIndexedField = SomeBooleanValue
OPTION(FORCE ORDER)

To use a clustered index in the query hint I would suggest use its number instead of its name:

SELECT A.Field1, B.Field2
FROM TableA AS A (FORCESEEK)
INNER LOOP JOIN TableB AS B WITH (FORCESEEK, INDEX(1))
    ON A.PrimaryKeyClusteredIndexedField = B.PrimaryKeyClusteredIndexedField
WHERE B.NonClusteredIndexedField = SomeBooleanValue

I would normally agree that the index name is better if we are talking about any index except the clustered one. I have seen such different names for clustered indexes. It's not rare for a name not to be at all self-documenting. Using index 1 you're at least sure of using the clustered index.

0
1

This is interesting, I have recently asked a question on dba.stackexchange where suitable indexes were not being used. One of the answers uses a FORCESEEK hint to achieve a more efficient plan which uses an index seek that the optimiser did not manage on its own.

Whilst this is a code change, it still (arguably) retains the readability of the original query but gives the performance of using the index seeks, without having to completely refactor the query.

It seems like for whatever reason, the optimiser doesn't want to use the most efficient plan. In this case, I would be happy to promote to production.

1
  • I have investigated cases where same code would act differently on different production systems (same baseline code, but different customer/data/size = different tipping point between seek and scan). e.g. smaller system = scan; larger ones = seek. We ended up adding FORCESEEK into code as well instead of re-compiling or re-write the code – Jerry Hung Apr 1 '20 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.