SQL Server 索引的自動維護 <第十三篇>

標籤：操作   企業   foreach   insert   名稱   objects   fetch   片段化   ble   

　在有大量事務的資料庫中，表和索引隨著時間的推移而片段化。因此，為了增進效能，應該定期檢查表和索引的片段，並對具有大量片段的進行整理。

　　1、確定當前資料庫中所有需要分析片段的表。

　　2、確定所有表和索引的片段。

　　3、考慮一下因素以確定需要進行磁碟重組的表和索引。

• 　　高的片段水平-avg_fragmentation_in_percent大於20%；
• 　　不是非常小的表或索引-也就是page_count大於8的；

　　4、整理具有大量片段的表和索引；

　　這裡給出一個樣板SQL預存程序，它執行以下操作；

• 　　遍曆系統上的所有資料庫並確認符合片段條件的每個資料庫中表上的索引，並將它們儲存到一個暫存資料表中；
• 　　根據片段水平，重新整理片段較少的索引並重建片段很多的索引。

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

　　為了自動化片段分析過程，可以從SQL Server企業管理器中用以下簡單的步驟建立一個SQL Server任務。

　　1、開啟SQL Server代理;

　　

　　2、開啟Management Studio，按右鍵，選擇建立=》任務；

　　

　　3、在建立任務對話方塊的“常規”頁面中，輸入任務名稱和其他細節：

　　

　　4、在建立任務對話方塊的“步驟”頁面中，單擊“建立”並輸入使用者資料庫的SQL命令。

　　

　　5、在建立任務步驟對話方塊“進階”頁面上，輸入報告片段分析結果的輸出檔案名稱：

　　

　　6、單擊“確定”按鈕，返回新增作業對話方塊；

　　7、在建立任務對話方塊“計劃”頁面，單擊“建立計劃”，並輸入運行SQL Server任務的合適計劃：

　　

　　安排這個預存程序在非高峰執行。為了確定資料庫的資料庫模式，記錄整天的SQL Server：SQL Statistics\Batch Requests/sec效能計數器，它將展示資料庫負載的波動。

　　8、單擊“確定”按鈕，返回建立任務對話方塊。

　　9、輸入所有資訊後，單擊建立任務對話方塊中的“確定”按鈕建立SQL Server任務。建立計劃在一個固定時間間隔(每周)運行sp_indexDefrag預存程序的SQL Server任務。

　　10、確保SQL Server代理運行，這樣SQL Server任務將自動根據設定的計劃運行。

　　這個SQL任務將在每個星期天的淩晨1點分析每個資料庫並且進行磁碟重組。

SQL Server 索引的自動維護 <第十三篇>