IT工程師數位筆記本

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

下面我們討論一下SQL Server數據文件和日志文件空間申請的一些特點。

假如我們有一個數據庫，它有3個數據文件（假如它們屬于同一個文件組）和兩個日志文件

假設現在有個客戶端要插入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/O負載平衡的效果。

對于日志文件，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來講都是件浪費資源的事情。在負載比較重的系統里，對性能的影響尤其大。他們是盡量避免而不是鼓勵的操作。

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