C6优化数据库IndexDefrag

USE [C6]
GO
/****** 对象:  StoredProcedure [dbo].[IndexDefrag]    脚本日期: 12/19/2012 10:47:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	索引碎片清理
*/
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