Find unused SQL Server indexes with Policy Based Management
In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server's overall performance by only maintaining useful indexes - but finding the ones you don't need can be quite a manual process.
Introduced in SQL Server 2008, Policy-Based Management (PBM) can help to automate the process of discovering indexes that are more trouble than they are worth - meaning they are updated more often than they are read. However, the PBM approach can require a little creativity - as Tom LaRock recently pointed out, PBM does not expose the name of an index via @@ObjectName, making it cumbersome to build a condition that identifies individual indexes.
So, instead of having PBM identify indexes that are infrequently used, you can instead have it identify tables with relatively unused indexes, and then have additional queries to help narrow down the indexes once you've identified a table that violates your policy. You can start by creating a new condition that will identify tables with high-cost indexes; this condition will be used in a policy.
In Object Explorer, expand Management >Policy Management, then right-click Conditions, and select "New Condition..." Give the condition a descriptive name (such as "Table has at least one unused index") and choose a Facet of "Table." For the Expression, click the "Advanced Edit" button (...) next to the Field column. Here you will paste this code:
Here is the code for copy and paste purposes:
ExecuteSql('Numeric', 'SELECT COALESCE((SELECT TOP 1 1 FROM sys.indexes AS i INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id] AND i.[index_id] = s.[index_id] WHERE s.[database_id] = DB_ID() AND OBJECTPROPERTY(i.[object_id], ''IsMsShipped'') = 0 AND OBJECT_NAME(i.[object_id]) = @@ObjectName AND OBJECT_SCHEMA_NAME(i.[object_id]) = @@SchemaName AND s.user_updates > (s.user_scans + s.user_seeks + s.user_lookups)), 0)')
This query will return a scalar value of 1 for any table that has more write activity than read activity, and 0 for tables that either have more read activity than write activity or have no activity at all.
Next, change the Operator to equal to (=), enter a value of 0, and click OK. The dialog should look like this:
Now that the condition has been created, you can create a policy for evaluation; right-click the Policies node and choose "New Policy..." You can give it a descriptive name (such as "Tables should not have unused indexes"), and choose the Condition you just created above. For now you can leave the Evaluation Mode to "On Demand" and click OK:
Now you can right-click the Policy in Object Explorer and choose Evaluate:
You will be presented with this dialog, which warns you about scripts in policies:
When you click Run, you can see the results as follows (I ran this against my local copy of AdventureWorksLT2008, after inserting a few new rows into the table SalesLT.Customer):
You'll notice that the policy has identified the table(s) with unused indexes, but it has not indicated the actual index(es) that have violated the policy. So while not entirely automatic, this is where your own scripts can come in handy. If you create a stored procedure to help with this, you can at least rely on PBM to alert you when you should check. Here is the stored procedure I use:
CREATE PROCEDURE dbo.DBA_UnusedIndexInfo @table_name NVARCHAR(520) = NULL AS BEGIN SET NOCOUNT ON; SELECT table_name = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])), index_name = QUOTENAME(i.name), s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update FROM sys.indexes AS i INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id] AND i.[index_id] = s.[index_id] WHERE s.[database_id] = DB_ID() AND OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0 AND i.[object_id] = COALESCE(OBJECT_ID(@table_name), i.[object_id]) AND s.user_updates > (s.user_scans + s.user_seeks + s.user_lookups) ORDER BY table_name, index_name; END GO
You can call it with a table name parameter if you only have a few items in the policy results, or you can call it without a parameter if you want to identify all tables (and indexed views!) in your database that may have violated the policy.
Now if you want to schedule the policy to run once a week, or once a month, you can right click the Policy, click Properties, and change the Evaluation Mode to "On Schedule." You can pick an existing schedule or create a new one, quite similar to scheduling a SQL Server Agent job.
You need to be careful to ignore false positives when running this query. A few things to keep in mind:
- Since the query relies on a DMV, do not be too trusting shortly after a failover, reboot or service restart. As we all know, DMVs are cleared out and reset when the service starts. Unless you are persisting DMV statistics, you cannot rely on the data to paint a complete picture of your index usage.
- Similarly, you need to allow a full business cycle to complete before really believing that an index is "unused." If you have reporting queries that only run at the end of the month, quarter or fiscal year, you may unwittingly alter or delete indexes that you should ultimately keep. On the other hand, this can help to identify indexes that you could consider only creating shortly before the times they are needed and dropping them shortly thereafter, to avoid their maintenance costs when they aren't necessary.
- Be aware of indexes that are intentionally dropped and re-created as a part of ETL or other business processes. This will affect the DMV just like a service restart would, eliminating all usage history, and polluting your information.
Policy-Based Management can help automate the discovery of unused or infrequently-used indexes, even if the approach for doing so is not immediately obvious. Even only running the policy on demand should save keystrokes every time you try to remember or dig up the query to find unused indexes. Hopefully this will help you discover other ways you can use PBM to keep you informed about potential performance issues on your servers.
- Create a condition that checks for tables with infrequently-used indexes.
- Create an on-demand policy that uses the condition.
- Evaluate the policy.
- Change the policy to run on an appropriate schedule.
- Browse the following tips and other resources:
About the author
View all my tips
Article Last Updated: 2010-11-30