Full site membership is required for this article. Already a full member? Login here. You currently have a guest membership. You can upgrade to full site membership here (free trial available). A Procedure that finds all the Non-Used Indexes in a database   Article Abstract: The following stored procedures queries SQL 2005 dynamic views , finds and drop/reports all the un-used indexes in the database , the procedure can only report unused indexes or alternatively , drop them   Read this article...

Related Articles - For Members. All Articles By Author Tips for using views in SQL Server 2005 Database Design Changes and Change Management SQL Server Views Optimization Tips Using Full-text Search Setup full-text search: step by step guide Indexes - Determining Unused Indexes Indexes - Good Choices, Bad Choices Dynamic SQL, Dynamic Ordering and Paging Announcement: NetIQ Launches SQL Management Suite 2.0

RE: A Procedure that finds all the Non-Used Indexes in a database
by S Maloney (posted: 9/26/2008 6:38:26 AM)
It is worth noting that the procedure may very well return a result set that includes constraints (in addition to not yet used indices). {As a best practice one shouldn't generally consider removing constraints (primary keys, etc.) just because activity hasn't yet occurred against the tables on which they are implemented. As it happens in this specific case, attempting to drop a constraint with a drop index statement should throw a DBMS exception and thereby inadvertently avoid database consistency harm.} However more caution / elaboration is advisable! Some users may not fully realize the possibility of [potentially very serious] negative future implications of dropping logical constraints - and - because the (physical) index names of constraints happen to be returned by the procedure; may attempt to drop important constraints by other means.

RE: A Procedure that finds all the Non-Used Indexes in a database
by Jason Harrison (posted: 9/26/2008 9:09:18 AM)
Everyone should keep in mind that this view, sys.dm_db_index_usage_stats, is initially empty after SQL restarts.

So, if you were to run this process to DROP indexes after SQL has recently restarted, you could drop a lot of your indexes.

Make sure you run it after you have had a considerable amount of uptime and high utilization by your users.

RE: A Procedure that finds all the Non-Used Indexes in a database
by Ken Stuber (posted: 9/26/2008 10:09:52 AM)
Keep in mind that you'll need to edit the procedure to change system table/view/column names to the appropriate case if you use a case-sensitive collation. The case-sensitivity extends to table/column names and not just data. For example SYS.DM_DB_INDEX_USAGE_STATS becomes sys.dm_db_index_usage_stats, A.OBJECT_ID becomes A.object_id, etc...

Besides this and the other comment regarding keys, this is a very handy little script!