C6优化数据库IndexDefrag

USE [C6]GO/****** 对象:  StoredProcedure [dbo].[IndexDefrag]    脚本日期: 12/19/2012 10:47:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*索引碎片清理*/ALTER PROCEDURE [dbo].[IndexDefrag]AS     DECLARE @sql NVARCHAR(MAX)    SET @sql = N''    DECLARE @temp TABLE ( sqlText VARCHAR(MAX) )    INSERT  INTO @temp            SELECT  'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].['                    + o.name + '] '                    + CASE WHEN ps.avg_fragmentation_in_percent > 15                           THEN 'REBUILD'                           ELSE 'REORGANIZE'                      END                    + CASE WHEN pc.partition_count > 1                           THEN ' PARTITION = '                                + CAST(ps.partition_number AS NVARCHAR(MAX))                           ELSE ''                      END            FROM    sys.indexes AS ix                    INNER JOIN sys.objects o ON o.object_id = ix.object_id                    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id                    INNER JOIN ( SELECT object_id ,                                        index_id ,                                        avg_fragmentation_in_percent ,--索引的逻辑碎片                                        partition_number ,--分区号（代表未分区）                                        page_count--页数                                 FROM   sys.dm_db_index_physical_stats(DB_ID(),                                                              NULL, NULL, NULL,         --获取统计信息的扫描级别（LIMITED、SAMPLED 或DETAILED），null即LIMITED，用DETAILED的话会有重复数据                                                              NULL)                               ) ps ON o.object_id = ps.object_id                                       AND ix.index_id = ps.index_id                    INNER JOIN ( SELECT object_id ,                                        index_id ,                                        COUNT(DISTINCT partition_number) AS partition_count                                 FROM   sys.partitions                                 GROUP BY object_id ,                                        index_id                               ) pc ON o.object_id = pc.object_id                                       AND ix.index_id = pc.index_id            WHERE   1 = 1                    AND ps.avg_fragmentation_in_percent > 10                    AND ix.name IS NOT NULL                    AND ix.index_id >= 1 --只关注聚集（=1）和非聚集（>1）                    AND page_count > 8 --只关注大小个区（页）以上的                    SELECT  @sql = @sql + N' ' + sqlText    FROM    @temp    EXECUTE sp_executesql @sql