美高梅开户-美高梅官方网站开户

您的位置:美高梅开户 > 数据库 > 美高梅开户:SQL巡检系列

美高梅开户:SQL巡检系列

发布时间:2020-01-01 16:26编辑:数据库浏览(95)

     

     

     

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

    注意,关于重复索引(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。 这个脚本是找出一模一样的索引,如果你创建下面索引,索引字段一样,但是有包含列字段不一样,那么这个脚本会将这个索引视为不一样的索引。有兴趣可以自己试试。

     

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

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

    美高梅开户 1

     

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

    --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;
    
     
    
    CREATE INDEX IX_Address_N1 ON [Person].[Address](StateProvinceID);
    

     

     

     

     

     

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

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

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

    美高梅开户 2

    ;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
    

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

    本文由美高梅开户发布于数据库,转载请注明出处:美高梅开户:SQL巡检系列

    关键词: 美高梅开户

上一篇:MySQL中的索引,开发中的mysql

下一篇:没有了