May 8, 2007 at 5:34 am
Is there any way to determine which table indices have been used during any given period of time? I am not talking about reviewing individual SQL queries to determine indices used or needed. Instead, I am interested in monitoring a complex system in effort to determine which indices, if any, are never used.
May 8, 2007 at 6:07 am
sql 2005 has this ability ( but i've not checked it actually works.) there is no easy way to do this in sql 2000.
Mu usual approach is to profile by table, but it's not a quick method.
The index tuning wizard is very good at suggesting dropping indexes, probably the only thing it does do!! but you have to pass a few days trace through it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 8, 2007 at 8:12 am
Agree with Colin, there's no good way to do this. If you have an idea of the queries being run, you could see how many of the SARG operators match up with the indexes in place.
If you decide to remove any, be sure you script them so that if performance worsens, you can put them back. Also, I'd remove one a day and watch performance. Don't yank them all at once as you might not know which one was being used heavily.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply