June 18, 2012 at 4:48 am
hi all,
after analyzing the index usage statistics of our database I found some indexes I'd like to drop. Now there might be the situation that after dropping the index I recognize that it was a bad idea and that the system slows down, maybe not immediately but some at the end of week when specific jobs are running. Of course I could recreate that index but this would take too much time resources.
Is there a possibility to tell the database not to use an index at all but still keep it up to date, so that I could activate it again without losing time? So I could deactivate the index, have a look at the system for some days, and after being sure I could drop it without risk (more or less...)
I found the possibility to disable an index but this actually drops the index and enabling means to rebuild the index, that's not what I want.
Thank's a lot,
Wolfgang
June 18, 2012 at 5:09 am
June 18, 2012 at 5:35 am
Yeah, no way I'm aware of either. The only way to remove the possibility for an index to be used is, drop it, disable it, or put an index hint into your query to use another index (and that will only work if the other index can actually satisfy the query). None of these do what you want and honestly, you should not use #3. You're going to have to drop or disable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2012 at 6:55 am
Thank you for your answers. I feared there would not be a possibility. Well that will be some funny days dropping these indexes 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply