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
