代码改变世界

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

2016-11-01 15:26  潇湘隐者  阅读(1537)  评论(2)  编辑  收藏  举报

前言感想：一时兴起，突然想写一个关于MS SQL的巡检系列方面的文章，因为我觉得这方面的知识分享是有价值，也是非常有意义的。一方面，很多经验不足的人，对于巡检有点茫然，不知道要从哪些方面巡检，另外一方面，网上关于MS SQL巡检方面的资料好像也不是特别多。写这个系列只是一个分享，自己的初衷是一个知识梳理、总结提炼过程，有些知识和脚本也不是原创，文章很多地方也是融入了自己的一些想法和见解的，不足和肤浅之处肯定也非常多，抛砖引玉，也希望大家提意见和建议、补充，指正其中的不足之处。Stay Hungry Stay Foolish！

 

在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:
 
;WITH    XMLTable
          AS ( SELECT   OBJECT_NAME(x.object_id) AS TableName ,
                        SCHEMA_NAME(o.schema_id) AS SchemaName ,
                        x.object_id ,
                        x.name ,
                        x.index_id ,
                        x.using_xml_index_id ,
                        x.secondary_type ,
                        CONVERT(NVARCHAR(MAX), x.secondary_type_desc) AS secondary_type_desc ,
                        ic.column_id
               FROM     sys.xml_indexes x ( NOLOCK )
                        JOIN sys.objects o ( NOLOCK ) ON x.object_id = o.object_id
                        JOIN sys.index_columns (NOLOCK) ic ON x.object_id = ic.object_id
                                                              AND x.index_id = ic.index_id
             ),
        DuplicatesXMLTable
          AS ( SELECT   x1.SchemaName ,
                        x1.TableName ,
                        x1.name AS IndexName ,
                        x2.name AS DuplicateIndexName ,
                        x1.secondary_type_desc AS IndexType ,
                        x1.index_id ,
                        x1.object_id ,
                        ROW_NUMBER() OVER ( ORDER BY x1.SchemaName, x1.TableName, x1.name, x2.name ) AS seq1 ,
                        ROW_NUMBER() OVER ( ORDER BY x1.SchemaName DESC, x1.TableName DESC, x1.name DESC, x2.name DESC ) AS seq2 ,
                        NULL AS inc
               FROM     XMLTable x1
                        JOIN XMLTable x2 ON x1.object_id = x2.object_id
                                            AND x1.index_id < x2.index_id
                                            AND x1.using_xml_index_id = x2.using_xml_index_id
                                            AND x1.secondary_type = x2.secondary_type
             )
    SELECT  SchemaName ,
            TableName ,
            IndexName ,
            DuplicateIndexName ,
            IndexType  ,
            Index_ID ,
            [Object_ID] ,
            1 AS IsXML
    FROM    DuplicatesXMLTable dtxml
    ORDER BY 1 ,
             2 ,
             3;

 

在每个库跑一次这个脚本，就能将所有的重复的索引（Duplicate Indexes）全部找出，但是当手头服务器、数据库特别多时，这个工作也是一个体力活，可以将这个常规工作自动化，避免重复劳动，我将这个集成在MyDBA工具里面，只需要点击一下鼠标，就可以帮助我自动处理这些工作。