?
Solved

Index hints

Posted on 2003-03-20
22
Medium Priority
?
509 Views
Last Modified: 2012-06-21
I just want to find out whether anybody has come across any examples of needing to use index hints to prompt MSSQL to use an index that does exist.  The only situation I am aware of is if the table is ridiculously large - 3.5 million rows, for example - but I'm interested in whether there are any other situations in which the query ought to make use of the index but does not.
0
Comment
Question by:Belazir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +4
22 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 8175129
YOu can tell sql server to use and index that does NOT exist--you can tell it to use an index that does exist.

I have to give hints once in a while, but usually I've found out that when you need to give hints a lot, it's because you have bad or out of data statistics on the table.

Take a look at UPDATE STATISTICS and CREATE STATISTICS in SQL Server BOL.

Brett
0
 
LVL 2

Expert Comment

by:tony_o1
ID: 8177746
If you want to update all statistics there is a system stored procedure 'sp_updatestats' that will update them all at once
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 8178639
Unfortunately, I had this exact problem yesterday. There was a perfectly good index but nothing short of recreating the indexes allowed the optimizer to see the obvious index. I tried updating stats several times (manually, and through EM) and using the dbcc indexdefrag but neither worked - only an index hint worked. I tried everything I could think of to "kick the index into view" of the optimizer. The DBCC dbreindex command will do this for you, but on my 50 million row table, it takes hours to execute. From what we can tell, there were no unusual operations that caused the indexes to fall out of view of the index. By the way, one symptom of our problem was a count of zero in the enterprise manager for the table.  

This may answer your question, but if someone provides you a way to avoid the reindexing, I will happily match your 69 point offer with another 100 points.  I am going to follow up with a call to Microsoft as soon as I finish researching. It may take a while to get an answer on this from them.
0
Industry Leaders: 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!

 

Author Comment

by:Belazir
ID: 8179275
aaron's is the type of thing i'm after - i just want examples of where it has happened and why, not necessarily potential solutions
0
 
LVL 34

Expert Comment

by:arbert
ID: 8181642
It's the optimizer that makes it happen.  It looks at the access plan and if it thinks the cost of doing IO on an index is too high, it will choose not to use it.  A lot of times you have a good index, but there is also a bookmark lookup that has to be done after using that index that causes the IO to skyrocket....

Brett
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 8181719
Brett, the problem with that logic is that if I force the index, performance is 7 seconds, but without the force, it does not use the index, and the query takes 24 seconds. This is a consistent benchmark (I used dbcc dropcleanbuffers to establish that memory caching was having essentially no impact).  I ran the query 5 or 6 times with and as many times without the hint. Even though SQL2000 is supposed to learn as it goes, it seems it cannot use this information. The index is marked as "bad" by the optimizer, I suspect.

To respond to Belazir, I have not been able to figure out the conditions that cause the problem. I have now seen it at least 3 times on my development system. There are relatively few transactions on some of the tables that have the problem, so simple index fragmentation is not the problem (and presumably, the dbcc indexdefrag would fix that problem).  It seems to happen on tables that have been hanging around for a long time without much going on. And though I have not been able to prove this, I believe it may happen more often with tables that do not have any clustered indexes (this is a wild theory, but I am out of ideas).
0
 
LVL 34

Expert Comment

by:arbert
ID: 8182143
Also, sometimes when SQL Server creates statistics on larger tables (like your 3.5million rows), it just takes a sampling that may NOT be indicative of the actual data as a whole.  You can create your statistics WITH FULLSCAN to tell SQL Server to create statistics on all rows.  Sometimes this has a tendancy to correct things as well.

Brett
0
 

Author Comment

by:Belazir
ID: 8182208
aaron, are you able to comment on that last suggestion of arbert's?  about the WITH FULLSCAN?
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 8182212
Hmm. I know I tried that once, but it is quite possible I failed to rebuild with fullscan this time. OK, I'll buy that. Next time I see this problem, I will see if building stats using fullscan helps.

The logic would have to follow that the partial scan was not statistically valid.

This would not explain getting 0 records in sysindexes (the same query that gets a count in Enterprise Manager). I will have a chance to try this on Monday afternoon.

Aaron
0
 
LVL 34

Expert Comment

by:arbert
ID: 8182280
Ya, that's weird you have 0 recrods in sysindexes--why didn't you say that before?????  Have you done a DBCC NEWALLOC or a DBCC CHECKALLOC to make sure the structure of the DB is sound????

