August 4, 2006 at 3:44 pm
Is there a tool or method/script that I might use in order to monitor index usage?
I've got a large database that has "adopted" many indexes over time and I'd like to find some way to eliminate the indexes that are rarely/never used. I have a static report which provides some indexes to target but the report only points out potentially poorly formed, redundant indexes. I'd really like to get a report of seldom-used indexes over time.
I've tried using the SQL Profiler (writing the results to a table) based on the scan completed event w/ index id & object Id as data columns - but this method creates >100K rows in ~5mins (so I suspect that I'm not using the correct event(s), filters etc.
Any thoughts appreciated!
Glenn
August 7, 2006 at 6:31 am
This is one of the few things (in my opinion) that I actually find the index tuning wizard genuinely useful for.
Build up a full profiler trace of your application, recording as many transactions as possible over as large a period as possible, and save the results as a trace file. Replay the resulting trace file into the ITW on a TEST SERVER (don't do it on production - a) it's risky in case you accidentally drop important indexes, and b) ITW can create lots of intermediate indexes and then not drop them again.
As part of the analysis, ITW will report all indexes that were never used as well as reporting on those which were used and recommending new indexes.
You may feel that this is little better than your static report, but I'm not sure how else to achieve what you want. All you can do, really, is to either repeat this method periodically and note whether or not the unused indexes stay unused, or do a similar thing with your own report.
Ultimately, only you can decide whether you're comfortable with dropping an apparently unused index or not, and you can only get the confidence to do this by gradually accumulating more and more evidence that it is, indeed, unused.
August 7, 2006 at 7:19 am
Philip - thanks fo rthe reply. I have been doing the SQL Profiler/ITW periodically. I guess that, given your statememnt and what little I know, I'll continue with this process.
Do you have any thoughts as to best practice mix of events/data columns/filters?
Glenn
August 7, 2006 at 1:42 pm
I would agree with Philip with one exception: try to create a server side trace with limited impact on the server, since most likely
it will be running against most of the tables in DB, possibly creating performance issues...
August 8, 2006 at 4:41 pm
Here is an article 'How to Identify Non-Active SQL Server Indexes'.
using Profiler and the Index Tuning Wizard with recommendations on Profiler settings, as you requested;
It looks tedious but could be fruitful for you. Good luck!
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply