第24/24周 資料庫維護（Database Maintenance）

哇哦，光陰似箭！歡迎回到性能調優教育訓練的最後一期。今天我會詳細講下SQL Server裡的資料庫維護，尤其是索引維護操作，還有如何進行資料庫維護。

索引維護

作為一個DBA，資料庫維護是你工作中非常重要的一部分，讓資料庫獲得最佳性能。一個SQL Server資料庫就像一輛車：它需要經常的檢查來保證運作沒有問題，副作用，且擁有最大可能的性能。SQL Server資料庫最重要的部分是它的索引及其對應的統計資訊對象。SQL Server運作一段時間後會有索引碎片，統計資訊必須更新，這樣的話查詢優化器才可以為你生成“足夠好”的執行計劃。

我們來詳細看下這2個東西。索引（聚集，非聚集）會産生碎片。索引意味着邏輯和實體排列順序不再一緻。如果你在傳統旋轉存儲上存儲你的資料庫，索引碎片在你的存儲子系統裡帶來随機I/O，與快速的循序I/O相比，它非常耗時。下圖展示了索引碎片。

微軟建議下列索引維護的最佳實踐：

• 碎片低于10%：不進行維護
• 碎片在10-30%：進行索引重組操作
• 碎片大于30%：進行索引重建操作

除此之外，索引重組和索引重建操作應該隻有在葉子層的頁數至少有10000頁才可以。如果你沒有達到這個門檻值，你不會看到随機I/O帶來的副作用消失。索引重建（Index Rebuild）操作會完全重建你的索引。它是一個在事務日志裡完全記錄的“大”事務。在99%進度的時候復原你的重建操作是個非常不好的想法，因為SQL Server需要把已做過的（有益的一面）全部重做一遍。因為你的復原會花費很長時間。因為索引重建會重新生成你的整個索引，你的統計資訊也會用全掃描更新。

另外，因為它是個大事務，如果這時你使用基于事務日志的HA技術就會有問題——例如資料庫鏡像或AlwaysOn可用組。SQL Server需要發送你的整個事務到鏡像（或複制的地方）。那就意味着你的網絡流量裡有大量的事務日志記錄。這對你的HA政策會有很大的副作用。

作為一個替代方法，索引重組（Index Reorganize）操作隻處理你的索引的葉子層，重組葉子層的邏輯排序。是以，索引重組不會為你更新統計資訊。索引重組隻包含多個小的系統事務。是以它不會在事務日志上帶來太大壓力，因為虛拟日志檔案可以為了更快的重用而被标記。索引重組操作對使用資料庫鏡像或AlwaysOn可用組都是有益的，因為使用索引重組操作你沒有給網絡流量裡倒入大量的事務。

如何進行維護操作

我經常被問到的問題是在SQL Server裡如何進行維護操作。我絕不，從未建議SQL Server提供的資料庫維護計劃（Database Maintenance Plans）。使用這些維護計劃你使用大錘往SQL Server裡砸：維護計劃會重組/重建你的索引而不管它們的實際碎片！

我已經看過運作整晚在索引上進行索引重建的維護計劃，盡管有些索引沒有碎片。使用剛才提到的資料庫維護計劃，你就不能依據索引碎片情況進行維護。重組和重建索引是要根據索引碎片等級來的。是以我從不推薦這些資料庫維護計劃給任何人！

我推薦使用Ola Hallengren提供的SQL Server維護解決方案。這個解決方案包含一些列的存儲過程，使用它們你可以繼續甯資料庫一緻性檢查，備份，還有索引維護操作。索引碎片等級就是你提供存儲過程的參數值。我們來看看下列IndexOptimize存儲過程的調用： 

1 EXEC [master].[dbo].[IndexOptimize]
 2    @Databases = 'AdventureWorks2012',
 3    @FragmentationLow = 'INDEX_REBUILD_OFFLINE',
 4    @FragmentationMedium = NULL,
 5    @FragmentationHigh = NULL,
 6    @FragmentationLevel1 = 10,
 7    @FragmentationLevel2 = 30,
 8    @PageCountLevel = 10000,
 9    @SortInTempdb = 'N',
10    @MaxDOP = NULL,
11    @FillFactor = NULL,
12    @PadIndex = NULL,
13    @LOBCompaction = 'Y',
14    @UpdateStatistics = NULL,
15    @OnlyModifiedStatistics = 'N',
16    @StatisticsSample = NULL,
17    @StatisticsResample = 'N',
18    @PartitionLevel = 'N',
19    @TimeLimit = NULL,
20    @Indexes = NULL
21    @Delay = NULL,
22    @LogToTable = 'Y',
23    @Execute = 'Y'
24 GO      

從代碼裡你可以看到，你可以指定不同的碎片等級作為參數（FragmentationLevel1, FragmentationLevel2）。最後對于這些碎片等級你指定你想要進行的索引操作（FragmentationLow, FragmentationMedium, FragmentationHigh）。在SQL Server裡它是非常簡單，卻是非常強大的進行索引維護操作的方法。試下——用了包你忘不了！！ 

小結

在今天的性能調優教育訓練裡，我們談了SQL Server裡的資料庫維護。你已經學到經常進行索引維護操作來擺脫索引碎片非常重要。因為索引碎片會在存儲子系統引入随機I/O，它會降低磁盤讀取操作。另外我想你展示通過使用Ola Hallengren提供的SQL Server維護解決方案進行非常高效的索引維護操作。

我希望和我一起的24個星期的性能調優教育訓練很有收獲，你已經學到了SQL Server裡一些新的東西，尤其是性能調優和故障排除。歡迎大家給我留言，把你的想法告訴我！！再一次感謝您關注性能調優教育訓練，感謝您和我一起度過這個24個星期的教育訓練！！ 謝謝您的一路陪伴！！！

注：此文章為

WoodyTu

學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！

若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！