SQL Server – Indexes – Unused Ones

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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s