|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Unused indexesI have inherited a SQL Server 2000 database. It is obvious why some of the
indexes exists. But I'm doubtful that others are needed. Is there any way to know if, when, or how often an index is used? Thanks, Mark MarkR (Ma***@discussions.microsoft.com) writes:
> I have inherited a SQL Server 2000 database. It is obvious why some of Profiler could be used, but it's a little complex. SQL 2005 has somewhat> the indexes exists. But I'm doubtful that others are needed. Is there > any way to know if, when, or how often an index is used? better methods, but they are not foolproof. One problem with determining whether indexes are in use, is that some indexes may be good for end-of-the-month reports and that sort of thing. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx You could look at the results from
sys.dm_db_index_usage_stats but as Erland indicates the results have to be thought about eg the data is derived from the cache, and it might be that you bounce the server often which would invalidate the conclusions. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)  |
|||||||||||||||||||||||
Other interesting topics