IT技术互动交流平台

MS SQL巡检系列 检查重复索引

作者:潇湘隐者  来源:IT165收集  发布日期:2016-11-01 19:52:46

在SQL Server数据库中,有可能存在重复的索引(Duplicate Indexes),这个不仅影响性能(INSERT、UPDATE、DELETE时带来额外的IO开销,当数据库维护,索引重组时也会带来额外的开销),而且占用空间。数据库存在重复索引(Duplicate Indexes)的原因是多方面的,很多时候、很多事情不是你所能完全掌控的,除非你所管理的数据库非常规范,权限控制、脚本发布非常严格、流程化。暂且不说这些,那么怎么在数据库巡检过程找出这些重复的索引(Duplicate Indexes)呢? 下面分享一个我在Premier Proactive Services中发现一个的脚本(做了一些修改和调整)。

 

我们以AdventureWorks2014数据库为例,如下所示,表[Person].[Address]下有4个索引,如下所示

 

clipboard

 

假设某个二愣子在这个表的字段StateProvinceID上创建了下面重复索引,IX_Address_N1 与IX_Address_StateProvinceID是一个重复索引。

 
CREATE INDEX IX_Address_N1 ON [Person].[Address](StateProvinceID);

 

那么我们执行下面脚本就能找到这个重复的索引,如下所示

;WITH    IndexColumns
          AS ( SELECT DISTINCT
                      SCHEMA_NAME(o.schema_id)     AS SchemaName    ,
                      OBJECT_NAME(o.object_id)     AS TableName     ,
                      i.name                       AS IndexName     ,
                      o.object_id                  AS [Object_ID]   ,
                      i.index_id                   AS Index_ID      ,
                      i.type_desc                 AS IndexType      ,
                      ( SELECT    CASE key_ordinal
                                    WHEN 0 THEN NULL
                                    ELSE '[' + COL_NAME(k.object_id,
                                                        column_id) + '] '
                                         + CASE WHEN is_descending_key = 1
                                                THEN 'Desc'
                                                ELSE 'Asc'
                                           END
                                  END AS [data()]
                        FROM      sys.index_columns  k WITH(NOLOCK)
                        WHERE     k.object_id = i.object_id
                                  AND k.index_id = i.index_id
                        ORDER BY  key_ordinal ,
                                  column_id
                      FOR
                        XML PATH('')
                      ) AS IndexColumns ,
                        CASE WHEN i.index_id = 1
                             THEN ( SELECT  '[' + name + ']' AS [data()]
                                    FROM    sys.columns (NOLOCK) AS c
                                    WHERE   c.object_id = i.object_id
                                            AND c.column_id NOT IN (
                                            SELECT  column_id
                                            FROM    sys.index_columns (NOLOCK)
                                                    AS kk
                                            WHERE   kk.object_id = i.object_id
                                                    AND kk.index_id = i.index_id )
                                    ORDER BY column_id
                                  FOR
                                    XML PATH('')
                                  )
                             ELSE ( SELECT  '[' + COL_NAME(k.object_id,
                                                           column_id) + ']' AS [data()]
                                    FROM    sys.index_columns k WITH(NOLOCK) 
                                    WHERE   k.object_id = i.object_id
                                            AND k.index_id = i.index_id
                                            AND is_included_column = 1
                                            AND k.column_id NOT IN (
                                            SELECT  column_id
                                            FROM    sys.index_columns kk
                                            WHERE   k.object_id = kk.object_id
                                                    AND kk.index_id = 1 )
                                    ORDER BY key_ordinal ,
                                            column_id
                                  FOR
                                    XML PATH('')
                                  )
                        END AS IndexInclude
               FROM     sys.indexes  i WITH(NOLOCK) 
                        INNER JOIN sys.objects o WITH(NOLOCK) ON i.object_id = o.object_id
                        INNER JOIN sys.index_columns ic  WITH(NOLOCK ) ON ic.object_id = i.object_id
                                                              AND ic.index_id = i.index_id
                        INNER JOIN sys.columns c WITH(NOLOCK) ON c.object_id = ic.object_id
                                                              AND c.column_id = ic.column_id
               WHERE    o.type = 'U'
                        AND i.index_id <> 0  -- 0 = 堆
                        AND i.type <> 3         -- 3 = XML  
                        AND i.type <> 5         -- 5 = 聚集列存储索引(SQL 2014~ SQL 2016)
                        AND i.type <> 6         -- 6 = 非聚集列存储索引(SQL 2014~ SQL 2016)
                        AND i.type <> 7         -- 7 = 非聚集哈希索引(SQL 2014~ SQL 2016)
               GROUP BY o.schema_id ,
                        o.object_id ,
                        i.object_id ,
                        i.name ,
                        i.index_id ,
                        i.type_desc
             ),
        DuplicatesTable
          AS ( SELECT   ic1.SchemaName    ,
                        ic1.TableName     ,
                        ic1.IndexName     ,
                        ic1.[Object_ID]   ,
                        ic2.IndexName AS DuplicateIndexName ,
                        ic1.IndexType   ,
                        CASE WHEN ic1.index_id = 1
                             THEN ic1.IndexColumns + ' (Clustered)'
                             WHEN ic1.IndexInclude = '' THEN ic1.IndexColumns
                             WHEN ic1.IndexInclude IS NULL THEN ic1.IndexColumns
                             ELSE ic1.IndexColumns + ' INCLUDE ' + ic1.IndexInclude
                        END AS IndexCols ,
                        ic1.index_id
               FROM     IndexColumns ic1
                        JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id
                                                 AND ic1.index_id < ic2.index_id
                                                 AND ic1.IndexColumns = ic2.IndexColumns
                                                 AND ( ISNULL(ic1.IndexInclude, '') = ISNULL(ic2.IndexInclude,
                                                              '')
                                                       OR ic1.index_id = 1
                                                     )
             )
    SELECT  SchemaName ,
            TableName ,
            IndexName ,
            DuplicateIndexName ,
            IndexType,
            IndexCols ,
            Index_ID ,
          Object_ID ,
          0 AS IsXML
    FROM    DuplicatesTable dt
    ORDER BY 1 , 2 ,3

clipboard

 

注意,关于重复索引(Duplicate Indexes)表示存在的索引除了名字不一样外, 索引所在字段以及索引字段顺序都是一样的。An index is considered to be a duplicate if it references the same column and ordinal position as another index in the same database。 这个脚本是找出一模一样的索引,如果你创建下面索引,索引字段一样,但是有包含列字段不一样,那么这个脚本会将这个索引视为不一样的索引。有兴趣可以自己试试。

 

CREATE INDEX IX_Address_N2 ON [Person].[Address](StateProvinceID) INCLUDE (City);

 

另外关于XML索引的重复索引,可以使用下面脚本检查。

--Use the below T-SQL script to generate the complete list of duplicate XML indexes in a given database:
				

				
Tag标签: 索引  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规