在 Windows 2008 R2 64 位版本上執行 SQL Server 2008R2 STD 64 位版本。

Windows 伺服器是一個 Hyper-V 虛擬機，其記憶體設置如下

當我執行以下查詢時；

SELECT
[physical_memory_in_bytes] AS [PhysMemBytes],
[physical_memory_in_use_kb] AS [PhysMemInUseKB],
[available_physical_memory_kb] AS [PhysMemAvailKB],
[locked_page_allocations_kb] AS [LPAllocKB],
[max_server_memory] AS [MaxSvrMem],
[min_server_memory] AS [MinSvrMem]
FROM
sys.dm_os_sys_info
CROSS JOIN
sys.dm_os_process_memory
CROSS JOIN
sys.dm_os_sys_memory
CROSS JOIN (
SELECT
[value_in_use] AS [max_server_memory]
FROM
sys.configurations
WHERE
[name] = 'max server memory (MB)') AS c
CROSS JOIN (
SELECT
[value_in_use] AS [min_server_memory]
FROM
sys.configurations
WHERE
[name] = 'min server memory (MB)') AS c2

它給出了下面的輸出；

任務管理器顯示 sqlserver.exe 程序正在使用相同的 1Gb

我不明白為什麼當虛擬機配置了動態記憶體並且能夠增長到 16Gb 時，SQL Server 沒有獲取 SQL Server 中設置的最小或最大記憶體？

編輯

下面的第一行錯誤日誌

LogDate ProcessInfo Text
2018-06-17 06:18:21.660 Server  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) 
Mar 26 2015 21:18:04 
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2018-06-17 06:18:21.680 Server  (c) Microsoft Corporation.
2018-06-17 06:18:21.680 Server  All rights reserved.
2018-06-17 06:18:21.680 Server  Server process ID is 1684.
2018-06-17 06:18:21.680 Server  System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2018-06-17 06:18:21.680 Server  Authentication mode is MIXED.
2018-06-17 06:18:21.680 Server  Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2018-06-17 06:18:21.690 Server  This instance of SQL Server last reported using a process ID of 1504 at 17/06/2018 6:16:11 AM (local) 16/06/2018 8:16:11 PM (UTC). This is an informational message only; no user action is required.
2018-06-17 06:18:21.690 Server  Registry startup parameters: 
-d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2018-06-17 06:18:21.700 Server  SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2018-06-17 06:18:21.700 Server  Detected 2 CPUs. This is an informational message; no user action is required.
2018-06-17 06:18:22.040 Server  Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2018-06-17 06:18:22.350 Server  Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2018-06-17 06:18:22.550 spid7s  Starting up database 'master'.
2018-06-17 06:18:22.710 spid7s  Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2018-06-17 06:18:22.910 spid7s  FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2018-06-17 06:18:23.000 spid7s  SQL Trace ID 1 was started by login "sa".
2018-06-17 06:18:23.000 spid7s  Starting up database 'mssqlsystemresource'.
2018-06-17 06:18:23.030 spid7s  The resource database build version is 10.50.4042. This is an informational message only. No user action is required.
2018-06-17 06:18:23.370 spid10s Starting up database 'model'.
2018-06-17 06:18:23.370 spid7s  Server name is 'DB01'. This is an informational message only. No user action is required.
2018-06-17 06:18:23.630 spid10s Clearing tempdb database.
2018-06-17 06:18:23.750 Server  A self-generated certificate was successfully loaded for encryption.
2018-06-17 06:18:23.750 Server  Server is listening on [ 'any' <ipv6> 1433].
2018-06-17 06:18:23.750 Server  Server is listening on [ 'any' <ipv4> 1433].
2018-06-17 06:18:23.760 Server  Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-06-17 06:18:23.760 Server  Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-06-17 06:18:23.760 Server  Server is listening on [ ::1 <ipv6> 1434].
2018-06-17 06:18:23.760 Server  Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-06-17 06:18:23.760 Server  Dedicated admin connection support was established for listening locally on port 1434.
2018-06-17 06:18:23.800 Server  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DB01 ] for the SQL Server service. 
2018-06-17 06:18:23.800 Server  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DB01:1433 ] for the SQL Server service. 
2018-06-17 06:18:23.800 Server  SQL Server is now ready for client connections. This is an informational message; no user action is required.
2018-06-17 06:18:23.990 spid12s A new instance of the full-text filter daemon host process has been successfully started.
2018-06-17 06:18:24.050 spid12s Starting up database 'msdb'.
2018-06-17 06:18:24.050 spid18s Starting up database 'SQL_D'.
   2018-06-17 06:18:24.050 spid16s Starting up database 'INVOICES'.
2018-06-17 06:18:24.050 spid14s Starting up database 'BAS'.
2018-06-17 06:18:24.050 spid17s Starting up database 'COLLEC'.
2018-06-17 06:18:24.250 spid10s Starting up database 'tempdb'.
2018-06-17 06:18:24.370 spid15s The Service Broker protocol transport is disabled or not configured.
2018-06-17 06:18:24.370 spid15s The Database Mirroring protocol transport is disabled or not configured.
2018-06-17 06:18:24.550 spid15s Service Broker manager has started.
2018-06-17 06:18:24.640 spid17s Recovery is writing a checkpoint in database 'COLLEC' (8). This is an informational message only. No user action is required.
2018-06-17 06:18:26.160 spid12s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
   2018-06-17 06:18:27.970 spid7s  Recovery is complete. This is an informational message only. No user action is required.
2018-06-17 06:18:28.060 Logon   Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection made using Windows authentication. [CLIENT: <local machine>]
2018-06-17 06:18:29.430 spid51  Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:29.480 spid51  Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2018-06-17 06:18:29.640 Logon   Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection made using Windows authentication. [CLIENT: <local machine>]
2018-06-17 06:18:29.930 spid51  Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:30.000 spid51  Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2018-06-17 06:18:30.440 spid51  Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:31.240 spid51  Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2018-06-17 06:22:26.230 spid70  Configuration option 'blocked process threshold (s)' changed from 30 to 30. Run the RECONFIGURE statement to install.
2018-06-17 06:22:26.230 spid70  FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2018-06-17 06:22:27.060 spid70  Attempting to load library 'odsole70.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:22:27.900 spid70  Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'. This is an informational message only; no user action is required.

您是否知道您只有 5.2 GB 分配給 Windows 機器。查看下面的輸出PhysicalMemBytes，您從 DMV sys.dm_os_sys_info 獲得的列是 Windows 機器上的總物理記憶體（以字節為單位），大約為 5.2 GB。

在這 5GB 中，Windows 作業系統和其他組件將使用 4GB，因此只有 1GB 留給 SQL Server。這就是 SQL Server 記憶體消耗不能超過 1 GB 的原因。將最大伺服器記憶體保持在 10GB 並將最小伺服器記憶體保持在 6GB 是沒有意義的，因為 SQL Server 的 RAM 不存在。我建議在系統上添加更多 RAM。

簡化您的情況

如果我將您在問題中提供的所有記憶體值都轉換為一個GB值，那麼我們會遇到以下情況。

輸入

SELECT 
-- Convert all values to GB
/*Bytes*/   5611511808  / 1024 / 1024 / 1024    AS PhysMemGB, 
/*kBytes*/  1116492     / 1024 / 1024           AS PhysMemInUseGB,
/*kBytes*/  1187560     / 1024 / 1024           AS PhysMemAvailGB,
/*MBytes*/  10240       / 1024                  AS MaxSvrMemGB,
/*MBytes*/  6144        / 1024                  AS MinSrvrMemGB

輸出

PhysMemGB | PhysMemInUseGB | PhysMemAvailGB | MaxSvrMemGB | MinSrvrMemGB
----------+----------------+----------------+-------------+--------------
5.22612.. | 1              | 1              | 10          | 6

這使我們對您的情況的認識略有不同。您的“虛擬”伺服器目前已分配 5.2 GB RAM，其中 1 GB 仍然可用。

---

您正在檢索的值的說明

physical_memory_in_bytes(PhysMemGB)

指定機器上的物理記憶體總量。不可為空。

參考： sys.dm_os_sys_info (Transact-SQL) (Microsoft Docs)

以上值是作業系統值。

physical_memory_in_use_kb(PhysMemInUseGB)

指示作業系統報告的程序工作集（以 KB 為單位），以及使用大頁面 API 跟踪的分配。不可為空。

參考： sys.dm_os_process_memory (Transact-SQL) (Microsoft Docs)

上述值是 SQL Server 實例值。

available_physical_memory_kb(PhyMemAvailGB)

可用物理記憶體的大小，以 KB 為單位。

參考： sys.dm_os_sys_memory (Transact-SQL) (Microsoft Docs)

以上是作業系統值。

max server memory(MB)(MaxSvrMemGB)

**使用 max_server_memory 來保證作業系統不會遇到有害的記憶體壓力。**要設置最大伺服器記憶體配置，請監視 SQL Server 程序的總體消耗以確定記憶體要求。為了更準確地對單個實例進行這些計算：

