碎片批處理

 --推薦使用ACTION1，因為它支援線上操作，重建索引期間支援其他操作

 DECLARE @ACTION1 VARCHAR(MAX)

 DECLARE @ACTION2 VARCHAR(MAX)

 DECLARE pcurr CURSOR

 FOR

    SELECT  

            --objects.name ,

            --indexes.name AS indexid ,

            --avg_fragmentation_in_percent AS frag ,

            --partition_stats.row_count ,

            'ALTER INDEX ' + indexes.name + ' ON ' + objects.name

            + ' REBUILD  WITH (ONLINE = ON )' ACTION1 ,

            + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) ' ACTION2

    FROM    sys.dm_db_index_physical_stats(7, NULL, NULL, NULL, 'LIMITED') stats

            LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id

            LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id

                                             AND stats.object_id = indexes.object_id

            LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id

                                                              AND indexes.index_id = partition_stats.index_id

    WHERE   avg_fragmentation_in_percent > 4

            AND stats.index_id > 0

            AND objects.name NOT IN ( 'Student_Delete_Apply' )

    ORDER BY row_count DESC ,

            avg_fragmentation_in_percent DESC

 OPEN pcurr

 FETCH NEXT FROM pcurr INTO @ACTION1, @ACTION2

 WHILE ( @@FETCH_STATUS = 0 )

    BEGIN

        EXEC  (@ACTION1)

        PRINT ( @ACTION1 )

       -- EXEC  (@ACTION2)

    ----------------------------

        FETCH NEXT FROM pcurr INTO @ACTION1, @ACTION2

    END

 CLOSE pcurr

 DEALLOCATE pcurr