SQL Server内存理解的误区

 SQL Server内存理解

内存的读写速度要远远大于磁盘，对于数据库而言，会充分利用内存的这种优势，将数据尽可能多地从磁盘缓存到内存中，从而使数据库可以直接从内存中读写数据，减少对机械磁盘的IO请求，提高数据读写的效率。

内存对数据库而言是如此的重要，因此只要在涉及数据库优化的地方，我们都可以看到内存的身影。我们通常会想尽各种办法来优化数据库内存的使用，比如开启AWE、设置最大内存、锁定内存页等，但在很多时候，我们实际上都不知道某个配置是否一定能够解决当前的问题，或者我们误以为会解决当前的问题，出现这种现象的原因是我们对数据库的内存理解还不够透彻或者理解存在误区，本文我希望将结合自己的经验和《SQL Server 2012实施与管理实战指南》的内容，通过以【介绍SQL server常见内存误区】的方式跟大家分享下我对SQL server内存的理解。

误区一：SQL Server在32位操作系统只能使用4G内存

对于这个问题，我们从两个方面来看：

1. 在32位操作系统中，应用进程可以使用的虚拟地址空间大小为4G，其中2G是给核心态（操作系统），另外2G留给用户态（应用程序）。因此，SQL server其实能最大利用的内存只有2G。

不过，这种核心态和用户态的分配方式是可以改变的，当在操作系统的启动文件（windows server 2003的boot.ini）中开启3G开关后，便可以使得用户态的内存增加到3G，而只留1G给核心态，这样做的好处是可以使得SQL server使用到3G的内存，不过坏处也很明显，因为减少了核心态的地址空间，在操作系统系统负载比较大时，可能会出现不稳定的风险。

（目前使用使用32位操作系统的客户越cong来越少，而且微软新推出的操作系统也都是64位，这种问题以后几乎都碰不到了。）

2. 从windows server 2003到windows server 2008，只要是标准版或者WEB版，且为32位架构时，操作系统最大也只能支持4G内存，即使按照上文1介绍的方法开启了3G开关，SQL server也只能使用最大3G的内存。

但如果是企业版或者数据中心版的操作系统时，同样是32位的架构，操作系统却最大可以支持到64G的内存（开启PAE的前提下）。但默认情况下SQL server依然只能使用最大2G的内存。

因此，相比64G的操作系统内存来说，2G对数据库而言简直就是杯水车薪，资源浪费。为解决这个问题，微软为企业版和标准版的SQL server（2005、2008）引入了AWE（地址窗口扩展）功能，你可以通过开启AWE来扩展SQL server的可用内存，使之最大可以达到64G内存（实际上会小一点，因为操作系统本身需要占用部分内存）。

总结起来，可以用一个表格来描述上面两个场景：

误区二：在开启了AWE后，SQL Server的所有功能一定能使用超过2G的内存。

在SQL server进程中，内存并非全部由SQL server的数据缓存所使用，部分通过SQL server调用的第三方代码、加载在SQL server内部的其他dll、SQL server连接、链接服务器、编译缓存、查询计划缓存等也会使用SQL server进程中的内存。

这部分组件或者功能在申请内存时与传统的数据缓存申请的方式不同，因为他们通常会申请大于8KB的内存页，这种内存区域为multi-page（以前叫memtoleave）。对于multi-page占用的内存，是没法使用SQL Server的AWE特性的，也就是说，在32位的SQL Server中，数据库即使开启了AWE，也只能使用到2G的内存（用户态）。由此可见，AWE更多的是提升了data page buffer pool的内存空间。

备注：在32位的SQL Server中，multi-page的默认大小为256MB+sp_configure配置的最大线程数X512KB，它是SQL server启动时就已经设定好的。

备注：在64位的SQL server中，multi-page的大小没有限制。

误区三：SQL Server进程不会使用超过最大内存设置的内存大小。

在SQL server的sp_configure中有一个max server memory (MB)的配置项（SSMS中右击实例，在属性中选择内存也可以看到），我们很多人以为设置了这个值以后SQL server的进程不会使用超过这个大小的内存。

其实不然，max server memory (MB)只是buffer pool的上限。但是在SQL server的内存中，不仅仅包括buffer pool，还有multi-page的内存，对于这部分内存，是无法通过max server memory (MB)来限制的，所以，在实际环境中，我们可能会看到sqlservr.exe这个进程会占用的内存会超过max server memory (MB)设定的值。

备注：一般情况下，multi-page占用的空间不会很大，因此，通常我们将max server memory (MB)约等于SQL server进程占用的内存大小。

误区四：设置了SQL Server的最小内存，表示SQL server启动时就会使用到这个大小的内存。

在SQL server的最小内存也是通过sp_configure配置，配置项为min server memory (MB)。该配置项默认为0，表示不限定最小内存，如果设置成某个具体值时，比如2G，表示当SQL server占用的内存超过这个大小后，就不会再低于这个值。而不是说SQL server启动时就马上达到这个值。

