Introduction
As part of an analysis I am doing, I wanted to identify potentially un-used indexes.
From Googling, I found the script shared by Foliotek’s John Pasquet to be a sturdy jump off.
John’s script is available at http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/
Code
Here is our very slightly revised version:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO USE [master] GO if object_id('[dbo].[sp_IndexIdentifyUnused]') is null begin exec('create procedure [dbo].[sp_IndexIdentifyUnused] as select 1/0 as [shell] ') end go ALTER proc [dbo].[sp_IndexIdentifyUnused] ( @MinimumPageCount int = 500 , @object sysname = null , @includeDisabled bit = 0 ) AS begin /* a) IDENTIFYING UNUSED Indexes IN A SQL SERVER DATABASE by John Pasquet http://www.foliotek.com/devblog/identifying-unused-tblI-in-a-sql-server-database/ b) Find your unused indexes by Michael Otey http://sqlmag.com/sql-server/tip-finding-unused-indexes */ /* References: a) sys.dm_db_index_usage_stats (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms188755.aspx */ ; with ctePS ( [object_id] , index_id , [rowCount] , [pageCount] ) as ( select tblPS.[object_id] , tblPS.index_id , [rowCount] = sum ( tblPS.[row_count] ) , [pageCount] = sum ( tblPS.[reserved_page_count] ) from SYS.dm_db_partition_stats tblPS group by tblPS.[object_id] , tblPS.index_id ) , cteIndexColumn ( [object_id] , [index_id] , [index] , [column_id] , [column] , [is_included_column] , [key_ordinal] , [index_column_id] ) as ( select [object_id] = tblI.[object_id] , [index_id] = tblI.index_id , [index] = tblI.name , [column_id] = tblIC.column_id , [column] = tblC.[name] , [is_included_column] = tblIC.is_included_column , [key_ordinal] = tblIC.key_ordinal , [index_column_id] = tblIC.index_column_id from sys.objects tblO inner join sys.indexes tblI ON tblO.object_id = tblI.object_id INNER JOIN SYS.index_columns tblIC ON tblI.object_id = tblIC.object_id AND tblI.index_id = tblIC.index_id INNER JOIN sys.columns tblC ON tblIC.object_id = tblC.object_id AND tblIC.column_id = tblC.column_id ) , cteIndexColumnNamesKey ( [object_id] , [index_id] , [listofColumns] ) as ( SELECT [object_id] , [index_id] , SUBSTRING ( ( SELECT ', ' + tblIC.[column] as [text()] FROM cteIndexColumn tblIC WHERE tblIC.object_id = tblI.object_id AND tblIC.index_id = tblI.index_id AND tblIC.is_included_column = 0 ORDER BY tblIC.key_ordinal FOR XML Path('') ), 2, 10000 ) AS [ListofColumnNames] from sys.indexes tblI ) , cteIndexColumnNamesIncluded ( [object_id] , [index_id] , [listofColumns] ) as ( SELECT [object_id] , [index_id] , SUBSTRING ( ( SELECT ', ' + tblIC.[column] as [text()] FROM cteIndexColumn tblIC WHERE tblIC.object_id = tblI.object_id AND tblIC.index_id = tblI.index_id AND tblIC.is_included_column = 1 ORDER BY tblIC.index_column_id FOR XML Path('') ), 2, 10000 ) AS [ListofColumnNames] from sys.indexes tblI ) SELECT [object] = QuoteName(Object_Schema_Name(tblI.object_id)) + '.' + Quotename(object_name(tblI.object_id)) , [Index] = tblI.name , [sql] = 'ALTER INDEX ' + quotename(tblI.name) + ' on ' + QuoteName(Object_Schema_Name(tblI.object_id)) + '.' + Quotename(object_name(tblI.object_id)) + ' DISABLE ' + ';' , [isDisabled] = case is_disabled when 0 then 'No' when 1 then 'Yes' end , [listofKeyColumns] = cteICNK.[listofColumns] , [listofIncludedColumns] = cteICNI.[listofColumns] , [RowCount] = ctePS.[rowCount] , [TotalSizeInMB] = CONVERT(decimal(18,2), ctePS.[pageCount] * 8 / 1024.0) , [UserUpdates] = tblIUS.user_updates FROM sys.Indexes tblI INNER JOIN sys.objects tblO ON tblI.[object_id] = tblO.[object_id] LEFT OUTER JOIN sys.dm_db_index_usage_stats tblIUS ON tblI.[object_id] = tblIUS.[object_id] and tblI.[index_id] = tblIUS.[index_id] LEFT OUTER JOIN ctePS ctePS ON tblI.[object_id] = ctePS.[object_id] and tblI.index_id = ctePS.[index_id] INNER JOIN cteIndexColumnNamesKey cteICNK ON tblI.[object_id] = cteICNK.[object_id] and tblI.[index_id] = cteICNK.index_id LEFT OUTER JOIN cteIndexColumnNamesIncluded cteICNI ON tblI.[object_id] = cteICNI.[object_id] and tblI.[index_id] = cteICNI.index_id /* Filter out records that have not been accessed during User Seeks or Scans */ WHERE ( ( isNull(tblIUS.user_seeks, 0) = 0 ) AND ( isNull(tblIUS.user_scans, 0) = 0 ) AND ( isNull(tblIUS.user_lookups, 0) = 0) ) -- ignore tblI with less than a certain number of pages of memory AND ( (ctePS.[pagecount] >= @MinimumPageCount) or (ctePS.[pagecount] is null) ) -- Skipped MS Objects AND tblO.is_ms_shipped = 0 -- Exclude Heaps AND tblI.index_id != 0 -- Exclude primary keys, which should not be removed AND tblI.is_primary_key != 1 -- Exclude Unique Constraints AND tblI.is_unique_constraint != 1 -- Index is not disabled --AND tblI.is_disabled = 0 AND ( tblI.[object_id] = case when (@object is null) then tblI.[object_id] else object_id(@object) end ) /* IsDisabled */ AND ( (@includeDisabled = 1) or ( ( ( isNull(@includeDisabled, 0) = 0) and ( tblI.is_disabled = 0) ) ) ) ORDER BY tblIUS.user_updates desc , (ctePS.[rowCount]) desc , (ctePS.[pageCount]) desc end go EXEC sys.sp_MS_marksystemobject 'dbo.sp_IndexIdentifyUnused' go