Find unused SQL Server indexes with Policy Based Management

By: Aaron Bertrand   |   Comments (4)   |   Related: > Policy Based Management

Problem

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.

Solution

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:

1. 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.
   
2. 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.
   
3. 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.

Conclusion

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.

Next Steps

• 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:
  • Using Policy-Based Management in SQL Server 2008
  • Retaining historical index usage statistics for SQL Server
  • Find all SQL Server columns of a specific data type using Policy-Based Management
  • SQL Server Policy-Based Management Blog : ExecuteSQL()

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips