SQL query with multiple indexes - SQL server 2000

Question

I use a similar query like this

select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)...

I get the following error

Only one list of index hints per table is allowed

This seems to be working well with SQL Server 2005. Is it an issue with SQL server?

Answer 1

Actually, you shouldn't be giving index hints in the first place.

Explanation

1. One of the objectives behind the SQL language is to separate the 'what' from the 'how'. In other words; your queries should indicate the rules of the result sets you require and not the data access paths (which is precisely what index hints do).
2. By binding your queries to specific indexes, you lose the ability to gain performance improvements by adding better indexes. I.e. You also have to modify your queries.
3. Many of the hint options are platform specific; you reduce portability when yo use them.
4. The query optimiser has been written to consider all indexes, various join scenarios, and most importantly; statistical information on the data in your tables. Even if you are able to cover all these bases yourself, and determine the ideal indexes to use today; in 6 months time the statistical frequency of certain values, records, references within your database may have changed - and your index selection may no longer be any good!

Side Note

If the optimser appears to be making a silly choice as to which indexes to use; this generally warrants further investigation.

• As a first step; are your table statistics reasonably up to date?

• Secondly, be sure that the optimiser isn't rejecting a particular index because in actual fact said index reduces performance. For example, you might be tempted to do one of the following:

  SELECT  Col1, Col2, Col3, ...
  FROM    Customers WITH (INDEX=IndexByName)
  WHERE   FistName LIKE 'A%'
  
  
  SELECT  Col1, Col2, Col3, ...
  FROM    Customers WITH (INDEX=IndexByName)
  ORDER BY FirstName
  

The index hints seem perfectly logical; however:

• If the index is clustered, or a covering index: it would be used anyway - without the hint.
• If the index is non-clustered and non-covering: Bookmark-lookups would be required for each record retrieved. This incurs a tremendous amount of overhead; especially on disk seek activity. So consequently the indexes are a poor choice after all.

Finally

I'm not sure if it is the case; but your question doesn't indicate it being a complex multi table query. So it may in fact be as trivial as follows?

SELECT  Col1, Col2, ...
FROM    ATable WITH (INDEX=Index1, INDEX=Index2)

Whatever the situation, it certainly doesn't make any sense to hint multiple indexes for a single table (unless it is used multiple times with self-joins). You said:

This seems to be working well with SQL Server 2005.

And I have to ask: Are you sure?
I tried it out; and as much as it didn't cause an error message - it seriously confused the optimiser. It forced the optimiser to traverse the same table twice (unnecessarily) and join the result sets back to each other - incurring a tremendous overhead!!

Answer 2

I think it may be because of the syntax you're using.

Instead of (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2), try:

(INDEX=IX_TABLE_1, IX_TABLE_2)

I think it is the fact you have 2 "INDEX=" parts.

Also, I would recommend only using index hints as a last resort as the query optimiser should generally choose the best plan/indexes to use. This is why generally, you should trust the optimizer. If you do use index hints, it's a good idea to review them fairly frequently as they may become worse over time (e.g. as data volumes grow, what did originally perform better with the hint, may start performing worse).