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 |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2005-02-22 : 11:48:33
|
| I've inherited a database doing a ton of queries and a lot of indexes where some look to be questionable and probably not used at all in the apps latest incarnation. Analyzing each query at a time would be best, but there isn't time or money for that at the moment. Is there any way to do a trace and analysis on it that will let me count up the amount of times an index is used? A bonus would be to list the queries that each index was used in and counts. Any ideas?Thanks. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-02-22 : 17:54:53
|
Use Profiler to capture the SQL statements that are run for an extended time period, say one day, and then use the Index Tuning Wizard to analyze the data to see what indexes it recommends to add or drop. Use your judgment to decide what to do with the recommendations.See the details on how to do this in BOL.quote:
Originally posted by sureshot
I've inherited a database doing a ton of queries and a lot of indexes where some look to be questionable and probably not used at all in the apps latest incarnation. Analyzing each query at a time would be best, but there isn't time or money for that at the moment. Is there any way to do a trace and analysis on it that will let me count up the amount of times an index is used? A bonus would be to list the queries that each index was used in and counts. Any ideas?Thanks.
Codo Ergo Sum |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-22 : 18:12:01
|
Index Tuning Wizard, what's that ?  Ok, maybe this is a good use for it.Does it really recommend which indexes to drop ?rockmoose |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-02-22 : 18:36:11
|
You can have it show indexes to drop, and it even generates scripts for you.It is a really valuable tool when you are given a database that you know nothing about and are asked to improve performance.As with any tool, read the safety instructions, and don't cut off your fingers.quote:
Originally posted by rockmoose
Index Tuning Wizard, what's that ? Ok, maybe this is a good use for it.Does it really recommend which indexes to drop ?rockmoose
Codo Ergo Sum |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2005-02-23 : 18:30:40
|
| I was always a bit wary of that Wizard. Now that you mention it it, I actually remember running it a while ago and it didn't suggest dropping anything and I wasn't sure it was trustworthy. I guess they're all used (or misused perhaps...) and I'll have to dig deeper at it. Oh well. Thanks. |
|
|
|
|
|
|
|
|
Index Usage