1.最小和最大服务器内存
这两个配置用于控制SQL Server可用内存的大小。对于最小内存,在SQL Server服务启动时,不会马上达到这个设置值,而是仅使用最小的需求内存,然后按需增长,一旦增长到最小内存设置值时,SQL Server将不会再释放内存。最大内存用于设置内存使用的上限,可以使用SSMS或者sp_configure来配置。需要提醒的是,这里的“最大内存”实际上指的是Buffer Pool,在64位系统中,如果没有控制好内存而导致空闲的物理内存不足,会引起Windows削减SQL Server的工作集。
如何计算合适的最大内存?可以参考以下信息。
(1)监控SQL Server的最大内存使用情况
可以通过性能监视器的MSSQL$<instance>:Memory Manager\Total Server Memory (KB)计数器来监控SQL Server总的Buffer Pool使用情况。如果SQL Server所需的物理内存超过了现有的可用数量,这个值就会降低,而在释放内存后,这个值则会升高。可以在一开始把这个值设置得低一点,然后通过监控来适当地进行调整。
(2)SQL Server潜在的最大内存使用
在考虑潜在使用时,很重要的一点是对连接服务器和外部存储过程的调用,在后期开发中,这部分内容可能会非计划地加入。一般来说,每个线程会使用0.5MB(32位)或者2MB(64位)的内存,还要保证大概有512MB可以用于这部分的使用。
另外,一些大型企业可能会使用第三方备份软件、杀毒软件等,这些也会影响SQL Server的内存使用。最好预留1~3GB的内存给这些软件使用。
2.检查最大内存配置是否合理
在搭建新服务器时,该怎么去决定配置的最大服务器内存是多少呢?最直接的答案是:从低开始设置,然后进行周期性监控(开始时监控周期要比较短,以便尽快发现问题),并按需调整。或者使用性能计数器来监控,比如通过MSSQL$<instance>:Buffer Manager\Page Life Expectancy (PLE)和Memory\Available Mbytes来监控。
PLE计数器用于表示SQL Server的数据缓存在内存中的时间,在理想情况下该时间越长越好,这是内存压力指标之一。如果小于300s,就要检查指标Available Mbytes了。
Available Mbytes表示Windows上当前有多少物理内存没有被使用。国外专家建议的标准是最少保留100MB。当然不要把最低标准当作最低配置,应尽可能保证有GB级别的可用内存。
如果PLE很低,但是可用内存数很高,那么应该调高最大服务器内存,因为这样可以增加PLE的时间。相反,如果可用内存很低,但是PLE很高,那就需要降低最大内存配置来释放内存给Windows。下面是部分配置示例。
最大服务器内存30GB,服务器有32G RAM,PLE平均值为10000,可用内存为90MB,那么最少降低500MB最大服务器内存。
最大服务器内存46GB,服务器有50G RAM,PLE平均值为10,可用内存为1500MB,那么应提高最大服务器内存500~1000MB。
最大服务器内存60GB,服务器有60G RAM,PLE平均值为50,可用内存为20M,那么应降低100MB最大内存,或者增加更多的RAM(最直接的方法)。
3.锁定内存页
锁定内存页(Lock Pages in Memory,LPIM)是在Windows和SQL Server之间发生问题时的应急处理方案,在旧版本(比如Windows Server 2003及以前版本)中,效果不佳。如果在操作系统上没有充足的物理内存可用于支持其他请求,会强制回收某些应用的内存。这对SQL Server的内存分配是毁灭性打击。可能会在SQL Server的错误日志中看到类似的话。
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.
从Windows Server 2008开始,这个现象有了明显的改进,但是还会出现。对于这类情况,可以把最大服务器内存调整到适当的大小,保证Windows有足够的内存来运行其他应用。另外也可以使用SQL Server来锁定内存页。
如果启用了LPIM,SQL Server的Buffer Pool页会被“锁定”,并且不允许操作系统强行收回。在页被锁定之后,这部分空间将不会算入可用内存中。但是只有SQL Server的Buffer Pool 分配的页才可以被锁定,操作系统依旧可以收回其他内存,包括SQL Server依赖的进程内存。
如何启用锁定内存可以参考http://support.microsoft.com/kb/2659143。