How can I get my query to change the Indexes it uses?
-
You can try the 'WITH INDEX (index_id)' table option. More info can be found in BOL under search item 'FROM' clause (described).
Another option you might light to try is to use the 'WITH FORCEORDER' option. Simply append this string to the end of you T-SQL statement and see if it works for you.
-
I think the "With Index()" Option will help~
-
I would run it through the profiler and then run it through the index tuning wizard. You then may be able to see why it is using the wrong indexes. If the app or query is written properly and you have the proper keys and indexes you should not have to specify a with index option.
i went through a similiar scenario not to long ago, the profiler showed me that the app was hitting the wrong indexes and we had to re-apply an older index until the code could be changed to use the primary key instead of the index in the queries.
-
oops Also, did you update the statistics on these tables after reindexing?
If this is coming from a stored proc, I would also do a sp_recompile on the table as well.
-
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Mike, that's what I did. Before I posted, I had already run a trace with profiler, found the problem query, determined the execution plan in Query Analyzer, and noticed it was using an Index that didn't "fit" what the query was looking for, in this case, the PK index was perfectly suited.
So I used the "With" clause to force it to use the PK Index and I was right, it took seconds instead of 30 minutes (all of which is in my original post).
Unfortunately, I can change the query in Query Analyzer to test, but I can't change it in the application, so "WITH" couldn't solve my problem, it helped in determining the solution though.
Anyway, after about 2 hours after posting, I an MS article on the Index Tuning Wizard, never used it before, and it solved the problem.
Said the best index to use was the PK Clustered Index, but strangely enough it then added a FOURTH index and used that one instead. Not sure why, but it accomplished the same thing. (Think it was an indexed view instead though, which kind of makes sense).
Thank you all for the replies, if I hadn't found the answer last night, I definitely would have found it this morning here
EDIT: Rudy, any idea how long that would take on a 4Gig DB?
And, no, I didn't update statistics after the re-index, So I'm sure all of my execution plans are off a little. I do have auto-update turned on though, so I assumed that would do the same thing, is that wrong?
-
My roughly 4GB (probably more) prod instance, takes about 10 minutes to run those tasks.
Tuning Wizard is a good tool, be careful with selecting to add indexed views though, as I have had issues where it added them to all kind of stuff and totally screwed perfromance. I never check that add indexed views box when i run it and only save them out to script.
I have also seen it recomending adding a index with every key in the table, which you really dont want. so I do not always trust it.
Having to add the fourth index is more than likely a result of the way the query is written in the code.
MM
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply