伺服器記憶體設定選項

  • 發行項

使用兩個伺服器記憶體選項，最小伺服器記憶體和最大伺服器記憶體，以) MB 為單位重新設定記憶體 (，SQL Server記憶體管理員所管理的記憶體SQL Server實例所使用的 SQL Server SQL Server進程。

[最小伺服器記憶體] 的預設值是 0，而 [最大伺服器記憶體] 的預設值是 2147483647 MB。 根據預設，SQL Server 可以根據可用的系統資源，以動態方式變更其記憶體需求。

注意

將最大伺服器記憶體設定為最小值可能會嚴重降低SQL Server效能，甚至防止它啟動。 如果您在變更此選項之後無法啟動SQL Server，請使用-f啟動選項啟動它，並將最大伺服器記憶體重設為其先前的值。 如需詳細資訊，請參閱 Database Engine Service Startup Options。

動態使用記憶體時，SQL Server 會定期查詢系統，以判斷可用的記憶體數量。 維持這個可用記憶體數量可避免作業系統 (OS) 進行分頁。 如果可用記憶體較少，SQL Server 會將記憶體釋出至 OS。 如果可用記憶體較多，SQL Server 可能配置較多記憶體。 只有在工作負載需要更多記憶體時，SQL Server才會新增記憶體;待用伺服器不會增加其虛擬位址空間的大小。

請參閱範例 B，以取得傳回目前使用之記憶體的查詢。 max 伺服器記憶體會控制SQL Server記憶體配置，包括緩衝集區、編譯記憶體、所有快取、qe 記憶體授與、鎖定管理員記憶體，以及 clr 記憶體 (基本上是在sys.dm_os_memory_clerks) 中找到的任何記憶體 Clerk。 執行緒堆疊、記憶體堆積、SQL Server以外的連結伺服器提供者，以及非SQL Server DLL 配置的任何記憶體，都不會受到最大伺服器記憶體控制。

SQL Server使用記憶體通知 API QueryMemoryResourceNotification來判斷SQL Server記憶體管理員可以配置記憶體和釋放記憶體。

建議SQL Server動態使用記憶體;不過，您可以手動設定記憶體選項，並限制SQL Server可以存取的記憶體數量。 在設定SQL Server的記憶體數量之前，請先從作業系統和其他任何SQL Server (和其他系統使用的記憶體中減去總實體記憶體，以判斷適當的記憶體設定。如果電腦不是完全專用於SQL Server) 。 此差異是您可以指派給SQL Server的最大記憶體數量。

手動設定記憶體選項

您可以將伺服器選項 [最小伺服器記憶體] 與 [最大伺服器記憶體] 設定成跨某範圍的記憶體值。 這個方法適用于系統或資料庫管理員，將SQL Server實例與其他應用程式或相同主機上執行之SQL Server的其他實例一起設定。

注意

[最小伺服器記憶體] 和 [最大伺服器記憶體] 選項屬於進階選項。 如果您要使用 sp_configure 系統預存程序來變更這些設定，只有當 show advanced options 設為 1 時，才能變更它們。 這些設定會立即生效，不需要重新啟動伺服器。

使用min_server_memory保證SQL Server實例SQL Server記憶體管理員可用的記憶體數量下限。 SQL Server不會在啟動時立即配置在最小伺服器記憶體中指定的記憶體數量。 不過，記憶體使用量因為用戶端負載而達到此值之後，除非最小伺服器記憶體的值降低，否則SQL Server無法釋放記憶體。 例如，當數個SQL Server實例可以同時存在於相同的主機時，請設定 min_server_memory 參數，而不是針對實例保留記憶體而max_server_memory。 此外，在虛擬化環境中設定min_server_memory值是不可或缺的，以確保基礎主機的記憶體壓力不會嘗試從客體SQL Server虛擬機器上的緩衝集區解除配置記憶體， (VM) 超出可接受的效能需求。

注意

SQL Server不保證配置最小伺服器記憶體中指定的記憶體數量。 如果伺服器上的負載絕對不需要配置在最小伺服器記憶體中指定的記憶體數量，SQL Server將會以較少的記憶體執行。

您可使用 max_server_memory 確保 OS 不會遇到有害的記憶體壓力。 若要設定最大伺服器記憶體組態，請監視SQL Server進程的整體耗用量，以判斷記憶體需求。 若要為單一執行個體進行更準確的計算：

  • 從 OS 總記憶體中保留 1GB - 4GB 的記憶體給 OS 本身。
  • 然後，在最大伺服器記憶體控制項之外減去潛在SQL Server記憶體配置的對等專案，此配置是由堆疊大小1 * 計算的最大背景工作執行緒2 + -g 啟動參數3 (或 256MB，如果-g未設定) 則為 256MB。 餘數即為單一執行個體安裝的 max_server_memory 設定。

1 如需每個架構之執行緒堆疊大小的資訊，請參閱記憶體管理架構指南。

2 如需在目前主機中，為指定數量之親和 CPU 而計算出的預設背景工作執行緒數目相關資訊，請參閱如何設定最大背景工作執行緒伺服器設定選項的文件頁面。

3 如需 -g 啟動參數的資訊，請參閱資料庫引擎服務啟動選項的文件頁面。 只有 32 位 SQL Server (SQL Server 2005 到 SQL Server 2014) 。

OS 類型 最大伺服器記憶體允許的最小記憶體數量
32 位元 64 MB
64 位元 128 MB

如何使用 SQL Server Management Studio 設定記憶體選項

針對 SQL Server SQL Server 實例，請使用兩個伺服器記憶體選項最小伺服器記憶體和最大伺服器記憶體，以 MB 為單位重新設定記憶體 (數量) 。 根據預設，SQL Server 可以根據可用的系統資源，以動態方式變更其記憶體需求。

設定固定記憶體數量的程序

若要設定固定的記憶體數量：

  1. 在物件總管中，請以滑鼠右鍵按一下伺服器，然後選取 [屬性]。

  2. 按一下 [記憶體] 節點。

  3. 在 [伺服器記憶體選項] 底下，輸入 [最小伺服器記憶體] 和 [最大伺服器記憶體] 所要的數量。

    使用預設設定，允許SQL Server根據可用的系統資源動態變更其記憶體需求。 最小伺服器記憶體的預設設定為 0，而最大伺服器記憶體的預設設定為 2147483647 MB (MB) 。

網路應用程式的資料輸送量最大化

若要將系統記憶體用於SQL Server優化，您應該限制系統用於檔案快取的記憶體數量。 若要限制檔案系統快取，請確定沒有選取 [檔案共用的資料輸送量最大化]。 您可以選取 [記憶體使用最小化] 或 [平衡]，藉以指定最小的檔案系統快取。

若要檢查作業系統的現有設定

  1. 按一下 [開始]及 [控制台]、按兩下 [網路連線]，然後按兩下 [區域連線]。

  2. 在 [一般] 索引標籤上按一下 [內容]，選取 [File and Printer Sharing Microsoft Networks]，然後按一下 [內容]。

  3. 如果已選取 [網路應用程式的資料輸送量最大化]，請選擇其他任何選項，按一下 [確定]，然後關閉其餘的對話方塊。

鎖定記憶體分頁

此 Windows 原則決定哪些帳戶可以使用處理序將資料保留在實體記憶體中，以防止系統將資料傳送到磁碟上的虛擬記憶體。 將記憶體分頁到磁碟時，鎖定記憶體分頁可能會讓伺服器保持回應狀態。 SQL Server鎖定記憶體中的頁面選項設定為 ON，在 32 位和 64 位的 SQL Server 2014 Standard 版和更新版本中，當具有執行sqlservr.exe許可權的帳戶已獲授與 Windows「記憶體中鎖定頁面」 (LPIM) 使用者權限時。 在舊版 SQL Server 中，設定 32 位元 SQL Server 執行個體的 [鎖定分頁] 選項時，需要具有 sqlservr.exe 執行權限的帳戶具有 LPIM 使用者權限，而且 'awe_enabled' 組態選項設定為 ON。

若要停用 SQL Server的[鎖定記憶體中的頁面] 選項，請移除SQL Server啟動帳戶的 [鎖定記憶體中的頁面] 使用者權限。

停用鎖定記憶體中的分頁

若要停用記憶體中的鎖定頁面選項：

  1. 在 [開始] 功能表上，按一下 [執行] 。 在 [ 開啟 ] 方塊中，輸入 gpedit.msc 。

    [群組原則] 對話方塊隨即開啟。

  2. 在 [群組原則] 主控台上，依序展開 [電腦組態] 和 [Windows 設定]。

  3. 展開 [安全性設定]，然後展開 [本機原則]。

  4. 選取 [使用者權限指派] 資料夾。

    這些原則會顯示在詳細資料窗格中。

  5. 在窗格中按兩下 [鎖定記憶體中的分頁]。

  6. 在 [本機安全性原則設定] 對話方塊中，選取具有 sqlservr.exe 執行權限的帳戶，然後按一下 [移除]。

虛擬記憶體管理員

32 位元作業系統可存取 4 GB 的虛擬位址空間。 其中 2 GB 的虛擬記憶體專供各個處理序使用，而且可供應用程式使用。 另外 2 GB 是保留給作業系統使用的。 所有作業系統版本都包括可提供應用程式存取 3 GB 虛擬位址空間的參數，並將作業系統限制成 1 GB。 如需有關如何使用參數記憶體組態的詳細資訊，請參閱有關 4 GB 微調 (4GT) 的 Windows 文件集。 當 32 位SQL Server在 64 位作業系統上執行時，其使用者可用的虛擬位址空間會是完整的 4 GB。

此位址空間的認可區域是由「Windows 虛擬記憶體管理員 (VMM)」對應到可用的實體記憶體。

如需有關不同作業系統所支援之實體記憶體數量的詳細資訊，請參閱 Windows 文件集：＜Windows 版本的記憶體限制＞。

虛擬記憶體系統會允許超額認可實體記憶體，所以虛擬記憶體與實體記憶體的比率可超過 1:1。 因此，大型程式可以在各種實體記憶體組態的電腦上執行。 然而，使用的虛擬記憶體若遠大於所有處理序的平均工作集組合，可能會導致效能降低。

[最小伺服器記憶體] 和 [最大伺服器記憶體] 選項屬於進階選項。 如果您要使用 sp_configure 系統預存程序來變更這些設定，只有當 show advanced options 設為 1 時，才能變更它們。 這些設定會立即生效，不需要重新啟動伺服器。

執行 SQL Server 的多個執行個體

當您執行多個 Database Engine 實例時，有三種方法可用來管理記憶體：

  • 使用 [最大伺服器記憶體] 來控制記憶體使用量。 建立每一個執行個體的最大設定值，注意，扣除總額不大於機器的總實體記憶體。 您想要提供與執行個體的預期工作負載或資料庫大小成比例的記憶體給每一個執行個體。 此方式的優點是當新的處理序或執行個體啟動時，立刻有記憶體可用。 缺點是，如果您不是執行所有執行個體，則任何執行中執行個體都不能利用剩餘可用的記憶體。

  • 使用 [最小伺服器記憶體] 來控制記憶體使用量。 建立每一個執行個體的最小設定值，使這些最小值的總和小於機器的總實體記憶體 1-2 GB。 同樣地，您可以建立與該執行個體的預期負載成比例的最小值。 此方式的優點是，若沒有同時執行所有執行個體，則執行的執行個體可使用剩餘可用的記憶體。 當電腦上有另一個耗用大量記憶體的進程時，此方法也很有用，因為它可確保SQL Server至少會取得合理的記憶體數量。 其缺點是，當新的執行個體 (或任何其他處理序) 啟動時，執行中的執行個體可能需要花一些時間來釋出記憶體，尤其如果它們必須將已修改的頁面寫回其資料庫才能這麼做的話。

  • 不執行任何動作 (不建議)。 有呈現工作負載的前幾個執行個體傾向於配置所有記憶體。 閒置執行個體或稍後啟動的執行個體最後可能只剩少量的記憶體可用。 SQL Server 不會嘗試在執行個體之間平衡記憶體使用量。 不過，所有執行個體將回應 Windows 記憶體通知訊號，以便調整其記憶體使用量的大小。 Windows 不會平衡具有記憶體通知 API 的應用程式之間的記憶體。 它只提供對系統上記憶體可用性的全域回應。

您可以變更這些設定，而不必重新啟動執行個體，因此，您可以輕易體驗來尋找使用模式的最佳設定。

提供最大的記憶體量給 SQL Server

32 位元 64 位元
傳統記憶體 處理所有SQL Server版本中的虛擬位址空間限制：

2 GB

具有 /3gb 開機參數的 3 GB*

WOW64 上的 4 GB**		 處理所有SQL Server版本中的虛擬位址空間限制：

在 x64 架構上為 8 TB

*/3gb 是作業系統開機參數。 如需詳細資訊，請流覽 MSDN 程式庫。

**Windows 64) 上的 WOW64 (Windows 是 32 位SQL Server在 64 位作業系統上執行的模式。 如需詳細資訊，請流覽 MSDN 程式庫。

範例

範例 A

下列範例會將 max server memory 選項設定為 4 GB：

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 4096;  
GO  
RECONFIGURE;  
GO

範例 B：決定目前的記憶體配置

以下查詢會傳回目前配置之記憶體的相關資訊。

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

另請參閱

效能的監視與微調
RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server)
sp_configure (Transact-SQL)