Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find unused SQL Server indexes with Policy Based Management


By:   |   Updated: 2010-11-30   |   Comments (4)   |   Related: More > 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:

instead of having PBM identify indexes that are infrequently used, you can instead have it identify tables with relatively unused indexes

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:

query will return a scalar value of 1 for any table that has more write activity than read activity

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 leave the Evaluation Mode to "On Demand" and click OK

Now you can right-click the Policy in Object Explorer and choose Evaluate:

click the Policy in Object Explorer and choose Evaluate

You will be presented with this dialog, which warns you about scripts in policies:

dialog that 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):

notice that the policy has identified the table(s) with unused indexes

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


Last Updated: 2010-11-30


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, November 09, 2011 - 7:56:06 AM - Cody Back To Top

Wow, this is pretty fantastic. I'm a report writer and do some DBA work also, but I never even knew PBM existed... suddenly I have something cool to sink my teeth into.

 


Tuesday, December 07, 2010 - 11:23:48 AM - Jason Yousef Back To Top

 

Cool, thanks for the hint, yep no errors so far.

the collation is : Latin1_General_CI_AS_KS_WS

I wonder why my ReportingServer DB is different than the others !


Tuesday, December 07, 2010 - 9:10:57 AM - Aaron Bertrand Back To Top

Hi Jason, sorry you are receiving an error.  What is the collation of ReportServer? 

SELECT collation_name FROM sys.databases WHERE name = N'ReportServer';

I believe you should be able to get around this by enforcing the collation on both sides of the equality for object name and schema name:

  AND OBJECT_NAME(i.[object_id]) COLLATE SQL_Latin1_General_CP1_CI_AS = @@ObjectName COLLATE SQL_Latin1_General_CP1_CI_AS
AND OBJECT_SCHEMA_NAME(i.[object_id]) COLLATE SQL_Latin1_General_CP1_CI_AS = @@SchemaName COLLATE SQL_Latin1_General_CP1_CI_AS

Please let me know if that helps resolve the issue.

Tuesday, December 07, 2010 - 8:52:42 AM - Jason Yousef Back To Top

Hello, Thanks for the article and your efforts, thought i've to share this error with you, i'm using 2008 R2.

the server's collation is : SQL_Latin1_General_CP1_CI_AS, and it gives this error on

ReportServer/tables/dbo.activeSubscriptions

My SSRS is in native mode if you're asking.

 

TITLE: Microsoft SQL Server Management Studio
------------------------------

Exception encountered while executing policy 'Tables should not have unused indexes'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=PolicyEvaluationException&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation. (Microsoft SQL Server, Error: 468)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=468&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Learn more about SQL Server tools