这种机制的好处就是避免SQL server的内存被操作系统不断挤占，这样当负载一旦起来，数据库可以立即使用内存而不需要向操作系统申请内存。（当数据库申请时不见得操作系统有多余的内存分配给数据库，这时数据库就只能歇菜了）。

因此合理设置数据库的最低内存也是十分重要的。微软建议的最小内存值为服务器总内存减去1-2G，跟最大内存的值差不多。（前提是这个服务器制作数据库服务器）

参考资料：http://msdn.microsoft.com/en-us/library/ms178067.as

误区五：SQL server占用内存特别大，说明SQL server有问题。

常见的关系型数据库都有这个特点：数据库就会尽可能的占用服务器的内存，而且这些占用的内存中即使很大部分空闲也不会释放，除非操作系统遇到内存压力，才会被操作系统重新分配。因此我们可以看到数据库服务器的内存使用率一般都会很高。

这其实并不是一个问题，反而是数据库的一个特性，就如文中首段说的，只有越多的数据缓存在内存中，数据库的读写效率才会越高，响应速度才会越快。这才是使用数据库的最佳方法。

不过，在很多场景下，服务器并非专用于数据库，为确保服务器上其他应用程序也能正常运行，我们必须为数据库设置最大内存，否则其他应用程序就会因为内存不足出现访问不流畅的问题。笔者曾碰到一个案例，某医院的HIS服务器必须每隔几天重启一次才能保证HIS业务能够被正常访问。原因就是因为数据库内存没有设置最大值，导致应用程序的内存被数据库挤占。

误区六：内存使用率高说明数据库服务器的遇到了内存压力。

（影响SQL Server性能的因素很多，不过因为本文的中心是谈谈内存因素与SQL server的关系，所以其他因素不在本文考虑范围内。）

如果一个DBA因为数据库服务器的内存使用率很高而做出内存存在压力的判断，说明这个人还不够全面。前文已经说过，数据库“喜欢”内存是天性，几乎所有DB服务器都会出现内存使用率很高的问题（除非设置最大内存，且最大内存远低于服务器内存），因此我们不能据此来判断数据库的内存压力。

我们通常会通过一些性能计数器来监控数据库的内存使用情况，据此作出压力的判断：

SQLServer:Buffer Manager\Buffer cache hit ratio

（一般要求OLTP的cache hit ratio在95%以上，OLAP则需要在90%以上才能称之

为性能良好）

SQLServer:Buffer Manager\Page life expectancy

SQLServer:Buffer Manager\free pages

SQLServer:Memory Manager\Target Server Memory

SQLServer:Memory Manager\Total Server Memory

上述后三个指标最能直观反映SQL server是否存在内存压力，free pages表示SQL server占用的内存中，有多少页面是free状态，将这个数值乘以8KB就得到了可用缓存的大小，这个值越大，说明SQL server占用的内存有很多还未被使用，因此说明了内存没有压力问题。

Total Server Memory表示SQL server为自己分配的buffer pool的总大小（已使用和未使用）。Target Server Memory表示操作系统能够分配给SQL server的目标内存大小，其最大值约等于【误区一】中描述的SQL server可用地址空间的大小，但会随着操作系统的压力增加而减少。正常情况下，Target server memory会大于total server memory，说明SQL server为自己分配的buffer pool还比较少，小于操作系统为SQL server设定的目标内存大小，数据库此时不会有内存压力。不过，随着操作系统的压力增加，它会调小Target Server memory的值，使其小于SQL Server的Total Server Memory，这样一来，SQL Server不得不释放已占用的缓存，减小total server memory，这种情况，说明数据库服务器存在内存压力。

误区七：增加内存一定能够提升SQL server的性能。

数据库虽然会尽可能多的占用内存，但并不意味增加内存就一定是越多越好，就如同上文说的，如果数据库的内存长期没有什么压力，增加内存也不会带来性能的提升。

另外，在32位 的SQL server中，在数据库启动时就为连接、查询计划、第三方dll、链接服务器等分配了固定大小的multi-page（上文在介绍AWE时已有介绍），因为multi-page的大小不会随着内存的增加而改变，所以即使增加内存，也无益于这些功能、组件，而只是为增大了数据缓存。

备注：在64位的SQL server中，multi-page的大小没有限制。

误区八：如果其他应用程序也需要内存，SQL server会释放一部分自己的内存，以保证其他应用程序能够正常运行。

SQL Server不会为其他程序释放自己以占用的内存，只有在操作系统遇到内存压力时，才会根据操作系统的要求减少自己的内存占用量。

但如果SQL server启用了锁定内存页的，那即使是操作系统有要求，其内存也不会释放。因为锁定内存页会使SQL server占用的内存长久保留在物理内存中，避免被分页到虚拟内存中去，这是提升SQL server性能的常见做法。在SQL Server的推荐配置中，我们经常建议客户这样做。不够为了避免内存占用太大，可以通过设置最大内存来限定内存的使用上限。