共用方式為

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

  • 發行項

原始產品版本：SQL Server
原始 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） 的效能問題。 此外，這有時可能會導致數據修改的效能問題。

注意

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

如果您將資料庫成長為小型增量，或將資料庫成長，然後壓縮資料庫，則最後可能會有磁碟片段。 在某些情況下，磁碟分散可能會導致效能問題。 小型成長增量的案例也可以降低系統上的效能。

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

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

  • 針對受控生產系統，您必須考慮自動成長只是非預期成長的應變措施。 請勿使用自動成長，以日常方式管理您的數據和記錄成長。

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

  • 經過訓練的資料庫管理員必須仔細評估自動成長和自動成長：DBA：它們不得保持非受控。

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

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

  • 盡可能縮小交易大小，以防止非計劃性檔案成長。

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

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

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

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

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

參考資料