文章編號: 315512 - 上次校閱: 2011年9月17日 - 版次: 3.0

SQL Server 中的 Autogrow 和 Autoshrink 設定考量

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。
全部展開 | 全部摺疊

結論

在許多 SQL Server 系統上，預設的 Autogrow 及 Autoshrink 設定不需要做任何調整即可使用。但是，有些環境根本不需要啟用這些設定，而有些環境則可能必須調整 Autogrow 及 Autoshrink 參數。本文為您提供一些背景資訊，以引導您針對實際環境選取各項設定。
回此頁最上方

其他相關資訊

如果您決定調整 Autogrow 和 Autoshrink 參數，有些事情必須列入考量。

我該如何配置設定？

  1. 您可以使用下列其中一項來配置 Autogrow 和 Autoshrink 設定：
    • ALTER DATABASE 陳述式 (不適用於 SQL Server 7.0)
    • SQL Server Management Studio 或 SQL Enterprise Manager
    • sp_dboption 預存程序 (在 SQL Server 2005 中已經被取代)
    注意 如果您使用的是 SQL Server 2005，請使用 SQL Server Management Studio 而不要用 SQL Enterprise Manager。如需有關如何在 SQL Server 2005 中配置這些設定的詳細資訊，請造訪下列 Microsoft Developer Network (MSDN) 網站：
    基本指南：將資料或記錄檔加入資料庫 (SQL Server Management Studio)
    http://msdn.microsoft.com/zh-tw/library/ms189253.aspx (http://msdn.microsoft.com/zh-tw/library/ms189253.aspx)
    資料庫屬性 (檔案頁面)
    http://msdn.microsoft.com/zh-tw/library/ms180254.aspx (http://msdn.microsoft.com/zh-tw/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。在上述範例中，您造成了不必要的負擔而且可能使得記錄檔過於分散，兩者均會對效能產生負面影響。
  • 資料或記錄檔的大小變更所造成的實體分散可能嚴重影響效能。無論您使用自動設定或經常以手動方式調整檔案使其成長和壓縮，都會影響效能。
  • 如果以小幅度遞增方式來成長資料庫，或是您先成長然後再壓縮資料庫，到最後可能整個磁碟都是分散的。在某些環境下，磁碟分散會造成效能方面的問題。小幅度遞增成長的情況也會降低系統效能。
  • 在 SQL Server 2005 或更新的版本中，您可以啟用檔案立即初始化。檔案立即初始化可以僅加速資料檔案裡的檔案配置。檔案立即初始化不適用於記錄檔。
  • 如果您的記錄檔不斷增加，可能會有過多虛擬記錄檔 (VLF)。這會造成資料庫啟動/線上作業、複寫、鏡像和變更資料擷取 (CDC) 的效能問題。此外，這也會造成資料修改的效能問題。

最佳作法

  • 就管理的生產系統來看，您一定要將 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 執行個體上被設定為自動壓縮的資料庫數目而定，在實際壓縮前，資料庫可能需要好幾個小時的時間才能達到臨界值。
回此頁最上方

?考

如需有關如何成長和壓縮資料庫與記錄檔的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
256650? (http://support.microsoft.com/kb/256650/ ) INF: 如何將 SQL Server 交易記錄檔壓縮
272318? (http://support.microsoft.com/kb/272318/ ) INF：Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
317375? (http://support.microsoft.com/kb/317375/ ) 執行 SQL Server 之電腦上的交易記錄檔意外地擴充或滿溢
247751? (http://support.microsoft.com/kb/247751/ ) BUG：Database Maintenance Plan Does Not Shrink the Database
305635? (http://support.microsoft.com/kb/305635/ ) 當資料庫自動擴充時會發生逾時問題
949523? (http://support.microsoft.com/kb/949523/ ) 在 SQL Server 2005 中，若是「初始大小」屬性的值和 Autogrowth 屬性的值較小，便會發生交易式複寫高度延遲的狀況。
如需有關資料庫檔案初始化的詳細資訊，請造訪下列 Microsoft Developer Network (MSDN) 網站：
資料庫檔案初始化 (http://msdn.microsoft.com/zh-tw/library/ms175935.aspx)
如需有關啟用檔案立即初始化的詳細資訊，請造訪下列 Microsoft 網站：
啟用檔案立即初始化的方法和原因 (http://blogs.msdn.com/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx)
如需有關交易記錄檔架構的詳細資訊，請造訪下列 MSDN 網站：
交易記錄檔的實體架構 (http://msdn.microsoft.com/zh-tw/library/ms179355.aspx)

《SQL Server 線上叢書》；主題：＜交易記錄檔實體架構＞(Transaction Log Physical Architecture)、＜壓縮交易記錄檔＞(Shrinking the Transaction Log)
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
回此頁最上方
關鍵字：?
kbsqldeveloper kbinfo KB315512
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。