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)的内存量。
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
Memory details:
http://img228.imageshack.us/img228/3830/02042009210917.png
5 个解决方案
#1
The value displayed is not the actual amount of used memory. Mark Russinovich deals with memory managment issues on Windows in some posts in his blog. Have a look at Pushing the Limits of Windows: Physical Memory and Pushing the Limits of Windows: Virtual Memory.
显示的值不是实际使用的内存量。 Mark Russinovich在他博客的一些帖子中处理Windows上的内存管理问题。看看推动Windows的限制:物理内存和推动Windows的限制:虚拟内存。
#2
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 know you 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内存配置,我建议将最小值设置为你知道在机器上永远不需要的任何其他任务,以及推动你填充物理内存的最大值(计算其他进程),但不能更远。
#3
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 not set 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.
但是,如您所见,总内存使用量超过了物理内存,这对服务器的性能不利。您不应将数据库服务器的内存限制设置为超过物理内存。数据库服务器使用内存来缓存数据和结果,如果必须从磁盘交换缓存,则无法获得缓存。
#4
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.
搜索未在Process Explorer中显示的SQL Server内存时发现此问题。原因是在SQL Server 32位和64位上,内存分配可以配置为使用AWE API。
The prerequisite for this is Lock Pages in Memory has 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 Assignment then adding the appropriate account to the Lock pages in memory policy 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- system.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,您需要通过使用AWE在内存选项中启用AWE来分配内存。 SQL Server 2012 32位及更高版本不再具有此选项。
For SQL Server 64-bit, having Lock Pages in Memory will use AWE to allocate memory.
对于SQL Server 64位,在内存中具有锁定页面将使用AWE来分配内存。
In order to see these AWE memory allocations, you can use a memory mapper tool such as Sysinternals RAMMap http://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 SeLockMemoryPrivilege is 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位,您可以通过(不需要对64位进行此检查)确认启用AWE:
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)是更好的方法 - 更好界面,更容易理解/使用。
#5
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可以每秒释放并获取几兆字节的内存,从而可以快速调整内存分配更改。
#1
The value displayed is not the actual amount of used memory. Mark Russinovich deals with memory managment issues on Windows in some posts in his blog. Have a look at Pushing the Limits of Windows: Physical Memory and Pushing the Limits of Windows: Virtual Memory.
显示的值不是实际使用的内存量。 Mark Russinovich在他博客的一些帖子中处理Windows上的内存管理问题。看看推动Windows的限制:物理内存和推动Windows的限制:虚拟内存。
#2
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 know you 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内存配置,我建议将最小值设置为你知道在机器上永远不需要的任何其他任务,以及推动你填充物理内存的最大值(计算其他进程),但不能更远。
#3
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 not set 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.
但是,如您所见,总内存使用量超过了物理内存,这对服务器的性能不利。您不应将数据库服务器的内存限制设置为超过物理内存。数据库服务器使用内存来缓存数据和结果,如果必须从磁盘交换缓存,则无法获得缓存。
#4
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.
搜索未在Process Explorer中显示的SQL Server内存时发现此问题。原因是在SQL Server 32位和64位上,内存分配可以配置为使用AWE API。
The prerequisite for this is Lock Pages in Memory has 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 Assignment then adding the appropriate account to the Lock pages in memory policy 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- system.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,您需要通过使用AWE在内存选项中启用AWE来分配内存。 SQL Server 2012 32位及更高版本不再具有此选项。
For SQL Server 64-bit, having Lock Pages in Memory will use AWE to allocate memory.
对于SQL Server 64位,在内存中具有锁定页面将使用AWE来分配内存。
In order to see these AWE memory allocations, you can use a memory mapper tool such as Sysinternals RAMMap http://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 SeLockMemoryPrivilege is 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位,您可以通过(不需要对64位进行此检查)确认启用AWE:
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)是更好的方法 - 更好界面,更容易理解/使用。
#5
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可以每秒释放并获取几兆字节的内存,从而可以快速调整内存分配更改。