For Day 24 of this series, we start out with Query #54, which is Bad NC Indexes. This query retrieves information from the sys.dm_db_index_usage_stats dynamic management view and the sys.indexes object catalog view about non-clustered indexes that have more writes than reads in the current database. Query #54 is shown in Figure 1.
Figure 1: Query #54 Bad NC Indexes
What you are looking for with this query are indexes that have high numbers of writes and very few or even zero reads. If you are paying the cost to maintain an index as the data changes in your table, but the index is never used for reads, then you are placing unneeded stress on your storage subsystem that is not providing any benefits to the system. Having unused indexes also makes your database larger, and makes index maintenance more time consuming and resource intensive.
One key point to keep in mind before you start dropping indexes that appear to be unused is how long your SQL Server instance has been running. Before you drop an index, consider whether you have seen your complete normal business cycle. Perhaps there are monthly reports that actually do use an index that normally does not see any read activity with your regular workload.
Query #55 is Missing Indexes. This query retrieves information from the sys.dm_db_missing_index_group_stats dynamic management view, the sys.dm_db_missing_index_groups dynamic management view, the sys.dm_db_missing_index_details dynamic management view and the sys.partitions catalog view about “missing” indexes that the SQL Server Query Optimizer thinks that it would like to have in the current database. Query #55 is shown in Figure 2.
Figure 2: Query #55 Missing Indexes
This query is very useful, but also very easy to misinterpret and misuse. I have seen many novice DBAs and developers use the results of this query to pretty badly over-index their databases, which affects their database size and hurts insert, update, and delete performance. I like to focus on the last_user_seek column, and see how long ago that was. Was it a few seconds or minutes ago, or was it days or weeks ago?
I then start looking at the user_seeks, avg_total_user_cost, and avg_user_impact columns to get a sense for how often SQL Server thinks it wants this proposed index, how expensive it is not to have the index, and how much the query optimizer thinks the cost of the query would be reduced if it did have this index that it is requesting.
Next, I’ll look at any other proposed indexes on the same table to see if I can come up with a wider, consolidated index that covers multiple requested indexes. Finally, I’ll look at the existing indexes on that table, and look at the index usage metrics for that table to have a better idea of whether a new index would be a good idea, based on the volatility of that table.
This query is very similar to Query #28, but this one is only for the current database. It also pulls back the number of rows in a table, which is useful information when you are considering creating a new index, especially when you are using SQL Server Standard Edition, which does not have online index operations.
Query #56 is Missing Index Warnings. This query retrieves information from the sys.dm_exec_cached_plans dynamic management view, the sys.dm_exec_query_plan dynamic management function about missing index warnings in the plan cache for the current database. Query #56 is shown in Figure 3.
Figure 3: Query #56 Missing Index Warnings
This query (which can be time consuming on a busy instance with a large plan cache) shows you where you have cached query plans with missing index warnings. This is very helpful, since it can often help you tie requested “missing” indexes to a particular stored procedure or prepared query plan.