INF：SQL Server 中的 Autogrow 及 Autoshrink 設定考量

我該如何配置設定？

1. 您可以使用下列方法來配置 Autogrow 及 Autoshrink 設定：
   • ALTER DATABASE 陳述式 (僅適用 SQL Server 2000)。
   • sp_dboption 預存程序。
   • SQL Enterprise Manager。
   注意 如果您使用的是 SQL Server 2005，請改用 SQL Server Management Studio 來代替 SQL Enterprise Manager。如需有關如何在 SQL Server 2005 中配置這些設定的詳細資訊，請造訪下列 Microsoft Developer Network (MSDN) 網站：
   如何：將資料或記錄檔加入資料庫 (SQL Server Management Studio) (英文)
   http://msdn2.microsoft.com/en-us/library/ms189253.aspx (http://msdn2.microsoft.com/en-us/library/ms189253.aspx)
   資料庫屬性 (檔案頁面) (英文)
   http://msdn2.microsoft.com/en-us/library/ms180254.aspx (http://msdn2.microsoft.com/en-us/library/ms180254.aspx)
   您也可以在建立資料庫時設定 Autogrow 選項。
   
   您可以透過 SQL Enterprise Manager (SEM) 中的資料庫屬性來檢視目前的設定，或者執行下列 Transact-SQL 命令：

   sp_helpdb [ [ @dbname= ] 'name' ]

2. 請記住，每個檔案各有其 Autogrow 設定值。因此，針對每個資料庫，您至少必須在兩處進行設定 (一處為主要資料檔，另一處為主要記錄檔)。如果資料檔及/或記錄檔不只一個，您必須設定每個檔案的選項。視您的環境而定，到最後可能每個資料庫檔案的設定都不相同。

對效能有何連帶影響？

• 如果所執行的交易需要的記錄空間大於可用空間，而且您已啟用該資料庫之交易記錄檔的 Autogrow 選項，那麼完成交易所花費的時間即包含了交易記錄檔依設定值而成長的時間。如果成長遞增相當大或有其他因素導致費時良久，開啟交易所在的查詢便可能由於逾時錯誤而失敗。資料庫的資料部分若自動成長也可能會發生這類問題。如果要變更 Autogrow 設定，請參閱《SQL Server 線上叢書》中的＜ALTER DATABASE＞主題。
• 如果您執行需要記錄檔成長的大型交易，那麼需要寫入交易記錄檔的其他交易就必須也等到成長作業完成後才能開始寫入。
• 如果您一併使用 Autogrow 及 Autoshrink 選項，可能就會造成不必要的負擔。請確定觸發成長和壓縮作業的臨界值不會導致大小經常上下變動。例如，在獲得認可前，您所執行的交易可能導致交易記錄檔成長 100 MB；而在某個時間點，Autoshrink 開始發生作用，又將交易記錄檔壓縮了 100 MB。接著您再度執行該交易，然後導致交易記錄檔再次成長 100 MB。在上述範例中，您造成了不必要的負擔而且可能使得記錄檔過於分散，兩者均會對效能產生負面影響。
• 資料或記錄檔的大小變更所造成的實體分散可能嚴重影響效能。無論您使用自動設定或經常以手動方式調整檔案使其成長和壓縮，都會影響效能。
• 如果以小幅度遞增方式來成長資料庫，或是您先成長然後再壓縮資料庫，到最後可能整個磁碟都是分散的。在某些環境下，磁碟分散會造成效能方面的問題。小幅度遞增成長的情況也會降低系統效能。

最佳實務做法

• 就管理的生產系統來看，您一定要將 Autogrow 視為僅僅只是非預期成長的偶發事件。請勿每天使用 Autogrow 來管理資料和記錄檔成長。
• 您可以使用警示或監控程式，積極監控檔案大小及調整檔案成長。這樣有助於防止磁碟分散，而您亦可等到離峰時段再來執行這些維護工作。
• AutoShrink 及 Autogrow 必須由受過訓練的資料庫管理員 (DBA) 仔細評估並妥善管理，不可置之不管。
• Autogrow 增量必須夠大，以免遭遇前一節所列的效能負面影響。組態設定中應使用的確切值，以及應使用成長百分比或特定的 MB 大小成長量，係取決於環境中的許多因素。您可以依照一般的首要規則先行測試，將 Autogrow 設定為檔案大小的八分之一左右。
• 啟用每個檔案的 <MAXSIZE> 設定，以避免任一檔案不斷成長以致用完了所有的可用磁碟空間。
• 交易的大小盡量愈小愈好，以免檔案成長超出預期。

大小設定既已自動控制，我為何還需要煩惱磁碟空間？

• 在成長資料庫大小方面，Autogrow 設定不會受限於檔案定義所在之磁碟機的可用磁碟空間。因此，即使您依賴 Autogrow 功能來決定資料庫大小，仍然必須自行檢查可用的硬碟空間。Autogrow 設定亦受制於每個檔案所選的 MAXSIZE 參數。為了降低磁碟空間不足的可能性，您可以監控效能監視器計數器 [SQL Server:Databases Object: Data File(s) Size (KB)]，並設定資料庫在達到特定大小時發出警示。
• 超出預期的資料和記錄檔成長可能會佔用其他應用程式所需的可用空間，進而導致這些應用程式發生問題。
• 交易記錄檔的成長遞增必須夠大，才能滿足交易單位的需求量。即使啟用了 Autogrow，如果交易記錄檔成長太慢，因而無法滿足查詢的需要，還是會出現交易記錄檔已滿的訊息。
• SQL Server 並不會持續測試哪些資料庫已達 Autoshrink 的設定臨界值。相反地，它會探查可用的資料庫並尋找最先設定為自動壓縮的資料庫。找到後則檢查該資料庫並視需要予以壓縮。然後隔了幾分鐘之後，它會開始檢查下一個被設定為 Autoshrink 的資料庫。換言之，SQL Server 並非同時檢查所有資料庫然後一次全部壓縮。它是採遞迴方式逐一檢查資料庫，每隔一段時間交錯地卸除負載。因此，視特定 SQL Server 執行個體上被設定為自動壓縮的資料庫數目而定，在實際壓縮前，資料庫可能需要好幾個小時的時間才能達到臨界值。

回此頁最上方