Solved

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

Posted on 2004-09-07
15
459 Views
Last Modified: 2012-06-27
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
Comment
Question by:AaronAbend
  • 9
  • 6
15 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11997439
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
 
LVL 10

Author Comment

by:AaronAbend
ID: 11997601
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
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 500 total points
ID: 11997729
"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
 
LVL 10

Author Comment

by:AaronAbend
ID: 11997924
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
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 11998251
"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
 
LVL 10

Author Comment

by:AaronAbend
ID: 11998314
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11998347
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:ShogunWade
ID: 11998368
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
 
LVL 10

Author Comment

by:AaronAbend
ID: 11998389
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11998446
16 rings a bell but i may be mistaken here.
0
 
LVL 10

Author Comment

by:AaronAbend
ID: 11998464
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
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 500 total points
ID: 11998490
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
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 500 total points
ID: 11998577
Aaron,

just a thought,   you might want to dabble with FORCE ORDER  hint.    Ive used this a bit with huge success.
0
 
LVL 10

Author Comment

by:AaronAbend
ID: 11998689
"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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11998771
but force order still gives sql some freedom.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now