• 從總作業系統記憶體中，為作業系統本身預留 1GB-4GB。
• 然後減去最大伺服器記憶體控制之外的潛在 SQL Server 記憶體分配的等價物，它由堆棧大小 1 * 計算的最大工作執行緒 2 + -g 啟動參數 3 組成（如果未設置 -g，則預設為 256MB）。剩下的應該是單個實例設置的 max_server_memory 設置。

（強調我的）

參考： 伺服器記憶體伺服器配置選項（Microsoft Docs）

min server memory(MB)(MinSrvMemGB)

使用min_server_memory 保證 SQL Server 記憶體管理器可用於 SQL Server 實例的最小記憶體量。SQL Server 不會在啟動時立即分配 min server memory 中指定的記憶體量。 **但是，由於客戶端負載，記憶體使用量達到此值後，SQL Server 無法釋放記憶體，除非 min server memory 的值減小。**例如，當 SQL Server 的多個實例可以同時存在於同一主機中時，設置 min_server_memory 參數而不是 max_server_memory 以便為實例保留記憶體。此外，在虛擬化環境中設置 min_server_memory 值是必不可少的，以確保來自底層主機的記憶體壓力不會嘗試從來賓 SQL Server 虛擬機 (VM) 上的緩衝池中釋放超出可接受性能所需的記憶體。

（強調我的）

參考： 伺服器記憶體伺服器配置選項（Microsoft Docs）

---

分析你的情況

• 您的作業系統分配了 5.2 GB 的虛擬 RAM。
• SQL Server 目前使用 1 GB 的 RAM。
• 作業系統有 1 GB 的可用 RAM 分配給程序。
• 您的 SQL Server 實例尚未達到min server memory(MB)設置，如果作業系統需要更多記憶體，仍會向作業系統釋放記憶體。

如果您查看任務管理器中的圖形，您的伺服器是否總共消耗了 5.2 GB 的 RAM？嘗試查看流程詳細資訊併計算所有流程的總和。

假設

• max server memory(MB)由於“虛擬”作業系統記憶體已設置為動態並且 Windows Server（包含 SQL Server 實例）可能與同一台電腦上的其他“虛擬”伺服器競爭，您的 SQL Server 實例可能永遠不會達到該設置記憶體。
• 您的虛擬環境可能有 64 GB 的物理 RAM，但將所有虛擬機和 RAM 配置相加會導致過度承諾，例如 100%（例如，您已為虛擬伺服器分配了 128 GB RAM，但只有 64 GB物理記憶體）。

確定記憶體壓力

您可以查看以下計數器以確定您的單個 SQL Server 實例是否處於記憶體壓力之下：

• 大量檢查點頁面/秒
• 大量惰性寫入/秒
• 頁面讀取次數高/秒
• 緩衝區記憶體命中率低
• 低頁面預期壽命

您的 SQL Server 實例可能想要消耗更多 RAM，但由於“虛擬”RAM 的配置而無法這樣做。

參考： 如何辨識 Microsoft SQL Server 記憶體瓶頸(MSSQLTips.com)

---

解決方法

1. 嘗試將 Windows Server 的 RAM 的“虛擬”最小值配置為 8 GB。這是min server memory(MB)6 GB 的設置，另外還有 2 GB 或用於作業系統的 RAM。不要忘記將“虛擬”最大值設置為 18 GB（SQL Server max server memory(MB)+ 2 GB 用於作業系統）。
2. 嘗試將“虛擬”Windows 伺服器配置為使用靜態 RAM。將該值設置為 18 GB。

最後聲明

SQL Server 喜歡消耗大量記憶體…

……如果可以的話。您必須讓 SQL Server 實例能夠消耗盡可能多的 RAM。嘗試解決方法，看看是否可以增加 SQL Server 實例的記憶體消耗。

如果您需要更多證據，請閱讀我在此答案的其他資源部分提供的一些文章。

其他資源

• 如何證明您的 SQL Server 需要更多記憶體$$ Video $$(BrentOzar.com)
• Microsoft SQL Server 記憶體系統管理員指南(BrentOzar.com)
• 如何為 VMware 設置 SQL Server 最大記憶體(BrentOzar.com)
• 我的 SQL Server 實際需要多少記憶體？(SQLSkills.com)
• 使用 dm_os_ring_buffers 和 RING_BUFFER_RESOURCE_MONITOR (SQLSkills.com)辨識外部記憶體壓力

引用自：https://dba.stackexchange.com/questions/209884