SQL Server 中自動成長和自動壓縮設定的考慮

  • 發行項

原始產品版本： SQL S
原始 KB 編號： 315512

摘要

在許多 SQL Server 系統上，都適用預設的自動成長和自動壓縮設定。 不過，在某些情況下，您可能必須調整自動成長和自動壓縮參數。 本文提供一些背景資訊，引導您何時為您的環境選取這些設定。

如果您決定調整自動成長和自動調整參數，請考慮下列事項。

如何?設定設定

  1. 您可以使用下列其中一項來設定或修改自動成長和自動調整設定：

    注意事項

    如需如何在資料庫檔案層級設定這些設定的詳細資訊，請參閱 將數據或記錄檔新增至資料庫。

    您也可以在建立資料庫時設定自動成長選項。

    若要檢視目前的設定，請執行下列 Transact-SQL 命令：

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. 請記住，自動成長設定是每個檔案。 因此，您必須針對每個資料庫至少設定兩個位置 (一個用於主要數據檔，另一個用於主要記錄檔) 。 如果您有多個數據和/或記錄檔，則必須在每個檔案上設定選項。 視您的環境而定，您可能會針對每個資料庫檔案使用不同的設定。

AUTO_SHRINK的考慮

AUTO_SHRINK是 SQL Server 中的資料庫選項。 當您為資料庫啟用此選項時，此資料庫就有資格透過背景工作進行壓縮。 此背景工作會評估所有符合壓縮和壓縮數據或記錄檔準則的資料庫。

您必須仔細評估針對 SQL Server 實例中的資料庫設定此選項。 頻繁的成長和縮減作業可能會導致各種效能問題。

  • 如果多個資料庫經常進行壓縮和成長作業，則這很容易導致文件系統層級分散。 這可能會對效能造成嚴重影響。 無論您是使用自動設定，還是要經常手動增加和壓縮檔案，都是如此。

  • 成功壓縮數據或記錄檔之後 AUTO_SHRINK ，如果需要空間且檔案需要增加，後續的 DML 或 DDL 作業可能會大幅降低速度。

  • AUTO_SHRINK當有許多資料庫需要壓縮時，背景工作可能會佔用資源。

  • 背景 AUTO_SHRINK 工作需要取得鎖定，以及其他可能與其他一般應用程式活動衝突的同步處理。

請考慮將資料庫設定為必要的大小，並預先擴充資料庫。 如果您認為應用程式使用模式會再次需要資料庫檔案，請保留資料庫檔案中未使用的空間。 這可以防止資料庫檔案經常壓縮和成長。

AUTOGROW 的考慮

  • 如果您執行的交易需要的記錄空間比可用的還多，而且您已開啟該資料庫事務歷史記錄的自動成長選項，則交易完成所花費的時間將會包含事務歷史記錄檔成長所花費的時間， 如果成長增量很大，或有一些其他因素導致它花費很長的時間，您開啟交易的查詢可能會因為逾時錯誤而失敗。 相同的問題可能是因為資料庫的數據部分自動成長所造成。

  • 如果您執行需要記錄檔成長的大型交易，其他需要寫入事務歷史記錄的交易也必須等到成長作業完成為止。

  • 如果您的記錄檔有許多檔案成長，則 VLF) (可能會有過多的虛擬記錄檔。 這可能會導致資料庫啟動/在線作業、復寫、鏡像和異動數據擷取 (CDC) 的效能問題。 此外，這有時可能會造成數據修改的效能問題。

注意事項

如果您結合自動成長和自動壓縮選項，可能會產生不必要的額外負荷。 請確定觸發成長和縮減作業的臨界值不會導致頻繁的向上和縮小大小變更。 例如，您可能會執行交易，導致事務歷史記錄在認可時增加 100 MB。 之後的某個時間，自動壓縮會啟動，並將事務歷史記錄壓縮 100 MB。 然後，您會執行相同的交易，這會導致事務歷史記錄再次成長 100 MB。 在該範例中，您會建立不必要的額外負荷，並可能會建立記錄檔的片段，而這兩者都可能會對效能造成負面影響。

如果您以少量遞增的方式擴增資料庫，或是將資料庫擴大再壓縮，則最後可能會出現磁碟片段。 在某些情況下，磁碟片段可能會導致效能問題。 小型成長增量的案例也可以降低系統上的效能。

在 SQL Server 中，您可以啟用立即檔案初始化。 立即檔案初始化只會加速數據檔案的檔案配置。 立即檔案初始化不適用於記錄檔。 如需詳細資訊，請參閱 資料庫立即檔案初始化。

自動成長和自動壓縮的最佳做法

  • 對於受控生產系統，您必須將自動成長視為非預期成長的應變專案。 請勿透過自動成長來管理您的數據和記錄的日常成長。

  • 您可以使用警示或監視程序來監視檔案大小，並主動增加檔案。 這可協助您避免分散，並允許您將這些維護活動移轉至非尖峰時段。

  • 已定型的資料庫管理員必須仔細評估自動壓縮和自動成長 (DBA) ;它們不能保持 Unmanaged。

  • 您的自動成長增量必須夠大，以避免上一節所列的效能降低。 要在組態設定中使用的確切值，以及百分比成長與特定MB大小成長之間的選擇，取決於環境中的許多因素。 您可以用於測試的一般經驗法則是將自動成長設定為檔案大小的 1-8。

  • 開啟 \<MAXSIZE> 每個檔案的設定，以防止任何一個檔案成長到它用盡所有可用磁碟空間的點。

  • 盡可能讓交易的大小保持較小，以防止非計劃性檔案成長。

如果自動控制大小設定，為何必須擔心磁碟空間

  • 自動成長設定無法將資料庫大小成長到超過定義檔案之磁碟驅動器上可用磁碟空間的限制。 因此，如果您依賴自動成長功能來調整資料庫的大小，您仍然必須獨立檢查可用的硬碟空間。 自動成長設定也受限於您為每個檔案選取的 MAXSIZE 參數。 若要減少空間不足的可能性，您可以監視 效能監視器 計數器 SQL Server：資料庫對象：數據檔 (的) 大小 (KB) ，並在資料庫達到特定大小時設定警示。

  • 非計劃性的數據或記錄檔成長可能會佔用其他應用程式預期可用的空間，並可能導致其他應用程式遇到問題。

  • 事務歷史記錄的成長增量必須夠大，才能符合交易單位的需求。 即使開啟自動成長，您也可以收到一則訊息，指出事務歷史記錄已滿，如果其成長速度不夠快，無法滿足查詢的需求。

  • SQL Server 不會持續測試已達到自動壓縮設定閾值的資料庫。 相反地，它會查看可用的資料庫，並尋找第一個設定為自動壓縮的資料庫。 它會檢查該資料庫，並視需要壓縮該資料庫。 然後，它會等候幾分鐘，再檢查下一個設定為自動壓縮的資料庫。 換句話說，SQL Server 不會一次檢查所有資料庫，也不會一次全部壓縮。 它會以迴圈配置資源方式處理資料庫，以錯開一段時間的負載。 因此，根據您已設定為在特定 SQL Server 實例上自動壓縮的資料庫數目，從資料庫達到臨界值到實際縮小可能需要數小時的時間。

參考資料