八、IO最佳化（7）減少IO競爭

一、自動成長與自動收縮

1. 自動成長

　　預設情況下，資料庫檔案的大小可以根據需要自動增大。這可以使檔案的大小增大到磁碟變滿為止。

1）不允許自動成長

　　如果生產環境不允許自動成長操作過程中可能出現的應用程式逾時，則應為預期的工作負載預分配空間。

　　如果不允許自動成長，而資料庫空間已滿，則SQL Server會報錯“database full”並拒絕寫入新資料。

2）允許自動成長

　　一般建議資料庫應設定為自動成長，在出現意外情況時此設定將用於增加資料庫檔案的磁碟空間。也就是說，自動成長是一道保險，在“database full”的時候自動為資料庫增長可用空間。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/125054J01-0.png" title="自動成長.png" alt="154315238.png" />

2. 自動成長的增量設定

　　將檔案增量設定為合理的大小以避免資料庫檔案的增量過小。如果檔案的增量與寫入資料庫的資料量相比過小，則資料庫可能需要不斷擴大。這將影響效能。

　　建議為資料庫檔案設定 FILEGROWTH 增量時遵循以下通用原則。

1）當資料庫檔案大小為 0 至 100 MB 時，增量為 10 MB。

2）當資料庫檔案大小為 100 至 200 MB 時，增量為 20 MB 。

3）當資料庫檔案大小超過 200 MB 時，增量為 10% 。此增量可能必須基於資料庫的檔案所在的 I/O 子系統的速度調整此百分比。

3. 初始化

　　為了避免潛在的閂鎖逾時，我們建議將自動成長操作限制在大約兩分鐘之內。例如，如果 I/O 子系統以每秒 50 MB 的速度初始設定檔案，則無論資料庫檔案的大小如何，FILEGROWTH 增量都應設定為最大值 6 GB。

　　可以通過檔案立即初始化功能提升效能 http://technet.microsoft.com/zh-cn/library/ms175935(v=sql.105).aspx

4. 自動收縮

　　SQL Server 不會持續測試達到為自動收縮配置的閾值的資料庫。相反，它會尋找可用的資料庫並找出第一個配置為自動收縮的資料庫。它將檢查該資料庫，並在需要時收縮該資料庫。然後，它會等待幾分鐘，再檢查下一個配置為自動收縮的資料庫。

　　換句話說，SQL Server 不會同時檢查所有資料庫，也不會同時收縮所有資料庫。它將以迴圈方式處理各個資料庫，以使負載在時間上錯開。因此，從資料庫達到閾值到實際完成收縮可能需要幾個小時，具體取決於特定 SQL Server 執行個體上配置為自動收縮的資料庫數量。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1250543B2-1.png" title="自動收縮.png" alt="154259354.png" />

5. 建議

　　資料庫的自動成長會導致以下問題：

1）可控的增長

　　資料庫發生自動成長，通常是寫入了大量資料，而這種情形又往往會發生在業務高峰時段。因此，自動成長往往會在業務高峰期拖累資料庫的效能。如果該增長量很大，或者有其他因素導致時間延長，則您在其中開啟事務的查詢可能因逾時錯誤而失敗。

　　對於受管理的生產系統，您必須將自動成長僅視為偶然的意外增長。請勿使用自動成長管理每天的資料和日誌增長。

　　建議定期每周或每月）在非高峰期手動增加資料庫的空間。至於需要增加多少空間才合適，應當監測資料庫的大小，估算其增長的趨勢。

2）監視可用的磁碟空間

在使用自動成長設定時，增長後的資料庫大小不能超出為其定義檔案的磁碟機上的可用磁碟空間。因此，如果您依賴自動成長功能來決定您的資料庫的大小，仍必須另外檢查可用的硬碟空間。因此，應當監測磁碟空間的變化，提前預測磁碟空間的使用方式。

3）禁用自動收縮

　　一般不建議自動收縮。

　　如果您同時使用自動成長和自動收縮選項，則可能會帶來不必要的開銷。請確保觸發增長和收縮操作的閾值不會造成頻繁的大小調整。例如，您可能會運行這樣一個事務，它導致交易記錄在提交時增長 100 MB。在自動收縮啟動後的一段時間內，交易記錄收縮 100 MB。然後，您又運行相同的事務，並導致交易記錄再次增長 100 MB。在該樣本中，您造成了不必要的開銷，並且可能會產生記錄檔片段，兩者都可能對效能造成負面影響。

二、檔案分布

　　在SQL Server中，主要會有以下內容發生磁碟I/O競爭。建議將這些檔案分別放在不同的物理磁碟中。

1）使用者資料庫的資料檔案mdf和ndf檔案）

2）使用者資料庫的交易記錄檔ldf檔案）

3）tempdb資料庫的資料檔案和記錄檔

4）備份時產生的bak和trn檔案。

5）Windows系統的事件、分頁檔案。

三、磁碟子系統

1. 陣列卡

　　通常磁碟使用以下幾種RAIDRedundant Array of Independent Disk，獨立冗餘磁碟陣列）。

1）RAID 0：磁碟條帶化

　　即Data Stripping資料分條技術）。整個邏輯盤的資料是被分條stripped）分布在多個物理磁碟上，可以並行讀/寫，提供最快的速度，但沒有冗餘能力。要求至少兩個磁碟。

　　通過RAID 0可以獲得更大的單個邏輯盤的容量，且通過對多個磁碟的同時讀取獲得更高的存取速度。RAID 0首先考慮的是磁碟的速度和容量，忽略了安全，只要其中一個磁碟出了問題，那麼整個陣列的資料都會不保了。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/125054EZ-2.gif" style="float:none;" title="RAID0.gif" alt="104954483.gif" />

2）RAID 1：磁碟鏡像

　　即鏡像Mirror）方式，也就是資料的冗餘。在整個鏡像過程中，只有一半的磁碟容量是有效另一半磁碟容量用來存放同樣的資料）。同RAID 0相比，RAID 1首先考慮的是安全性，容量減半、速度不變。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/12505435I-3.gif" title="RAID1.gif" style="float:none;" alt="104954572.gif" />

3）RAID 5：帶奇偶的條帶化

　　RAID 5的工作方式是將各個磁碟產生的資料校正切成塊，分別存放到組成陣列的各個磁碟中去，這樣就緩解了校正資料存放時所產生的瓶頸問題，但是分割資料及控制存放都要付出速度上的代價。意味著RAID 5在資料寫入時會變慢。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1250542100-4.gif" title="RAID5.gif" style="float:none;" alt="104954156.gif" />

4）RAID 10：帶條帶化的鏡像

　　為了達到既高速又安全，出現了RAID 10或者叫RAID 0+1），可以把RAID 10簡單地理解成由多個磁碟組成的RAID 0陣列再進行鏡像。這種搭配，使得讀取和寫入的速度都非常快。但是容量減半。

2. 建議

1）tempdb

　　tempdb 資料庫效能要求非常高，但對資料安全性要求低。建議將 tempdb 資料庫放置在快速 I/O 子系統中。如果有許多直接連接的磁碟，請使用RAID 0 。

2）資料檔案mdf和ndf檔案）

　　這類檔案即要求效能，又要求安全性。建議RAID 10。如果硬體預算非常緊張，RAID 5也湊合吧。

3）交易記錄檔ldf檔案）

　　交易記錄檔的特性是連續的順序寫入，因此對效能要求不高，僅要求安全性。建議RAID 1。

本文結語：

　　盡量避開各種磁碟I/O的競爭，避免不必要的效能開銷。

本文出自 “我們一起追過的MSSQL” 部落格，轉載請與作者聯絡！