優化數據庫之重建整理索引

 

--運行環境

/*

Microsoft SQL Server 2005 - 9.00.4035.00(Intel X86)   Nov 24 2008 13:01:59  

Copyright (c) 1988-2005 MicrosoftCorporation  Developer Edition on WindowsNT 5.2 (Build 3790: Service Pack 1)

*/

USE master

go

IF OBJECT_ID('SP_RefreshIndex','P') IS NOT NULL

    DROP PROCEDURESP_RefreshIndex

go

CREATE PROCEDURE SP_RefreshIndex

AS

IF db_id()<5

    RETURN

PRINT N'優化DB:'+DB_Name()

SET NOCOUNT ON;

DECLARE @command varchar(8000);

 

DECLARE partitions CURSOR FOR

SELECT

    CASE WHEN a.avg_fragmentation_in_percent<30.0THEN 'ALTER INDEX ' + d.Name + ' ON ' + c.NAME + '.' + b.NAME + ' REORGANIZE '

         ELSE 'ALTER INDEX [' + d.Name +'] ON [' + c.NAME + '].[' + b.NAME + '] REBUILD' END+

    CASE WHENpartitioncount>1 THEN' PARTITION=' + rtrim(a.partition_number)+';' ELSE ';' END

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS a

    INNER JOIN sys.objects AS b ON a.object_id=b.object_id

    INNER JOIN sys.schemas AS c ON c.schema_id=b.schema_id

    INNER JOIN sys.indexes AS d ON d.object_id=a.object_id AND d.index_id=a.index_id

    INNER JOIN (SELECT object_id,index_id,partitioncount=COUNT(*) FROM sys.partitions GROUP BY object_id,index_id) AS e ON e.object_id=a.object_id AND e.index_id=a.index_id

WHERE a.avg_fragmentation_in_percent> 10.0 AND a.index_id > 0 AND d.Name IS NOT NULL

 

OPEN partitions;

FETCH NEXT  FROMpartitions INTO @command;

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC (@command);

    FETCH NEXT FROM partitions INTO@command;

END

CLOSE partitions;

DEALLOCATE partitions;

 

GO

exec sp_ms_marksystemobject 'SP_RefreshIndex'--標記為系統存儲過程

go

 

EXEC sp_msforeachdb 'use [?] exec SP_RefreshIndex'--優化整個實例所有庫