This post is Part 3 of my series of posts on Index Maintenance for a DBA. So here goes.
As a regular part of your DBA work, it is very important to keep evaluating the need for indexes in your database environment. Any DBA worth his salt, will always look to “maintain” the indexes by ensuring the fragmentation is kept low. Most of you probably have maintenance tasks/scripts to take care of this. An often under-looked aspect in index maintenance is to also consider whether some indexes are an over-kill a.k.a unnecessary and potentially drop them if it’s not really utilized. This is especially important when dealing with 3rd party vendor databases, where the DBA does not have full control on what comes in and what goes out in terms of index changes.
I like to think of the database as my “house” and take care of it the same way one would do to your own home. You always consider who/what comes in to your house. The same care needs to be applied to the databases you maintain. As gatekeepers to the database, even if you don’t have complete control on what comes in, it is good to be aware & have the stats handy so that you can work with your application teams or vendors, to keep the house tidy.
If you link this data back to my previous posts Part 1 – Seek & Destroy Duplicate Indexes in your SQL Database and Part 2 – Seek & Understand Top Indexes by Size in your SQL database , you will be on your way to hopefully free up some space and realize space cost and performance gains.
Here is a handy script that I use to:-
1) Lists the indexes on each table in the database, that are completely unused.
This scripts lists those indexes where not even a single Read operation has used these indexes. Keep in mind, write operations still need to update this index as data is being updated/inserted contributing to overall time of the Write operations. When no-one is using the index for Read it becomes a system overhead.
2) Lists the indexes on each table in the database, that are having more Writes compared to Reads
The maintenance cost of having these indexes from insert, update, or delete operations on the underlying table are more compared to the benefits for Read operations. These indexes needs to be investigated to see if they are still required. A cost benefit analysis on these indexes needs to be performed.
Note: I’m not advocating that you remove these indexes, but just that you carefully consider the benefits vs the cost, before making any decision.
Script #1 – TOTALLY UNUSED INDEXES
USE [DBNAME] /* Replace with your Database Name */ GO --TOTALLY UN-USED INDEXES SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique, s.user_updates AS [Total Writes], (s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads], s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference], (partstats.used_page_count / 128.0) AS [IndexSizeinMB] FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id AND s.database_id = DB_ID() INNER JOIN sys.dm_db_partition_stats AS partstats ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND user_updates > (user_seeks + user_scans + user_lookups) AND (s.user_lookups=0 AND s.user_scans=0 AND s.user_seeks=0) AND i.index_id > 1 ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE); GO
Script #1 Result
Here is a sample output from my test system showing all unused indexes on the MSDB database.
Script #2 – INDEXES WITH MORE WRITES THAN READS
USE [DBNAME] /* Replace with your Database Name */ GO --INDEXES WITH WRITES > READS SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique, s.user_updates AS [Total Writes], (s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads], s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference], (partstats.used_page_count / 128.0) AS [IndexSizeinMB] FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id AND s.database_id = DB_ID() INNER JOIN sys.dm_db_partition_stats AS partstats ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND (s.user_lookups<>0 OR s.user_scans<>0 OR s.user_seeks<>0) AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups) AND i.index_id > 1 ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE); GO
Script #2 Result
Here is a sample output from my test system showing all indexes having more Writes compared to Reads on the MSDB database.
Well, that’s all folks.
-Sudarshan (TheSQLDude)
. 
The SQL Dude! 