Brett
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 8182393
I did mention in my first post.

I have not tried the dbcc's you suggest. The zero value is only for this table, other tables are correct. But I will definitely try these next time I see this problem, and I will post the result on this question even if it is closed.

If it works, you'll get those points I mentioned in that post, too!
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8186514
The Query Optimizer uses proximate analysis of a query only. I saw one page long query at SP3 Beta forum describing a computation of query cost values. Sometimes the difference is 0.543 x 0.544 and the worst plan is used. This can change with every service pack or a very little change of statistics. If your query speed is essential for your application, do not rely on the Query Optimizer and add fixed index hints.

Good luck !
0
 

Author Comment

by:Belazir
ID: 8194392
A colleague has just tried that WITH FULLSCAN option and no, it does not work.

Am I to assume then, getting back to the point, that the only situation this happens is with massive recordsets?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8194587
Create a new database, fill data and compare results of
query plan got by "SET SHOWPLAN_ALL ON" (in Excel :). Then try with less data once again. Maybe it is 0.543x0.544 optimizer problem.
And what about DBCC SHOWCONTIG?
0
 
LVL 34

Expert Comment

by:arbert
ID: 8195093
No, you're not to assume that this only happens with massive recordsets.  I think you missed the underlying problem.  There should not be an entry in sysindexes saying 0--something is corrupt or out of synch....

You really should consider dropping the index and rebuilding it and also DBCC checkalloc.

Brett
0
 

Author Comment

by:Belazir
ID: 8195140
Brett, that wasn't the original question - that was something that Aaron threw into the mix.  I've seen a demonstration of this very recently where, as Ispaleny says, the index hint is necessary to make SQL2K use the index as the optimizer comes up with the wrong result - no problem with sysindexes or anything else we can spot for that matter, simply that the formulas the query optimizer uses to work out how best to do it can come up with the wrong result for massive recordsets (despite using WITH FULLSCAN).  What I wanted to know was whether there was any other scenario where this would happen.
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 8195206
I have already rebuilt my index and that fixed the problem, so I know 2 things:  1) the index, when available with "perfect" stats (since index had just been rebuilt and no transactions had been executed against the table) is determined as the optimal path. 2) the zero records in the sysindexes table is clearly a symptom of a problem, a bug, and not something that should be expected by the optimizer. So, I believe there is a bug in MSSQL here. Most likely, the problem is more likely to occur with more records, but there is no reason the problem could not occur with just a few records.  My personal pet theory is that I built the table and did some work with a few records before populating it. Maybe the old stats stayed in place and some system level problem kept the sysindexes table from being properly updated. There are major problems with locking in the data dictionary in MS SQL Server (just try viewing management stats in EM while building an index).  I have reproducible benchmarks that show complete locking of dictionary tables while executing "transactions" and I believe that to mitigate these gross table locks, the system defines transactions as atomically as possible. This leads to the potential of something like an index build completing as a transaction without the attended sysindexes entries being updated.

That is my view at this point.

Aaron
0
 
LVL 34

Expert Comment

by:arbert
ID: 8195242
Aaron, do you have auto create stats and auto update stats on?

Belazir, the original question was about using hints and Aaron also said that sysindexes contained 0 for the row count--my answer is correct in saying that a hint should not be needed and there is an integrity problem when the database says there are 0records in an index for a 3.5million row table....

brett
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 8195290
If you mean did I Select the "Do not recompute..." on the index build, I can say that I did not select that option. However, normally I build indexes from scripts, and from what I can see, the default is to automatically compute the stats (when I look in EM, the box is not checked on indexes created from scratch).  It is possible that EM does not accurately reflect the index status, though. Any thoughts?



0
 
LVL 1

Expert Comment

by:Computer101
ID: 8580337
A request for deletion or PAQ has been made.  If no response or you feel this is in error, comment.  If no objection, I or another Moderator will handle this question in three days.

Computer101
E-E Admin
0
 
LVL 34

Expert Comment

by:arbert
ID: 8580874
People gave quite a few answers and in the end it looks like the question was answered to me....
0
 

Accepted Solution

by:
Chmod earned 0 total points
ID: 8623745
PAQ'd and points refunded, as the comments did not actually resolve the problem - See
http://www.experts-exchange.com/Community_Support/Q_20621866.html

Chmod
Community Support Moderator @Experts Exchange
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

765 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