有人可以解释我的 Windows/SQL Server 内存使用情况吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/711445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can someone explain my Windows/SQL Server memory usage
提问by Mr. Flibble
I'm running Windows Server 2003 x64 with 8GB RAM and SQL Server 2005 64 bit. I have SQL set to use loads of memory, but the SQL process only ever takes under 100 MB RAM. Is this normal? It is accessing data with indexes many GBs in size.
我正在运行带有 8GB RAM 和 SQL Server 2005 64 位的 Windows Server 2003 x64。我将 SQL 设置为使用大量内存，但 SQL 进程只占用 100 MB 以下的 RAM。这是正常的吗？它正在访问具有许多 GB 大小的索引的数据。
Moreover, no process is taking over 100MB, yet there is only a minimal (<100MB) amount ofree memory.
此外，没有一个进程占用超过 100MB，但只有极少 (<100MB) 的ree 内存量。
Could some kind person explain to me where the memory is being used and if the SQL memory usage is normal?
有人可以向我解释一下内存在哪里使用以及SQL内存使用情况是否正常？
Memory usage: http://img129.imageshack.us/img129/8624/02042009210608.png
内存使用：http: //img129.imageshack.us/img129/8624/02042009210608.png
SQL Server memory settings:
SQL Server 内存设置：
http://img10.imageshack.us/img10/8964/02042009210845.png
http://img10.imageshack.us/img10/8964/02042009210845.png
Memory details:
内存详情：
回答by Daniel Brückner
The value displayed is not the actual amount of used memory. Mark Russinovichdeals with memory managment issues on Windows in some posts in his blog. Have a look at Pushing the Limits of Windows: Physical Memoryand Pushing the Limits of Windows: Virtual Memory.
显示的值不是实际使用的内存量。Mark Russinovich在他的博客中的一些帖子中讨论了 Windows 上的内存管理问题。看看推动 Windows 的极限：物理内存和推动 Windows 的极限：虚拟内存。
回答by Godeke
I recommend Process Explorer (http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) as a better way to gauge how much memory a process is using. Right click on the header and choose "Select Columns" and then the "Process Memory" tab and finally check the "Private Bytes" and "Private Bytes History" options.
我推荐 Process Explorer ( http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) 作为衡量进程使用多少内存的更好方法。右键单击标题并选择“选择列”，然后选择“进程内存”选项卡，最后检查“私有字节”和“私有字节历史记录”选项。
As far as your SQL memory configuration, I recommend setting the minimum to whatever you knowyou will never need on the machine for other tasks, and the maximum to what pushes you to filling the physical memory (counting other processes), but no farther.
就您的 SQL 内存配置而言，我建议将最小值设置为您知道在机器上执行其他任务永远不需要的任何内容，并将最大值设置为促使您填充物理内存（计算其他进程）的内容，但不能再远了。
回答by Guffa
The memory usage is normal for those settings, and although you don't see it in the task manager, it's the SQL Server that is using the memory.
这些设置的内存使用情况是正常的，尽管您在任务管理器中看不到它，但使用内存的是 SQL Server。
However, as you see the total memory usage is more than there is physical memory, which is bad for the server's performance. You should notset the memory limit for the database server to more than you have physical memory. The database server uses the memory to cache data and results, and if it has to swap the cache from disk there is no gain in having it.
但是，正如您所看到的，总内存使用量大于物理内存，这对服务器的性能不利。你应该不会比你的物理内存设置数据库服务器内存限制更多。数据库服务器使用内存来缓存数据和结果，如果它必须从磁盘交换缓存，那么拥有它就没有任何好处。
回答by Shiv
Discovered this question when searching for SQL Server memory not being shown in Process Explorer. The reason for this is on SQL Server 32-bit and 64-bit, memory allocation can be configured to use the AWE API.
在搜索进程资源管理器中未显示的 SQL Server 内存时发现了这个问题。原因是在 SQL Server 32 位和 64 位上，内存分配可以配置为使用 AWE API。
The prerequisite for this is Lock Pages in Memoryhas to be configured for the account that the SQL Server process runs under. This applies for both 32-bit and 64-bit. This is done via Group Policy Editor(gpedit.msc) then navigating to Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignmentthen adding the appropriate account to the Lock pages in memorypolicy setting.
此操作的先决条件是必须为运行 SQL Server 进程的帐户配置内存中的锁定页面。这适用于 32 位和 64 位。这是通过组策略编辑器(gpedit.msc) 完成的，然后导航到计算机配置 -> Windows 设置 -> 安全设置 -> 本地策略 -> 用户权限分配，然后将适当的帐户添加到内存策略设置中的锁定页面。
Local System by default already has this (http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx).
默认情况下本地系统已经有这个（http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-系统.aspx）。
For SQL Server 32-bit 2005 - 2008R2, you then need to enable AWE in Memory Options via Use AWE to allocate memory. SQL Server 2012 32-bit and higher no longer has this option.
对于 SQL Server 32 位 2005 - 2008R2，则需要通过Use AWE to allocation memory在 Memory Options 中启用 AWE 。SQL Server 2012 32 位及更高版本不再具有此选项。
For SQL Server 64-bit, having Lock Pages in Memorywill use AWE to allocate memory.
对于 64 位 SQL Server，在内存中设置锁定页面将使用 AWE 来分配内存。
In order to see these AWE memory allocations, you can use a memory mapper tool such as Sysinternals RAMMaphttp://technet.microsoft.com/en-au/sysinternals/ff700229.aspx.
为了查看这些 AWE 内存分配，您可以使用内存映射器工具，例如Sysinternals RAMMap http://technet.microsoft.com/en-au/sysinternals/ff700229.aspx。
You can determine if your SQL Server is using Locked Pages via this:
您可以通过以下方式确定您的 SQL Server 是否正在使用锁定页面：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
EXEC xp_cmdshell 'whoami /priv'
GO
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
and you should see SeLockMemoryPrivilegeis Enabled.
您应该会看到SeLockMemoryPrivilege已启用。
For SQL Server 32-bit, you can then confirm AWE is enabled via (don't need this check for 64-bit):
对于 SQL Server 32 位，您可以通过以下方式确认 AWE 已启用（64 位不需要此检查）：
EXEC sp_configure 'awe enabled'
GO
and the run_value should be 1.
并且 run_value 应该是 1。
If it turns out your server is not using AWE allocated memory, using Sysinternals Process Explorer (http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) as per Godeke's post is the nicer way to go - better interface and easier to understand/use.
如果事实证明您的服务器没有使用 AWE 分配的内存，则按照 Godeke 的帖子使用 Sysinternals Process Explorer ( http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) 是更好的方法 - 更好界面，更易于理解/使用。
回答by programmer
By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
默认情况下，SQL Server 会根据操作系统报告的物理内存负载动态增长和缩小其缓冲池（缓存）的大小。只要有足够的内存可用于防止分页（在 4 到 10 MB 之间），SQL Server 缓冲池就会继续增长。当与 SQL Server 位于同一台计算机上的其他进程分配内存时，SQL Server 缓冲区管理器将根据需要释放内存。SQL Server 每秒可以释放和获取几兆字节的内存，使其能够快速适应内存分配的变化。
