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

Index hints

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
Belazir
Asked:
Belazir
  • 7
  • 6
  • 4
  • +4
1 Solution
 
arbertCommented:
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
 
tony_o1Commented:
If you want to update all statistics there is a system stored procedure 'sp_updatestats' that will update them all at once
0
 
AaronAbendCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
BelazirAuthor Commented:
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
 
arbertCommented:
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
 
AaronAbendCommented:
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
 
arbertCommented:
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
 
BelazirAuthor Commented:
aaron, are you able to comment on that last suggestion of arbert's?  about the WITH FULLSCAN?
0
 
AaronAbendCommented:
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
 
arbertCommented:
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
 
AaronAbendCommented:
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
 
ispalenyCommented:
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
 
BelazirAuthor Commented:
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
 
ispalenyCommented:
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
 
arbertCommented:
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
 
BelazirAuthor Commented:
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
 
AaronAbendCommented:
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
 
arbertCommented:
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
 
AaronAbendCommented:
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
 
Computer101Commented:
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
 
arbertCommented:
People gave quite a few answers and in the end it looks like the question was answered to me....
0
 
ChmodCommented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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