Max server memory是否需要配置？
翻译自：https://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/
是否需要配置Max server memory和Min server memory？这个配置的正确值是什么？如何确定？
有很多关于它的讨论，以上问题在很多论坛被SQL Server DBA频繁问到。如果你问我，它依赖于多方面。
在我们选择配置或保留默认值之前，理解SQL Server如何增长和收缩它的基于操作系统可用内存的使用是非常重要的，甚至当Max server memory没有配置或为默认值。
SQL Server如何增长和收缩它的基于操作系统可用内存的使用是非常重要的，甚至当Max server memory没有配置或为默认值？
SQL Server内存管理设计为基于系统可用内存数量动态调节内存使用。SQL Server将根据需要分配内存，只要内存可用，也就是，只要MEMPHYSICAL_HIGH (HighMemoryResourceNotification)提醒信号在Windows里发出。SQLServer将缩减它的使用当MEMPHYSICAL_LOW (LowMemoryResourceNotification)信号在Windows发出。当可用内存在低内存和高内存之间，SQL Server将使用一些异常保持内存使用稳定（RESOURCE_MEM_STEADY）。
你可以从这里下载ResourceNotificationHighandLow.exe来查看Windows的内存提醒。
发出一个LowMemoryResourceNotification事件信号的可用内存默认等级是大约32MB/4GB，最大64MB。（默认，在大多数系统上该阈值是64MB）
发出一个high-memory-resource提醒事件信号的默认级别三倍于默认的低内存值（默认，在大多数系统上阈值是64*3=192MB）。
要点：
当系统上的可用内存小于192MB，HighMemoryResourceNotification (MEMPHYSICAL_HIGH)信号被Windows取消，SQL Server将不会增长它的Bpool。
当系统上的可用内存小于64MB，LowMemoryResourceNotification (MEMPHYSICAL_LOW)信号被Windows发出，SQL Server将会收缩它的Bpool（减少它的内存使用）。
但系统上的可用内存在192MB和64MB之间（也就是，在LowMemoryThreshold和HighMemoryThreshold之间），SQL Server将不会增加或收缩它的使用（使用一些异常，我们将在一会看到）。
注意：所以除非有一个疯狂的应用程序在系统里以锯齿形状保持分配和释放内存，Windows触发HighMemoryResourceNotification和LowMemoryResourceNotification一个接一个的，SQLServer将不会持续循环增加和收缩它的内存使用。如果在系统中有这样的应用程序，那么甚至配置Max server memory不会有帮助。
默认的低内存阈值64MB不会是对所有的系统都是理想的。例如：让我们假设一个应用程序正突然请求150MB内存，当可用内存是190MB，授予成功。可用内存现在降到40MB使得Windows发出LowMemoryResourceNotification信号。SQL Server将会从Windows响应LowMemoryResourceNotification，但是同时Windows工作集管理器也将开始收缩所有进程的工作集。这将降低系统的整体性能。
我们可以通过修改如下注册表值来增加LowMemoryThreshold值。如果LowMemoryThreshold设置为更高的值，操作系统将会更加容易在低内存条件提醒应用程序（像SQL Server），在系统急需内存之前和在Windows工作集管理器开始收缩所有进程的工作集之前，SQL Server可以更容易响应内存压力。
在Regedit，定位到HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement
右键点击右侧面板，选择“New”，选择点击“DWORD Value”，输入“LowMemoryThreshold”
双击“LowMemoryThreshold”，value选择decimal，设为512
重启系统让配置生效
在以上示例，我设置LowMemoryThreshold为512MB，因此只要可用内存降到512MB，MEMPHYSICAL_LOW提醒信号将会发出，HighMemoryResourceNotification (MEMPHYSICAL_HIGH)将会发出信号直到可用内存为1536MB（LowMemoryThreshold*3）。
在做出以上修改后，直到系统中的可用内存大于1536MB，SQL Server才会增加它的Bpool内存，只要可用内存肩高1536MB以下，HighMemoryResourceNotification信号将会被Windows取消，导致SQLServer维持稳定状态，之后不会增加它的内存使用，但那不意味着在HighMemoryResourceNotification提醒取消后，SQL Server将会等待LowMemoryResourceNotification提醒收缩它的内存使用。SQLServer将会一直尝试在系统中保持高的可用物理内存（也就是，SQL Server将会尝试在系统中保持可用内存为HighMemoryThreshold (LowMemoryThreshold*3)）。
如果我在相同的服务器上有多实例SQL Server该怎么办，在它们之间如果平衡内存使用？
SQL Server将会尝试与运行在相同容器内的其他SQLServer实例平衡它的内存使用。就像我之前提到的，SQL Server将会尝试维护系统上的可用内存为高内存阈值。SQL Server Lazy writer检查在过去的10秒钟是否有磁盘读操作，如果在过去的10秒钟没有读操作，SQL Server将会降低内存使用直到操作系统发出HighMemoryResourceNotification信号。
让我们通过一个示例看一下：
让我们假设系统上有两个SQL Server实例，有32GB内存，Lowmemorythreshold设置为512MB（因此HighMemoryThreshold为1536（Lowmemorythreshold*3））。
当操作系统启动时HighMemoryResourceNotification设置为开启，因为服务器上有足够可用的内存。
SQL Server实例1首先启动，消耗内存直到HighMemoryResourceNotification资源提醒被取消（当可用内存降到1536MB以下，HighMemoryResourceNotification将会被取消）。
现在第2个实例启动，发现高内存资源提醒被取消，因此不会增加内存使用。
第1个实例的Lazy writer线程检查在过去了10秒钟是否有第1个实例执行的磁盘读操作，如果没有磁盘读，那么第1个实例将会收缩它的使用直到操作系统发出HighMemoryResourceNotification信号（当可用内存变为1536MB时，HighMemoryResourceNotification信号发出）。
渴望内存的第2个实例看到高内存资源提醒后，开始增加内存使用直到高内存提醒被取消。一旦高内存提醒被取消，第2个实例将会停止增加内存使用。
第1个实例发现高内存使用被取消，将会再次检查在过去的10秒钟内是否有任何磁盘读操作，如果没有读操作，那么进一步收缩内存直到高内存资源提醒。
一旦高内存信号发出，第2个实例将会再次增加内存使用。
在一段时间每个实例将非常好的在它们之间平衡内存需求。（也就是，如果在过去的10秒钟内有执行读操作，我们假设对于该实例有额外的内存需求，那么它不会收缩，而另一方面，10分钟多分钟没有读操作，并且如果可用内存低于高内存阈值，该实例将会收缩内存给到其他实例）。
之后有更高内存需求的实例将会比有低内存需求的实例消耗更多内存。这样两个实例会在彼此之间平衡它们的内存需求。
注意：
如果系统上的总物理内存相比较运行在系统上的多实例的内存需求非常低，以上逻辑可能不适用，因为如果你开始第2个实例，而第1个SQL Server实例正利用全部内存运行，仍执行大量读操作。也就是，RESOURCE_MEM_STEADY并仍有大量读操作，第2个实例会花很长时间，或之后可能不会增加内存使用。在这种情况下，你可以设置Max server memory，但是SQL Server的性能将会因为内存争用而非常差。
也要小心，当你增加LowMemoryThreshold的值超过512MB。增加这个阈值增加了可用内存的范围，不会发出LowMemoryResourceNotification和HighMemoryResourceNotification对象信号（而是RESOURCE_MEM_STEADY）。因此当你有多个实例，如果你启动第2个实例，而第1个实例正在利用全部内存运行，并且有大量的读操作，也就是，RESOURCE_MEM_STEAY+持续读操作，第2个实例之后会花时间增加内存使用和将Lowmemorythreshold变低的机会，因为RESOURCE_MEM_STEADY更宽的范围。
FAQ:
当MTL分配增加的时候，会发生什么？
当MTL消耗增加时系统上的可用内存降低。如果设置了MEMPHYSICAL_HIGH，那么对于bPool没有任何作用。如果MTL消耗大大增加，可能导致可用内存进一步下降，导致Windows触发LowMemoryResourceNotification。
如果发出LowMemoryThreshold信号，SQL Server将会收缩它的bPool使用。
只要发出LowMemoryResourceNotification信号，Windows工作集管理器将会开始收缩所有集成的工作集吗？
修改LowMemoryThreshold的其他作用是什么？
可能有其他来自Windows的应用程序和驱动正在使用内存提醒去增加和收缩内存使用。当Windows发出提醒的时候它们也会收缩和增加内存使用。
当在SQL Server里有一个很棒的动态机制来增加和收缩内存使用时，为什么我需要修改我的SQL Server内存设置？
如果你的操作系统是Window 2008或者以上版本，并且如果你打了这个链接和这个链接里的所有补丁，并且你没有突然请求大量内存的不完美驱动或应用程序，并且如果你没有使用大页内存模型，你可以保留Max server memory为默认值，否则我会建议你设置Max server memory。
如果你决定配置Max server memory，记得它不会控制SQL Server使用的整个内存。在SQL Server 2012之后，由Max server memory控制的内存分配有重要的改变。让我们来理解下在SQL Server 2012和之前版本的SQL Server里，它控制了什么分配。
SQL Server Max Server Memory控制了什么？（来自SQL Server 2012内存）
SQL Server内存在内部分为两个部分，称为BPOOL和NonBPool（也叫MTL或MTR）。有关BPOOL和MTL的更加详细的信息可以在这篇博文里找到。
在之前版本的SQL Server（一直到2008 R2），“Max Server Memory”控制了Single page allocator (BPOOL)在SQL Server用户地址空间可以消耗的最大物理内存。
只有Single page allocator是BPOOL的部分，Max server memory只控制BPOOL，因此以下分配在BPOOL（Max server memory）之外：
来自SQL Server的Multi-Page分配（有分配请求大于8KB并需要持续内存）
CLR分配（包含SQL CLR堆和在启动期间它的全局分配创建）
在SQL Server进程内对thread stacks的内存使用（Max worker threads * thread stack size）。在32位SQL Server里Thread stack大小为512K，在WOW是904K，在64位是2MB
Non-SQL Server DLL产生的直接Windows分配（这些包含Windows堆使用和加载到SQLServer进程的直接虚拟分配。例如：来自扩展存储过程DLL的分配，使用OLE自动化过程（sp_OA调用）的对象创建，加载到SQL Server进程的链接服务器提供者的分配）
SQL Server 2012内存管理器现在整合了single page allocator和multipage allocator到一起作为any-size page allocator。结果，any-size page allocator现在管理分配，这在过去归类为single page和Multi-Page分配：
“Max server memory”现在控制和包含“Multi pages allocations”。
在之前版本的SQL Server CLR内存分配是在BPOOL（Max server memory）之外。SQL Server 2012在“Max server memory”包含SQL CLR内存分配。
SQL Server 2012“Max server memory”配置不只包含以下分配：
SQL Server进程内的thread stacks内存分配。
直接请求Windows的内存分配（例如：加载到SQL Server进程的第三方DLL的分配（Heap，Virtualalloc调用），使用OLE自动化过程（sp_OA）的对象创建等）。
希望你很清楚了Max server memory控制的内存分配，让我们看看如何设置它。
对于SQL Server Max server memory如何设置正确的值？
对于这没有什么捷径。评估内存需求，通过运行在相同服务器上的其他应用程序，操作系统，驱动，SQL Server Non-bPool分配，作业，防病毒软件等等。确保你有可接受的可用物理内存，甚至当系统处于高负载下。
考虑操作系统内存需求。
大约1GB（如果它是DC，cluster等会增加）
考虑运行在服务器上的其他应用程序/进程的内存需求。
基于运行在系统上的应用程序/进程/防病毒软件和它们的内存需求来获得。（Perfmon Process-> Private bytes和Working set会有帮助）
考虑驱动/固件的内存需求。
基于安装在系统上的驱动的内存需求来获得。（RAMMAP可能有帮助）
考虑SQL Server Non-bPool（也叫MTL或MTR）内存需求。
select sum(multi_pages_kb)/1024as multi_pages_mb from sys.dm_os_memory_clerks
（如果SQL Server版本是2012，你可以跳过以上查询）
+
Max worker threads * 2MB
+
直接Windows分配的内存，在大多数情况下大约0到300MB，但是，如果有很多加载到SQL Server进程里的第三方组件（包含链接服务器DLL，第三方备份DLL等），你可能要增加它。
+
如果你正广泛的使用CLR，为CLR添加一些额外的内存。
考虑运行服务器上的作业（包括复制代理，日志传送等）和包的内存需求。
你得获取到（可能MB到GB不等）。
考虑SSAS和***S内存需求。
你得获取到。
确保有足够的空闲空间给操作系统。
大约（100MB/GB，直到4G）+（额外的50MB/GB，直到12G）+（额外的25MB/GB，直到你的内存大小）
译者注：对于32GB内存，100*4+50*8+25*20=1300MB
其他内存需求。
如果对于你的环境，你还有其他特定内存需求。
对于所有以上内存需求，一旦你计算了一个合适的值，将以上需求求和，并从总物理内存扣除，获得你最大服务器内存的理想值。
Max server memory = Total physical memory –(1+2+3+4+5+6+7+8)
如果你仍看到LowMemoryResourceNotification或者working set频繁低于100%，那么使用这个exe来打印出，当操作系统发出低内存提醒时，所有进程的内存信息和系统范围内存信息（全局内存状态）。当有LowMemoryResourceNotification时，一旦你从这个exe获得输出信息，你需要重新评估每个进程的需求，并相应的稍微调整Max server memory。
重要：如果你是Windows 2003，确保你有这个补丁，http://support.microsoft.com/kb/938486
那关于Min servermemory我要配置它么？
我之前有提到，当Windows发出LowMemoryResourceNotification或者HighMemoryResourceNotification被取消且10秒钟内没有读操作，SQLServer收缩它的内存使用。
它能收缩多少？
直到“Minimum server memory”（如果系统上有持续内存压力）。
当你设置Max server memory和Min server memory为相同值，会发生什么？
SQL Server绝不会收缩它的内存使用，甚至当有系统范围的内存压力（在系统级别设置Lowphysicalmemory提醒）。注意：这个设置不会影响系统的页交换。
这有什么影响？
当有LowMemoryResourceNotification，如果LPM没有启用，SQLServer的working set (Bpool + Non bPool)将会被页交换。如果LPM启用，系统急需内存，Non-bPool将会被页交换。
译者注：LPM，Lock Pages in Memory，内存中锁定页。
当Windows里有LowMemoryResourceNotification，如果你不想SQL Server收缩它的内存使用，配置Minserver memory和Max server memory为相同值（坏的选择）。
如果你想限制“SQL Server想要收缩的内存数量”，你可以配置这个值。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
