Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lou
Yak Posting Veteran
59 Posts |
 Posted - 2002-02-13 : 14:39:30
|
| Should I avoid index hints? I've read in several places that I should let the optimizer do its thing. I am a developer and do not maintain the actual databases. (Many of the tables that I have to use have missing statistics). I usually wind up getting frustrated and give explicit hints to my slow running queries. Any advice? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 14:59:04
|
| If the optimizer doesn't seem to be giving you a good plan, you can certainly try index hints to see if they improve things. Make sure you TEST the results with and without hints and compare them. If you find that your hints do a significantly better job, then use them.Also check to see what indexes ARE available on the table(s) in the query, what columns are joined (if any), and run UPDATE STATISTICS on the indexes periodically. If the right columns have the right indexes and are up-to-date, the optimizer will most likely find the best plan. |
 |
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2002-02-13 : 15:16:33
|
quote:
Should I avoid index hints? I've read in several places that I should let the optimizer do its thing. I am a developer and do not
From my experience the optimizer does do a very good job, most of the time. But it's not human, which means sometimes it needs a little help.For instance just last week a developer came to me with a stored procedure that was running slow, but when the identical code was executed in Query Analyzer it run 5x as fast. We looked at all the usual suspects, including recompile the procedure. But for whatever reason SQL Server would create different execution plans between the stored procedue and QA, though the code was identical. I was finally forced to use a couple index hints to fix the problem.Jerry======================================================="The world has achieved brilliance without conscience.Ours is a world of nuclear giants and ethical infants." -- General Omar N. Bradley (1893-1981) |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-02-13 : 16:23:01
|
| Thanks guys! |
|
|
|
|
|
|
Index Hints?