• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Index Hints - do they limit the number of indexes used?

I have a query something like this:

select count(*)
from  a  with (index=a.bid),
        b,
        c
where a.bid = b.bid
and b.cid = c.cid
and a.datecol between '1/1/04' and '2/1/04'

a.datecol is indexed
a.bid is indexed
b.bid is indexed
...

I observe the following behavior:  

When run without the hint shown, it uses two indexes from table a: the datecol and then the BID. It appears to decide that the index is a better place to find the IDs it needs for the joins than the table (which is clustered on its unused primary key, aid).  

We used a hint on a.BID to get it to go from C to B to A, theoretically bringing better performance. However, in that case it does NOT use the index on the a.datecol.

I am tuning this database and know what I need to do to improve performance. However, I am curious about the hint on a.BID - will that hint PREVENT the query from using the index on a.datecol?

It seems clear that they do, in which case is it possible to hint more than one index per table (without doing rigamaroles with SQL syntax?

(By the way, I do know how to tune this query with a concatenated index (which the prior DBA did not believe in) but that is not the question).

Thanks,

Aaron

0
AaronAbend
Asked:
AaronAbend
  • 9
  • 6
4 Solutions
 
ShogunWadeCommented:
In all my years of SQL Server I have only ever had one case where an INDEX hint consistently performed better than letting SQL do it itself.

index hinting should be used as an absolute last resort.   often rejigging the query will give you a more appropriate plan.
0
 
AaronAbendAuthor Commented:
I regularly obtain better performance than the optimizer using hints. A microsoft engineer even supported my recommendation that we use hints to force strategies. In Oracle, we had to specify the strategy for years until their cost based optimizer got up to snuff. The fact is, unless 1) the data distribution changes dramatically, or 2) someone launches a query you were not expecting when you tuned the database (usually a reporting query). Index hints can obtain the best performance and not leave you at the mercy of unreliable statistics. (in our database, there are both purges and inserts going on all the time which wreak havoc on the statistics - there are a total of 1 billion records in our database here).

For example, just last week I had a query here that performed 40 times worse than my forced strategy. To check it, I put the database on a quiet machine (with a good sample of about 40 million records). I did full scan stat builds on all of the statistics in all of the tables. I did not build any new stats - just rebuilt the ones that were there.  I put the query in the query analyzer, copied it, added my hint to the second one. It predicted my query would use  98% of the time both queries would take. Mine came back in 1 second, the optimizer's choice in 55 seconds. In both cases, I cleared the data and procedure buffers before each run of each query. I got the same results 4 or 5 times.

Now, the indexing on these tables is very poor for a number of reasons (which is why I am here) so the optimizer was working with a bad situation to begin with. And I DO AGREE that, ideally, we should let the optimizer do its thing. My goal, in fact, is to reindex so that the optimizer's choice for strategy is the best - both theoretically and actually.

But... that was not the question.
0
 
ShogunWadeCommented:
"A microsoft engineer even supported my recommendation"  dont believe everything MS says.   trust me on this i was there the best SQL people definately dont work at  MS.

But quite frankly, there are serious problems if SQL cant optimally construct a plan for the sort of query you have posted.   Such that i would consider that SQL server would not be a marketable product.


if you really want to encourage sql to select certain indexes then change the query to

select count(*)
from  (SELECT * FROM  a where datecol between '1/1/04' and '2/1/04') x
  inner join  b on a.bid=b.bid
  inner join c on b.cid=d.sid


Sure yo probably did have to specify indexing etc in oracle,  but lets face it apples are not pears!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AaronAbendAuthor Commented:
This does not answer the question.

But... If bad query optimization is not a problem, why do I regularly get better performance with my hints - even if the database is poorly designed? Can't the optimizer optimize bad database designs? If it cannot, then it is only going to help the 3% of the applications with good designs...

I have been tuning SQL Server databases since 1984.
0
 
ShogunWadeCommented:
"However, I am curious about the hint on a.BID - will that hint PREVENT the query from using the index on a.datecol? "
the answer to this is yes.   if you only specify the one index in the hint it will only use one.

BOL:
INDEX =

Supported for backward compatibility only.
Consider removing all references to INDEX = and replacing (when using multiple index hints) with references to INDEX(index, index...) as shown in SELECT.

This allows you to specify multiple indexes
0
 
AaronAbendAuthor Commented:
So the presumtion must be that if you only mention one index, it will not allow use of others?  I can believe that - obviously if it were allowed to use other indexes it might just pick its own strategy (though I was thinking it would have to use the hinted index at least once).

By the way I tried your alternative syntax but it did not help (I had tried something similar with an exists, too, which also failed to improve things). The indexes on this database are not doing the optimizer any favors.

I am hoping to get some other POVs before I wrap this up.

Thanks for the BOL reference.
 

0
 
ShogunWadeCommented:
I assume that you have looked closely at the statistics of the tables.    I have often found that tweaking the sample volume of records results in far more accurate results.  
0
 
ShogunWadeCommented:
Also there are time when an index hint will be completely ignored by sql and it will still go off an do its own thing.  Generally this will occur if by iusing the index specified the plan is too complex then sql throws it away.
0
 
AaronAbendAuthor Commented:
That is why they are called - hints - not directives, so that is not surprising.

Do you know what the current limit is for the optimizer? Is there a specific number of tables at which it starts to "lose" it? This number used to be 5, but I am sure it has gone up.
0
 
ShogunWadeCommented:
16 rings a bell but i may be mistaken here.
0
 
AaronAbendAuthor Commented:
Regarding the stats - unfortunately I am working on a log shipped database that I cannot change. I would assume that the stats are part of the problem as well. One of the reasons there are so many single-column indexes is because the previous dba was operating on the assumption that the optimizer would prefer them, since the stats are only "good" (i.e., distributions are computed) for the first column whereas additional columns only have densities. The result is that the indexes are designed to optimize statistics maintenance rather than query performance.  And all of the clusters are on (sometimes unused) primary key identity columns instead of join columns.
0
 
ShogunWadeCommented:
might be worth just taking a quick peak anyway   sp_showstats

generally speaking the optimiser will only target indexes if the density < 0.1
0
 
ShogunWadeCommented:
Aaron,

just a thought,   you might want to dabble with FORCE ORDER  hint.    Ive used this a bit with huge success.
0
 
AaronAbendAuthor Commented:
"Ive used this a bit with huge success"  ... but what about what you said about trusting the optimizer ;^o)?  Thanks... I have not used that hint (I always use specific index hints when I hint, which I try not to do, but..)

Thanks for your help.

0
 
ShogunWadeCommented:
but force order still gives sql some freedom.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now