SQL Server 索引碎片整理

索引碎片整理的四种方法：

1）删除索引并重建

2）使用 DROP_EXISTING 语句重建索引

3）使用 ALTER INDEX REBUILD 语句重建索引

4）使用 ALTER INDEX REORGANIZE 重新组织索引

--1.查看碎片

SELECT  DB_NAME() AS DatbaseName ,

        SCHEMA_NAME(o.Schema_ID) AS SchemaName ,

        OBJECT_NAME(s.[object_id]) AS TableName ,

        i.name AS IndexName ,

        ROUND(s.avg_fragmentation_in_percent, 2) AS [Fragmentation %] ,

        CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片，索引需要重建'

             WHEN avg_fragmentation_in_percent >= 5

                  AND avg_fragmentation_in_percent < 30 THEN '轻度碎片，索引需要重新组织'

             ELSE '正常状态'

        END 提示

FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s

        INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

        INNER JOIN sys.objects o ON i.object_id = O.object_id

ORDER BY [Fragmentation %] DESC  

--2.整理碎片（建议在空闲时间运行，尤其不要在生产环境运行）

SET NOCOUNT ON

DECLARE @Objectid INT ,

    @Indexid INT ,

    @schemaname VARCHAR(100) ,

    @tablename VARCHAR(300) ,

    @ixname VARCHAR(500) ,

    @avg_fip FLOAT ,

    @command VARCHAR(4000)

DECLARE IX_Cursor CURSOR

FOR

    SELECT  A.object_id ,

            A.index_id ,

            QUOTENAME(SS.NAME) AS schemaname ,

            QUOTENAME(OBJECT_NAME(B.object_id, B.database_id)) AS tablename ,

            QUOTENAME(A.name) AS ixname ,

            B.avg_fragmentation_in_percent AS avg_fip

    FROM    sys.indexes A

            INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL,

                                                      NULL, 'LIMITED') AS B ON A.object_id = B.object_id

                                                              AND A.index_id = B.index_id

            INNER JOIN SYS.OBJECTS OS ON A.object_id = OS.object_id

            INNER JOIN sys.schemas SS ON OS.schema_id = SS.schema_id

    WHERE   B.avg_fragmentation_in_percent > 10

            AND B.page_count > 20

            AND A.index_id > 0

            AND A.IS_DISABLED <> 1

            --AND OS.name='book'

ORDER BY    avg_fip DESC ,

            tablename ,

            ixname

OPEN IX_Cursor

FETCH NEXT FROM IX_Cursor INTO @Objectid, @Indexid, @schemaname, @tablename,

    @ixname, @avg_fip

WHILE @@FETCH_STATUS = 0

    BEGIN

        --碎片率>5%或<=30%，索引重组

        IF @avg_fip < 30.0

            SET @command = N'ALTER INDEX ' + @ixname + N' ON ' + @schemaname

                + N'.' + @tablename + N' REORGANIZE ';

        --碎片率>=30%，索引重建

        IF @avg_fip >= 30.0

            AND @Indexid = 1

            BEGIN

                IF EXISTS ( SELECT  *

                            FROM    SYS.columns

                            WHERE   OBJECT_ID = @Objectid

                                    AND max_length IN ( -1, 16 ) )

                    SET @command = N'ALTER INDEX ' + @ixname + N' ON '

                        + @schemaname + N'.' + @tablename + N' REBUILD ';

                ELSE

                    SET @command = N'ALTER INDEX ' + @ixname + N' ON '

                        + @schemaname + N'.' + @tablename + N' REBUILD '

                        + N' WITH (ONLINE = ON)';

            END

        IF @avg_fip >= 30.0

            AND @Indexid > 1

            BEGIN

                IF EXISTS ( SELECT  *

                            FROM    SYS.index_columns IC

                                    INNER JOIN SYS.columns CS ON CS.OBJECT_ID = IC.OBJECT_ID

                                                              AND CS.column_id = IC.column_id

                            WHERE   IC.OBJECT_ID = @Objectid

                                    AND IC.index_id = @Indexid

                                    AND CS.max_length IN ( -1, 16 ) )

                    SET @command = N'ALTER INDEX ' + @ixname + N' ON '

                        + @schemaname + N'.' + @tablename + N' REBUILD ';

                ELSE

                    SET @command = N'ALTER INDEX ' + @ixname + N' ON '

                        + @schemaname + N'.' + @tablename + N' REBUILD '

                        + N' WITH (ONLINE = ON)';

            END

        --打印命令，单独执行

        PRINT @command

        --直接执行命令

        --EXEC(@command)

        FETCH NEXT FROM IX_Cursor INTO @Objectid, @Indexid, @schemaname,@tablename, @ixname, @avg_fip

    END

CLOSE IX_Cursor

DEALLOCATE IX_Cursor