轻松识别重复索引

现在SQL2005提供了DTA的工具，大家在去优化一个语句时都有意无意的使用此工具所给出的一些优化建议。不过它始终是个工具，所给出的优化建议很多时候都是使用2005新的索引功能INCLUDE把查询列表统统包括在一个索引中。因此，每个开发人员所定义的索引就会存在重复或是很相似的地方。因为索引页的数据比较密集，因此在对包含有索引列的字段做修改操作时，都会去相应的修改包含此键值列的索引。理论上对一张表多加一个索引，修改数据的速度就会比原来慢1.2倍。因此，这会增加记录被锁定的时间，从而也就会影响到查询的性能。

但是，如果通过SQL2005提供的几个与索引相关的视图，我们不能很方便的观察出索引所包含的键值列和它的包含列是哪些。同时，如果表是分区表，通过sys.partitions查看总记录数时要累加各分区的行数。

下面的脚本可以组合这些视图，查询出对象名称、对象类型（表或索引视图）、索引名称、索引编号、索引类型、是否主键、是否唯一、填充度、键值字段、包含字段、表的总记录数（取各分区中行的总数）、索引描述，如下图部分显示结果所示，这样就很方便的判断出哪些索引是重复或相似的：

对取包含字段时用到了FOR XML PATH这个功能，可以方便的把包含列组织成A,B,C的形式。然后使用CROSS APPLY得出最终的结果。脚本定义如下：

USE AdventureWorks;
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader(CustomerID)
INCLUDE(ShipDate,Freight)
GO

--sp_helpindex不能反应出包含字段
EXEC sp_helpindex 'Sales.SalesOrderHeader'
GO
--SQL2005下用于诊断索引重复的脚本
DECLARE @Result TABLE(
    objname            sysname            NOT NULL,
    objtype            char(2)            NOT NULL,
    indexname        sysname            NOT NULL,
    index_id        int                NOT NULL,
    indextype        tinyint            NOT NULL,
    is_primary_key  bit                NOT NULL,
    is_unique        bit                NOT NULL,
    fill_factor        tinyint            NOT NULL,
    IndexKeys        nvarchar(2126)    NOT NULL,
    Included        nvarchar(max)    NULL,
    rows            bigint            NOT NULL,
    IndexDesc        varchar(210)    NULL
)


CREATE TABLE #IndexInfo
(
    IndexName    sysname         NOT NULL,
    IndexDesc    varchar(210)     NULL,
    IndexKeys    nvarchar(2126)     NULL
)
DECLARE @objname     sysname

DECLARE ObjectList CURSOR FAST_FORWARD FOR
    SELECT SCHEMA_NAME(o.schema_id)+'.'+o.name AS objname
    FROM sys.indexes i JOIN sys.objects o ON i.object_id=o.object_id
    WHERE o.type IN('U','V') AND i.index_id IN(0,1) 
        --AND o.object_id=OBJECT_ID(N'Sales.SalesOrderHeader')

OPEN ObjectList
FETCH NEXT FROM ObjectList INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #IndexInfo EXEC sp_helpindex @objname--使用全名称，防止直接使用表名称时无法获取其它架构表的信息
    
    INSERT INTO @Result
     SELECT SCHEMA_NAME(o.schema_id)+'.'+o.name AS objname, o.type AS objtype,
         i.name AS indexname,i.index_id,i.type AS indextype,i.is_primary_key,i.is_unique,i.fill_factor,
        t.IndexKeys,
        c.name AS Included,
        p.rows,t.IndexDesc
    FROM sys.indexes i
         INNER JOIN sys.objects o ON i.object_id=o.object_id
         INNER JOIN #IndexInfo t ON t.IndexName=i.name
         CROSS APPLY (SELECT SUM(rows) AS rows 
                         FROM sys.partitions p
                         WHERE p.index_id = i.index_id AND p.object_id = i.object_id
                     ) p
         CROSS APPLY (SELECT name=STUFF((SELECT N',' + QUOTENAME(y) AS [text()]
                       FROM (SELECT c.name AS y
                                FROM sys.index_columns ic
                                    JOIN sys.columns c ON ic.column_id=c.column_id AND ic.object_id=c.object_id
                                WHERE ic.object_id=i.object_id AND ic.index_id=i.index_id  AND ic.is_included_column=1
                            ) AS Y
                       ORDER BY y FOR XML PATH('')), 1, 1, N'')
                     ) c
    WHERE o.object_id=OBJECT_ID(@objname)
            
    TRUNCATE TABLE #IndexInfo
    
  FETCH NEXT FROM ObjectList INTO @objname
END

CLOSE ObjectList
DEALLOCATE ObjectList

DROP TABLE #IndexInfo
SELECT * FROM @Result ORDER BY objname,index_id

用于SQL2000的脚本：

--SQL2000下用于诊断索引重复的脚本
DECLARE @Result TABLE (
    [objname] [sysname] NOT NULL ,
    [indexname] [sysname] NOT NULL ,
    [indid] [smallint] NOT NULL ,
    [IsUnique] [int] NOT NULL ,
    [IndexKeys] [nvarchar] (2126) NOT NULL ,
    [rowcnt] [bigint] NOT NULL ,
    [rowmodctr] [int] NOT NULL ,
    [keycnt] [smallint] NOT NULL ,
    [OrigFillFactor] [tinyint] NOT NULL ,
    [dpages] [int] NOT NULL ,
    [IndexDesc] [varchar] (210) NULL 
)

CREATE TABLE #IndexInfo
(
    IndexName    sysname     NOT NULL,
    IndexDesc    varchar(210)     NULL,
    IndexKeys    nvarchar(2126)     NULL
)
DECLARE @objname     sysname,
        @objid        int

DECLARE ObjectList CURSOR FAST_FORWARD FOR 
    SELECT USER_NAME(o.uid)+'.'+o.name AS objname,o.id AS objid
    FROM dbo.sysobjects o JOIN dbo.sysindexes i ON i.id = o.id
    WHERE o.type IN( 'U','V') AND i.indid IN(0,1) AND o.name<>'dtproperties'--用于保存关系图的系统表
    ORDER BY o.name,o.uid 

OPEN ObjectList
FETCH NEXT FROM ObjectList INTO @objname,@objid

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #IndexInfo EXEC sp_helpindex @objname--使用全名称，防止直接使用表名称时无法获取其它用户表的信息
    
    INSERT INTO @Result
     SELECT USER_NAME(o.uid)+'.'+o.name AS objname, i.name AS indexname, i.indid, 
        CASE WHEN t.IndexDesc LIKE '%unique%' THEN 1 ELSE 0 END AS IsUnique,
        t.IndexKeys, i.rowcnt, i.rowmodctr, i.keycnt, i.OrigFillFactor, i.dpages,t.IndexDesc
    FROM dbo.sysindexes i
         INNER JOIN dbo.sysobjects o ON i.id = o.id
         INNER JOIN #IndexInfo t ON t.IndexName=i.name
    WHERE o.id=@objid
    TRUNCATE TABLE #IndexInfo
    
  FETCH NEXT FROM ObjectList INTO @objname,@objid
END

CLOSE ObjectList
DEALLOCATE ObjectList

DROP TABLE #IndexInfo
SELECT * FROM @Result ORDER BY objname,indid

如果你有更方便的方法，请分享！