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 |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-09-26 : 13:21:56
|
| Is there a good way to get a handle on index utilization? Specifically, indexes that are not utilized by the optimizer.I have a handful or stored procs that I am tuning in a production environment. I find it pretty easy to look at execution plans to determine how adding indexes on columns can help execution.But how do you know which indexes on a table should be dropped? I've used the Index Tuning Wizard in the past, and I know that it can make recommendations based on a workload. I'm not a fan of using magic and spells on computers.I suppose I could query syscomments across all my databases to find all the procs that query a given table. Then I could go look at plans for each of these procs to find out which indexes they use. But with several databases, with many tables each and probably thousands of procs, that seems like a daunting task. Not to mention the fact that for every index I add, I may likely be alienating some indexes. So for every index I add, I'll need to redo my 'which indexes can I drop' process.Typically, I like to create indexes as I write procs. However, in this case, I am working with (mostly) other peoples code and for some reason, those other people haven't yet learned about primary keys or clustered index.Many of the tables in question are quite big, so I'd like to cut the dead weight of unused indexes.Any advice?Jay White{0} |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-26 : 13:33:11
|
| To add one more thing...Peppered throughout many of these procs are joins to tables in different databases (using the three-part name) ... it's my understanding that sp_depends and sp_MSDependencies do not report on dependencies outside the current database. Is there anyway to get these references without reading syscomments?Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-26 : 13:58:22
|
quote:
it's my understanding that sp_depends and sp_MSDependencies do not report on dependencies outside the current database. Is there anyway to get these references without reading syscomments?
Not without using some third-party tool, AFAIK. And I don't even know of a tool that does it either.As far as getting rid of unnecessary indexes, IMHO, the BEST way, and IMNSHO the most thorough way, is to drop all of the indexes on every table in every database. Re-evaluate the procedures and such, and maybe run the Index tuning wizard, and THEN add indexes as needed. Since you'd be starting with a clean slate the ITW will be less likely to be confused by an existing index. You can always script out the databases for the index code and reapply all of them in one fell swoop if you need. You can also scan the code and see if anything looks out of whack or unnecessary. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-26 : 14:55:41
|
I suppose I shouldn't go through and drop every index on the clients production DBMS this afternoon ...  ... oh, on dev, you say? Whats that?Jay White{0} |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-27 : 14:36:13
|
| Precise have a new tool that can do this, I saw a brief demo of this recently and it looked promising - it was designed from the start to be used as a production tool and to have a very low monitoring cost on the server. Of course it does a lot more than this but index utilisation (or lack of) can be tracked, another feature I really liked the look of is the ability to track the performance of an SP over time i.e. as data volumes increase you can track performance, see if those original indexes are still relavent. A trial download is available[url]http://www.precise.com/products/indepthSQL.asp[/url]HTHJasper Smith |
|
|
|
|
|
|
|
|
Index Tuning