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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
16 rings a bell but i may be mistaken here.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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.
Thanks for your help.
but force order still gives sql some freedom.
index hinting should be used as an absolute last resort. often rejigging the query will give you a more appropriate plan.