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.
1: -- Possible Bad NC Indexes (writes > reads) (Query 54) (Bad NC Indexes)
2: SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
3: i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
4: user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
5: user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
6: FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
7: INNER JOIN sys.indexes AS i WITH (NOLOCK)
8: ON s.[object_id] = i.[object_id]
9: AND i.index_id = s.index_id
10: WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
11: AND s.database_id = DB_ID()
12: AND user_updates > (user_seeks + user_scans + user_lookups)
13: AND i.index_id > 1
14: ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
15:
16: -- Look for indexes with high numbers of writes and zero or very low numbers of reads
17: -- Consider your complete workload, and how long your instance has been running
18: -- Investigate further before dropping an index!
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.
1: -- Missing Indexes for current database by Index Advantage (Query 55) (Missing Indexes)
2: SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
3: migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
4: mid.equality_columns, mid.inequality_columns, mid.included_columns,
5: migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
6: OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
7: FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
8: INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
9: ON migs.group_handle = mig.index_group_handle
10: INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
11: ON mig.index_handle = mid.index_handle
12: INNER JOIN sys.partitions AS p WITH (NOLOCK)
13: ON p.[object_id] = mid.[object_id]
14: WHERE mid.database_id = DB_ID()
15: ORDER BY index_advantage DESC OPTION (RECOMPILE);
16:
17: -- Look at index advantage, last user seek time, number of user seeks to help determine source and importance
18: -- SQL Server is overly eager to add included columns, so beware
19: -- Do not just blindly add indexes that show up from this query!!!
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.
1: -- Find missing index warnings for cached plans in the current database (Query 56) (Missing Index Warnings)
2: -- Note: This query could take some time on a busy instance
3: SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],
4: query_plan, cp.objtype, cp.usecounts, cp.size_in_bytes
5: FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
6: CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
7: WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
8: AND dbid = DB_ID()
9: ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
10:
11: -- Helps you connect missing indexes to specific stored procedures or queries
12: -- This can help you decide whether to add them or not
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.