SQL Server – Indexes – Identify Duplicates – Ignore Included Columns

Background

Again using John Pasquet’s excellent work.

This time I am trying to identify possibly duplicate Indexes. Not going to consider included columns, just the actual key columns.

Btw, John’s work is available at http://www.foliotek.com/devblog/query-to-identify-duplicate-or-redundant-indexes-in-sql-server/

Code

use master
go
 
 
if object_id('[dbo].[sp_IndexIdentifyDuplicatesIgnoreIncludedColumns]') is null
begin
 
    exec('create procedure [dbo].[sp_IndexIdentifyDuplicatesIgnoreIncludedColumns] as select 1/0 as [shell] ')
 
end
go
 
alter procedure [dbo].[sp_IndexIdentifyDuplicatesIgnoreIncludedColumns]
as
 
    ; with 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
                         
 
    )
 
    , IndexColumnInclude
    as
    (
       select
          tblI.object_id
        , tblI.index_id
        , [includedColumnCount] =count(*)
 
      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
 
 
    where tblIC.is_included_column = 1
 
    group by
          tblI.object_id
            , tblI.index_id
 
    )
    , IndexSummary 
    (
          [schema]
        , [Table Name]
        , [Index Name]
        , is_primary_key
        , is_unique_constraint
        , [Indexed Column Names]
        , [Included Column Names]
        , [object_id]
        , [index_id]
 		, [numberofRecords]
    )
    AS
    (
 
        SELECT DISTINCT
               [schema] = schema_name(tblO.schema_id)
             , tblO.name AS [Table Name]
             , tblI.name AS [Index Name]
             , tblI.is_primary_key
             , tblI.is_unique_constraint
             , 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 [Indexed Column Names]
 
                , ISNULL(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.key_ordinal
                              , tblIC.index_column_id
 
                        FOR XML Path('')
                        )
                            , 2
                        , 10000
                      ), ''
                       ) AS [Included Column Names]
 
                    , tblI.object_id
                    , tblI.index_id
					, [numberofRecords]
						= tblSP.[rows]
 
        FROM sys.indexes tblI
 
            INNER JOIN sys.index_columns tblIC
 
                ON  tblI.index_id = tblIC.index_id
 
                AND tblI.object_id = tblIC.object_id
 
            INNER JOIN sys.objects tblO
 
                ON tblO.object_id = tblI.object_id  
 
            INNER JOIN sys.partitions tblSP
 
                ON  tblI.object_id = tblSP.object_id  
				AND tblI.index_id = tblSP.index_id  

        WHERE tblO.[type] = 'U'
 
    )
    , DupIndexes
    as
    (
        SELECT
              IndexSummary.[schema]
            , IndexSummary.[Table Name] 
            , IndexSummary.[Index Name] 
            , IndexSummary.is_primary_key
            , IndexSummary.is_unique_constraint
            , [Index Name Dup] = IndexSummaryDup.[Index Name]
            , IndexSummary.[Indexed Column Names] 
            , IndexSummary.[Included Column Names]
            , [includedColumnCount] = isNull(IndexColumnInclude.[includedColumnCount],0)
            , IndexSummary.object_id
            , IndexSummary.index_id
			, IndexSummary.[numberofRecords]
 
        FROM IndexSummary
 
            INNER JOIN IndexSummary as IndexSummaryDup
 
                ON   IndexSummary.[object_id] = IndexSummaryDup.[object_id]
                AND  IndexSummary.[Indexed Column Names] = IndexSummaryDup.[Indexed Column Names]
 
                AND  IndexSummary.[index_id] != IndexSummaryDup.[index_id]
 
            LEFT OUTER JOIN IndexColumnInclude
 
                ON  IndexSummary.object_id = IndexColumnInclude.object_id
                AND IndexSummary.index_id = IndexColumnInclude.index_id
 
    )
 
 
    select
              [Table] = DupIndexes.[schema]
                         + '.'
                         + DupIndexes.[Table Name]
 
            , [Indexed Columns]
                 = min( DupIndexes.[Indexed Column Names])
 
            , [Index] 
                = DupIndexes.[Index Name]
 
            , [Included Columns] 
                = min( DupIndexes.[Included Column Names])
 
            , is_primary_key
                = min(cast (is_primary_key as tinyint))
 
            , is_unique_constraint
                = min(cast( is_unique_constraint as tinyint))
 
            , [includedColumnCount]
                = min([includedColumnCount])
 
            ,  'Index size (MB)'
                = CAST(8 * sum(reserved_page_count)                
                    / 1024.0 AS DECIMAL(18,0))

 			, [numberofRecords]
				= avg([DupIndexes].[numberofRecords])

    from   DupIndexes
 
                inner join sys.dm_db_partition_stats PhysicalStats
 
                     ON  DupIndexes.object_id = PhysicalStats.object_id
                    AND  DupIndexes.index_id = PhysicalStats.index_id 
 
    group by
              [schema]
            , DupIndexes.[Table Name]
            , DupIndexes.index_id 
            , DupIndexes.[Index Name]
 
    ORDER BY
                DupIndexes.[Table Name]
              , min( DupIndexes.[Indexed Column Names])
              , min( DupIndexes.[Included Column Names])
              , DupIndexes.[Index Name]
              , sum(reserved_page_count) desc
go
 
exec sp_MS_marksystemobject 'dbo.sp_IndexIdentifyDuplicatesIgnoreIncludedColumns'
go


Documentation

sys.indexes

Column Name Description Product
 index_column_id ID of the index column. index_column_id is unique only within index_id. Order of Included  Key columns
column_id  Column ID Match with object_id to join with column table ( sys.columns)
 key_ordinal Ordinal (1-based) within set of key-columns.

0 = Not a key column, or is an XML index, a columnstore index, or a spatial index.

 Order of Key index columns; for included columns will be 0
 is_included_column 1 = Column is a nonkey column added to the index by using the CREATE INDEX INCLUDE clause, or the column is part of a columnstore index.

0 = Column is not an included column.

 

 

Brief Explanation:

It seems that Microsoft made a tactical change and started using key_ordinal rather than index_column_id for the order of Indexed columns.

 

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