1、確定當前數據庫中所有需要分析碎片的表。
2、確定所有表和索引的碎片。
3、考慮一下因素以確定需要進(jìn)行碎片整理的表和索引。
4、整理具有大量碎片的表和索引；
這里給出一個(gè)樣板SQL存儲過(guò)程，它執行以下操作；
CREATE PROCEDURE IndexDefragASDECLARE @DBName NVARCHAR(255) ,@TableName NVARCHAR(255) ,@SchemaName NVARCHAR(255) ,@IndexName NVARCHAR(255) ,@PctFrag DECIMALDECLARE @Defrag NVARCHAR(MAX)IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'#Frag')) DROP TABLE #FragCREATE TABLE #Frag(DBName NVARCHAR(255),TableName NVARCHAR(255),SchemaName NVARCHAR(255),IndexName NVARCHAR(255),AvgFragment DECIMAL)EXEC sp_msforeachdb 'INSERT INTO #Frag ( DBName, TableName, SchemaName, IndexName, AvgFragment) SELECT ''?'' AS DBName ,t.Name AS TableName ,sc.Name AS SchemaName ,i.name AS IndexName ,s.avg_fragmentation_in_percent FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL, NULL, ''Sampled'') AS s JOIN ?.sys.indexes i ON s.Object_Id = i.Object_id AND s.Index_id = i.Index_id JOIN ?.sys.tables t ON i.Object_id = t.Object_Id JOIN ?.sys.schemas sc ON t.schema_id = sc.SCHEMA_IDWHERE s.avg_fragmentation_in_percent > 20AND t.TYPE = ''U''AND s.page_count > 8ORDER BY TableName,IndexName'DECLARE cList CURSORFOR SELECT * FROM #FragOPEN cListFETCH NEXT FROM cListINTO @DBName, @TableName,@SchemaName,@IndexName,@PctFragWHILE @@FETCH_STATUS = 0BEGIN IF @PctFrag BETWEEN 20.0 AND 40.0 BEGIN SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE' EXEC sp_executesql @Defrag PRINT 'Reorganize index: ' + @DBName + '.' + @SchemaName + '.' + @TableName +'.' + @IndexName END ELSE IF @PctFrag > 40.0 BEGIN SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REBUILD' EXEC sp_executesql @Defrag PRINT 'Rebuild index: '+ @DBName + '.' + @SchemaName + '.' + @TableName +'.' + @IndexName END FETCH NEXT FROM cList INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFragENDCLOSE cListDEALLOCATE cListDROP TABLE #Frag
為了自動(dòng)化碎片分析過(guò)程，可以從SQL Server企業(yè)管理器中用以下簡(jiǎn)單的步驟創(chuàng )建一個(gè)SQL Server任務(wù)。
1、開(kāi)啟SQL Server代理;
2、打開(kāi)Management Studio，右鍵單擊，選擇新建=》任務(wù)；
3、在新建任務(wù)對話(huà)框的“常規”頁(yè)面中，輸入任務(wù)名稱(chēng)和其他細節：
4、在新建任務(wù)對話(huà)框的“步驟”頁(yè)面中，單擊“新建”并輸入用戶(hù)數據庫的SQL命令。
5、在新建任務(wù)步驟對話(huà)框“高級”頁(yè)面上，輸入報告碎片分析結果的輸出文件名稱(chēng)：
6、單擊“確定”按鈕，返回新建作業(yè)對話(huà)框；
7、在新建任務(wù)對話(huà)框“計劃”頁(yè)面，單擊“新建計劃”，并輸入運行SQL Server任務(wù)的合適計劃：
安排這個(gè)存儲過(guò)程在非高峰執行。為了確定數據庫的數據庫模式，記錄整天的SQL Server：SQL Statistics\Batch Requests/sec性能計數器，它將展示數據庫負載的波動(dòng)。
8、單擊“確定”按鈕，返回新建任務(wù)對話(huà)框。
9、輸入所有信息后，單擊新建任務(wù)對話(huà)框中的“確定”按鈕創(chuàng )建SQL Server任務(wù)。創(chuàng )建計劃在一個(gè)固定時(shí)間間隔(每周)運行sp_indexDefrag存儲過(guò)程的SQL Server任務(wù)。
10、確保SQL Server代理運行，這樣SQL Server任務(wù)將自動(dòng)根據設置的計劃運行。
這個(gè)SQL任務(wù)將在每個(gè)星期天的凌晨1點(diǎn)分析每個(gè)數據庫并且進(jìn)行碎片整理。
聯(lián)系客服
