《SQL Server企業級平台管理實踐》讀書筆記——SQL Server如何設定自動成長和自動收縮項

標籤：style   color   ar   使用   sp   strong   檔案   資料   on   

SQL Server允許使用者佈建資料庫初始值和最大值，可以通過自動成長或者自動收縮排行配置。通過這些配置，我們可以防止資料庫空間問題而導致的應用程式修改失敗或者SQL Server磁碟空間耗盡的事情發生。一般來講，如果資料庫不是很忙，預設的設定為自動成長，這種方式能夠滿足大部分的需求。但是在大量並發的情況下，申請資料檔案和記錄檔增長本身是一件非常消耗系統資源和影響效能的工作。所以如果完全依賴SQL Server自動完成，可能會導致系統效能不夠穩定。一個管理得比較精細的系統，應該預先考慮到可能的空間使用需求，提前規劃並引導資料的流向。盡量避免空間用盡而使得SQL Server不得不自動成長的現象發生。同時也要確保每一次自動成長都能夠在可接受的時間內完成，及時滿足用戶端應用的需求。

下面我們討論一下SQL Server資料檔案和記錄檔空間申請的一些特點。

假如我們有一個資料庫，它有3個資料檔案（假如它們屬於同一個檔案組）和兩個記錄檔

檔案名稱	現有大小（MB）	現有空閑大小（MB）
MyDB_primary	2000	200
MyDB_secondary1	2000	100
MyDB_seconday2	2000	100
MyDB_log1	1000	500
MyDB_log2	1000	1000

假設現在有個用戶端要插入40MB的資料，20MB的日誌記錄，SQL Server會怎樣往這些檔案裡寫呢？

SQL Server對於資料和日誌有著不同的處理方法。

資料檔案

SQL Server會按照同一個檔案組裡所有的檔案現有空閑空間的大小，按這個比例把新的資料分布到所有有空間的資料檔案裡。如果某個檔案已經寫滿了，SQL Server就不再繼續往這個檔案裡寫，而是寫到其他有空間的檔案裡面。

比如上面的例子：因為3個檔案空閑是200:100:100,40MB的資料就按照20MB:10MB：10MB的比例寫入這3個檔案。

記錄檔

SQL Server對於日誌記錄是按照嚴格的順序寫入的。所以雖然這裡有兩個記錄檔，SQL Server還是在一個時間點唯寫其中一個。只有這個檔案寫滿了，SQL Server才會寫入另外一個。

上面的案例資料庫中，20MB的日誌記錄就都會寫入MyDB_log1。

有時候我們會加入多個資料檔案中，並把它們放在不同的磁碟上，以達到分散I/O負載的目的。從上面的處理方式我們可以看到。如果想達到這個目的，對於資料檔案，就必須保證同一個檔案組裡所有資料檔案都有基本一樣大小的空閑空間。（不是這些檔案一樣大就可以的。）如果某個硬碟上的資料檔案已經寫滿了，SQL Server就不會再往這個硬碟上寫了。如果空閑空間相對比較下，SQL Server寫的數目也會相對減少。

對於記錄檔，由於SQL Server在同一個時間只有一個檔案，所以加入多個記錄檔對效能基本不會有什麼協助。

如果檔案全部都能寫滿了，SQL Sever會怎麼處理呢？在這裡資料問價和記錄檔也會稍有不同。

對於資料檔案，SQL Server會選取其中一個檔案（可能是任意一個）做自動成長，而不是讓每一個資料檔案都做自動成長。所有後面的資料都寫入這個做了自動成長的檔案裡，直到這個檔案再次寫滿，SQL Server要做下一次自動成長為止。換句話說，依靠自動成長，只能看到一個檔案增長，很難享受到I/OServer Load Balancer的效果。

對於記錄檔，SQL Server自動成長當前的記錄檔，以保證日誌記錄的連續性。

當某個操作觸發了檔案自動成長時，SQL Server會讓那個操作等待。直到檔案自動成長結束了，原先的那個操作才能繼續進行。如果自動成長用了很長時間，原先的操作會等不及就逾時取消了（一般預設的閥值是15秒），不但這個操作會復原，檔案自動成長也會被取消。也就是說，這一次檔案沒有得到任何增長。最壞的情況是，在一個時間點，有很多操作需要申請新的空間，可是誰都沒有能夠等檔案自動成長完成就逾時。這時體現在終端使用者的資料，就是任何修改操作都不能被提交，全部逾時。直到一個串連能夠等待足夠久，讓SQL Server把這個自動成長做完。做完以後，其它本來逾時的操作又忽然能恢複正常。

為什麼一個自動成長可能會花費比較長的時間呢？這基本上都是由於每次需要增長的空間太大造成的。資料檔案是按照8KB為單位儲存的。所以做資料檔案自增長的時候，SQL Server也要對這些新增加的部分進行格式化。如果一次要增長很大的空間，比如，上GB或者幾十GB，這個格式化的過程就會很耗時。SQL Server2005以後的版本採用了延遲些技術。只要增長的新空間已經分配好。這次自動成長就算大功告成。SQL Server會用一個背景線程把剩餘的格式化做完。這樣就大大縮短了一次增長的時間。前端不容易遇到逾時失敗。

還有一種極端，就是每次自動成長值太小，SQL Server要做好幾次自動成長才能滿足操作需求。同樣的大小，一次一步到位話的時間比分好幾次增長要少許多。所以自動成長值也不能太小。

鑒於以上幾點，我們來總結一下：

1、要設定成固定大小增長，而不能按比例。這樣就能避免一次增長太多或者太少所帶來的不必要的麻煩。建議對比較小的資料庫，設定一次增長50MB到100MB。對於大的資料庫，設定一次增長100MB到200MB.

2、要定期檢測各個資料檔案的使用方式，盡量保證每個檔案剩餘的空間一樣大，或者期望的比例。

3、設定檔案最大值，以免SQL Server檔案自增長用盡磁碟空間，影響作業系統。

4、發生自增長後，要及時檢查新的資料檔案空間分配情況。避免SQL Server總是往個別檔案寫資料。

除了自動成長，資料庫還有一個自動收縮的功能。如果設定了這個功能，SQL Server每隔半個小時就會檢查檔案使用方式。如果空閑空間大於25%，SQL Server就會自動運行DBCC Shrinkfile的動作。所以這個功能能夠防止資料申請過多的空間而不使用。對於一個磁碟空間很緊張的系統，這個設定無疑是有協助的。但是從資料庫自身的健康和效能考慮，這個設定並不建議多用。這是因為：

1、SQL Server只有空間用盡的情況下才會自動成長。如果沒有找出自增長的原因，從而從根本上避免空間用盡。雖然能夠暫時用DBCC Shrinkfile功能收縮檔案大小，但是下次資料還是有可能長大。收縮資料庫只是一個治標不治本的方法。

2、資料檔案收縮給檔案帶來更多的片段

3、不管是資料庫收縮，還是增長，對於SQL Server來講都是件浪費資源的事情。在負載比較重的系統裡，對效能的影響尤其大。他們是盡量避免而不是鼓勵的操作。

總之一句話：在一個比較繁忙的資料庫，推薦的設定是開啟資料庫自動成長選項，以防資料庫空間用盡導致應用程式失敗，但是要嚴格避免自動成長的發生。同時，盡量不使用自動收縮功能。

 

《SQL Server企業級平台管理實踐》讀書筆記——SQL Server如何設定自動成長和自動收縮項