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 |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2002-07-26 : 11:31:23
|
| Hey everyone.I have been going over our databases attempting to improve performance. I've noticed some tables have a ton of indexes, many (if not most) of which I'm sure are not being used. However, for obvious reasons, I don't just want to go deleting indexes willy-nilly.I know that viewing the execution plan from Query Analyzer will show you exactly which indexes are being used. I also know that you can query master..syscacheobjects to see which execution plans are being stored. Is there anyway to combine the two? In other words, I would like to get a list of all the indexes that are used by the currently stored execution plans. If that's not possible, I'll settle for world peace.Thanks.Jerry==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-07-26 : 12:06:01
|
| Your request for world peace has been granted.The best way to prove indexes are no longer needed/used is to run a trace in profiler. Make sure you capture a good sample of some heavy workload time, and don't forget to capture recuring items like weekly, monthly, quarterly, or yearly reports and such. Wade through the sql you capture and determine which indexes to keep, drop, or create. The trick is to make changes one or two at a time and to throughly check the effects of those changes. You can use the index tuning wizard if you want, but you'll want to go over all of its suggestions with a fine tooth comb.Jeff BanschbachConsultant, MCDBA |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-26 : 15:12:22
|
The ITW can be useful in your scenario because it will highlight indexes that are not used in its report. Whilst I am not usually that much of a fan of it, it does have its uses and this scenario is one of them  HTHJasper Smith |
|
|
|
|
|
|
|
|
Figuring out which indexes are used