By default, the query plan uses the best index in available in any scenario.
One reason could be that the index is fragmented.
You could decrease the fragmentation by running the query
ALTER INDEX [myindexname] ON [dbo].[mytable] REBUILD;
You can read more on this here: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
The query plan at times doesn't take the best available index for the query. For this, Microsoft has provided table hints.
- ForceSeek
This tells the optimizer to use an index seek operation in any case.
SELECT *
FROM mytable WITH(FORCESEEK)
WHERE mycolumn IS NULL
Caution: while using any Force Plan hint (ForceSeek,ForceScan,etc.) or you might break the query
- Index Hint
This tells the optimizer to use a particular index for seek/scan
SELECT *
FROM mytable WITH([myindexname])
WHERE mycolumn IS NULL
This query will now use myindexname for above query.
- You can combine both these hints:
SELECT *
FROM mytable WITH([myindexname], ForceSeek)
WHERE mycolumn IS NULL
This query will now use myindexname with a scan operation for above query.
More on table hints here: https://technet.microsoft.com/en-us/library/bb510478(v=sql.105).aspx