SQL Server 索引碎片整理

--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 

1. -1.查看碎片
2.  
   SELECT DB_NAME() AS DatbaseName ,
3.  
   SCHEMA_NAME(o.Schema_ID) AS SchemaName ,
4.  
   OBJECT_NAME(s.[object_id]) AS TableName ,
5.  
   i.name AS IndexName ,
6.  
   ROUND(s.avg_fragmentation_in_percent, 2) AS [Fragmentation %] ,
7.  
   CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片，索引需要重建'
8.  
   WHEN avg_fragmentation_in_percent >= 5
9.  
   AND avg_fragmentation_in_percent < 30 THEN '轻度碎片，索引需要重新组织'
10.  
    ELSE '正常状态'
11.  
    END 提示
12.  
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
13.  
    INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
14.  
    INNER JOIN sys.objects o ON i.object_id = O.object_id
15.  
    ORDER BY [Fragmentation %] DESC
16.  
     
17.  
    --2.整理碎片（建议在空闲时间运行，尤其不要在生产环境运行）
18.  
    SET NOCOUNT ON
19.  
    DECLARE @Objectid INT ,
20.  
    @Indexid INT ,
21.  
    @schemaname VARCHAR(100) ,
22.  
    @tablename VARCHAR(300) ,
23.  
    @ixname VARCHAR(500) ,
24.  
    @avg_fip FLOAT ,
25.  
    @command VARCHAR(4000)
26.  
    DECLARE IX_Cursor CURSOR
27.  
    FOR
28.  
    SELECT A.object_id ,
29.  
    A.index_id ,
30.  
    QUOTENAME(SS.NAME) AS schemaname ,
31.  
    QUOTENAME(OBJECT_NAME(B.object_id, B.database_id)) AS tablename ,
32.  
    QUOTENAME(A.name) AS ixname ,
33.  
    B.avg_fragmentation_in_percent AS avg_fip
34.  
    FROM sys.indexes A
35.  
    INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL,
36.  
    NULL, 'LIMITED') AS B ON A.object_id = B.object_id
37.  
    AND A.index_id = B.index_id
38.  
    INNER JOIN SYS.OBJECTS OS ON A.object_id = OS.object_id
39.  
    INNER JOIN sys.schemas SS ON OS.schema_id = SS.schema_id
40.  
    WHERE B.avg_fragmentation_in_percent > 10
41.  
    AND B.page_count > 20
42.  
    AND A.index_id > 0
43.  
    AND A.IS_DISABLED <> 1
44.  
    --AND OS.name='book'
45.  
    ORDER BY avg_fip DESC ,
46.  
    tablename ,
47.  
    ixname
48.  
    OPEN IX_Cursor
49.  
    FETCH NEXT FROM IX_Cursor INTO @Objectid, @Indexid, @schemaname, @tablename,
50.  
    @ixname, @avg_fip
51.  
    WHILE @@FETCH_STATUS = 0
52.  
    BEGIN
53.  
    --碎片率>5%或<=30%，索引重组
54.  
    IF @avg_fip < 30.0
55.  
    SET @command = N'ALTER INDEX ' + @ixname + N' ON ' + @schemaname
56.  
    + N'.' + @tablename + N' REORGANIZE ';
57.  
     
58.  
    --碎片率>=30%，索引重建
59.  
    IF @avg_fip >= 30.0
60.  
    AND @Indexid = 1
61.  
    BEGIN
62.  
    IF EXISTS ( SELECT *
63.  
    FROM SYS.columns
64.  
    WHERE OBJECT_ID = @Objectid
65.  
    AND max_length IN ( -1, 16 ) )
66.  
    SET @command = N'ALTER INDEX ' + @ixname + N' ON '
67.  
    + @schemaname + N'.' + @tablename + N' REBUILD ';
68.  
    ELSE
69.  
    SET @command = N'ALTER INDEX ' + @ixname + N' ON '
70.  
    + @schemaname + N'.' + @tablename + N' REBUILD '
71.  
    + N' WITH (ONLINE = ON)';
72.  
    END
73.  
    IF @avg_fip >= 30.0
74.  
    AND @Indexid > 1
75.  
    BEGIN
76.  
    IF EXISTS ( SELECT *
77.  
    FROM SYS.index_columns IC
78.  
    INNER JOIN SYS.columns CS ON CS.OBJECT_ID = IC.OBJECT_ID
79.  
    AND CS.column_id = IC.column_id
80.  
    WHERE IC.OBJECT_ID = @Objectid
81.  
    AND IC.index_id = @Indexid
82.  
    AND CS.max_length IN ( -1, 16 ) )
83.  
    SET @command = N'ALTER INDEX ' + @ixname + N' ON '
84.  
    + @schemaname + N'.' + @tablename + N' REBUILD ';
85.  
    ELSE
86.  
    SET @command = N'ALTER INDEX ' + @ixname + N' ON '
87.  
    + @schemaname + N'.' + @tablename + N' REBUILD '
88.  
    + N' WITH (ONLINE = ON)';
89.  
    END
90.  
    --打印命令，单独执行
91.  
    PRINT @command
92.  
     
93.  
    --直接执行命令
94.  
    --EXEC(@command)
95.  
     
96.  
    FETCH NEXT FROM IX_Cursor INTO @Objectid, @Indexid, @schemaname,@tablename, @ixname, @avg_fip
97.  
    END
98.  
    CLOSE IX_Cursor
99.  
    DEALLOCATE IX_Cursor