七、記憶體優化（2）動态記憶體配置設定

一、動态記憶體配置設定

　　SQL Server 将根據需要動态擷取并釋放記憶體。雖然該選項仍然存在且在有些環境下需要用到，但通常情況下管理者不必指定為 SQL Server 配置設定多少記憶體。

　　所有資料庫軟體的主要設計目标之一是盡量減少磁盤 I/O，因為磁盤的讀取和寫入操作占用大量資源。SQL Server 在記憶體中生成緩沖池，以儲存從資料庫讀取的頁。SQL Server 中的大量代碼專門用于盡量減少磁盤與緩沖池之間的實體讀寫次數。SQL Server 設法在以下兩個目标之間達到平衡：

（1）防止緩沖池變得過大，進而導緻整個系統記憶體不足。

（2）盡量增加緩沖池達的大小，以便盡量減少資料庫檔案的實體 I/O。

二、監視計數器以确認記憶體不足

　　定期監視 SQL Server 的執行個體以确認記憶體使用量在正常範圍内。如果要監視記憶體不足的情況，請使用下列對象計數器：

（1）Memory: Available Bytes

　　此計數器訓示目前有多少記憶體（以位元組為機關）可供程序使用。如果它的值低，則表示計算機總記憶體不足或應用程式沒有釋放記憶體。

（2）Memory: Pages/sec

　　此計數器訓示由于頁錯誤而從磁盤取回的頁數，或由于頁錯誤而寫入磁盤以釋放工作集空間的頁數。如果它的比率高，則表示分頁過多，需要同時監視 Memory: Page Faults/sec 計數器以確定磁盤活動不是由分頁導緻。

　　分頁率偏低（以及由此産生的頁錯誤）是正常的，即使計算機有大量的可用記憶體。Windows 虛拟記憶體管理器 (VMM) 在剪裁 SQL Server 和其它程序的工作集大小時會收走這些程序的頁。此 VMM 活動會導緻頁錯誤。若要确定分頁過多是由 SQL Server 還是由其它程序導緻，請監視用于 SQL Server 程序執行個體的 Process: Page Faults/sec。

三、伺服器記憶體選項

1. 記憶體選項

　　預設情況下，SQL Server 将根據可用系統資源動态改變其記憶體要求。如果 SQL Server 需要更多記憶體，它會查詢作業系統以确定是否有可用的空閑實體記憶體，然後使用可用記憶體。如果 SQL Server 目前不需要配置設定給它的記憶體，它會将記憶體釋放給作業系統。

　　也可以覆寫此選項通過 min server memory 和 max server memory 伺服器配置選項來動态使用記憶體。

2. 最小伺服器記憶體（min server memory）

　　使用 min server memory 確定 SQL Server 執行個體的緩沖池可用的最小記憶體量。SQL Server 在啟動時将不立即配置設定在 min server memory 中指定的記憶體量。當記憶體使用量達到該值後，SQL Server 不能從已配置設定的緩沖池中釋放記憶體，除非降低 min server memory 的值。

　　min server memory 的預設設定是 0。

3. 最大伺服器記憶體（max server memory）

　　預設情況下，SQL Server 的記憶體要求會根據可用系統資源的情況動态地變化。配置 max server memory 選項可另外留出一些可用記憶體，以滿足Windows和其它應用程式的需要。當記憶體使用量達到 max server memory 中指定的值，SQL Server 無法超過此記憶體使用量，除非增加 max server memory 的值。若要減小 max server memory，可能需要重新啟動 SQL Server 以釋放記憶體。

　　max server memory 的預設設定是 2147483647，最小可設定為16MB。

　　注意：如果将 max server memory 設定為最小值，則可能會嚴重降低 SQL Server 的性能，甚至使其無法啟動。如果在更改此選項之後無法啟動 SQL Server，則請使用 –f 啟動選項進行啟動，并将 max server memory 重置為以前的值。

4. 建議

　　建議允許 SQL Server 動态使用記憶體；但可以手動設定記憶體選項并限制 SQL Server 可以通路的記憶體量。在設定 SQL Server 的記憶體量之前，請通過從總實體記憶體中減去作業系統和任何其他 SQL Server 執行個體所需的記憶體（如果計算機并非完全由 SQL Server 專用，則還要減去其他系統使用的記憶體量）來确定适當的記憶體設定。這個內插補點就是可以配置設定給 SQL Server 使用的最大記憶體量。

　　在減小 max server memory 值之前，請在加載時使用性能螢幕來檢查 SQLServer:Buffer Manager 性能對象，并注意 Stolen pages 和 Reserved pages 計數器的目前值。這些計數器将記憶體報告為 8K 頁的數目。應将 max server memory 設定為大于這兩個值的和，以免出現記憶體不足錯誤。對于合理的最低 max server memory 設定（以 MB 為機關），近似值是 ([Stolen pages] + [Reserved pages])/ 100。

5. 多執行個體時的記憶體配置

　　當運作多個資料庫引擎執行個體時，可以使用三種方法來管理記憶體：

（1）使用 max server memory 控制記憶體使用量

　　為每個執行個體建立最大設定，注意總的允許設定值不能大于計算機上的實體總記憶體。可能需要為每個執行個體提供與預期的工作負荷或資料庫大小成正比的記憶體。這種方法的優勢展現在：當啟動新的程序或執行個體時，可以立即為這些程序或執行個體提供可用記憶體。這種方法的缺點為：如果沒有運作所有執行個體，則所有運作中的執行個體都無法使用剩餘的可用記憶體。

（2）使用 min server memory 控制記憶體使用量

　　為每個執行個體建立最小設定，以使這些最小值的和比計算機上總的實體記憶體小 1-2 GB。此外，可能需要建立與該執行個體的預期負荷成正比的最小值。這種方法的優勢展現在：如果沒有同時運作所有執行個體，則運作中的執行個體可以使用剩餘的可用記憶體。當計算機上存在其他占用大量記憶體的程序時，這種方法也十分有用，因為它可確定 SQL Server 至少獲得合理的記憶體量。這種方法的缺點為：當啟動新的程序（或任何其他程序）時，它可能會花費一些時間運作執行個體以釋放記憶體，如果執行個體必須将修改後的頁寫回到資料庫中來釋放記憶體，則花費的時間可能會更長。還可能需要顯著增大分頁檔案的大小。

（3）不執行任何操作（不推薦）

　　帶有工作負荷的第一個執行個體通常配置設定所有的記憶體。稍後啟動的空閑執行個體最終可能會隻使用最少的可用記憶體量運作。SQL Server 不會嘗試均衡配置設定各個執行個體的記憶體使用量。但是，所有執行個體都将響應 Windows 記憶體通知信号以調整其緩沖池的大小。Windows 不會使用記憶體通知 API 來平衡各個應用程式使用的記憶體。它隻提供有關系統記憶體可用性的全局回報。

　　您可以在不重新啟動執行個體的情況下更改這些設定，以便可以輕松地進行嘗試以找到适用于您使用模式的最佳設定。

本文結語：

　　預設由資料庫引擎動态配置設定記憶體，也可以通過伺服器記憶體選項配置最小和最大記憶體。