理解内存----优化SQL Server内存配置

最小和最大Server内存

Min Server Memory (MB) 和 Max Server Memory (MB)控制所有SQL Server内存使用的许可大小。比起之前的版本，SQL Server 2012的Memory Manager可以更简单地设置SQL Server内存需求的大小。SQL Server服务是以所需的最小量启动的，并根据需要增长。一旦内存使用增长超过Min Server Memory设置，SQL Server将不会释放任何低于该量的内存。Min Server Memory设置内存使用的下限，而Max Server Memory则设置上限。这两个值可以使用sp_configure或通过Management Studio中的SQL Server属性窗口的内存页面进行设置。两个设置中，配置缓冲池的最大值更重要，它会阻止SQL Server占用过多的内存。这在64位系统中尤其重要，因为缺少可用物理内存能够导致Windows裁剪SQL Server的工作集。后面的“锁定内存页”有关于这个问题的完整描述。对于配置Max Server Memory，有一些不同的方法来计算合适的值，最直接的方法是看看SQL Server的最大使用量，及确定SQL Server之外内存需求的最大潜在用量。

查看SQL Server的最大使用量

设置SQL Server动态管理内存，然后使用性能监视器监视计数器MSSQL$<instance>:Memory Manager\Total ServerMemory (KB)。这个计数器测量SQL Server的总缓冲池使用量。如果SQL Server以外的其他需求需要比当前可用内存更多的物理内存时，Total Server Memory值会降低，然后使用任何可用内存再增加。如果您监视此计数器一段时间（包括忙时和淡时），然后你就可以将Max Server Memory设置为你观察到的Total Server Memory (KB)的最低值，你不必担心SQL Server在正常操作期间收缩它的使用量。

确定SQL Server之外内存需求的最大潜在用量

此选项是最流行的，因为其目的是计算出最坏的情况下，除SQL Server之外的内存需求。你应该考虑如下情况：

• 2GB用于Windows
• xGB用于SQL Server工作线程。你可以查询sys.dm_os_sys_info来获取最大工作线程数，每个线程会使用0.5MB（在x86系统），2MB（在x64）
• 512MB，如果你用Linked servers、扩展存储过程dll，或通过自动化程序（Automation     procedures (sp_OA calls)）创建的对象
• 1~3GB，用于其他可能再系统上运行的应用程序，如备份程序或反病毒软件

例如，在一个有8个CPU内核、64GB RAM、运行SQL Server 2012、一个第三方备份工具和病毒检测程序的服务器上，你将考虑如下情况：

• 2GB用于Windows
• 1GB用于工作线程（576     X 2MB四舍五入）
• 512MB用于Linked Server等
• 1GB用于备份程序和病毒检测程序

共计4.5GB，你可以将Max Server Memory配置为59.5GB

检查你的Max Server Memory是否有效

当你建立一个服务器时决定要如何配置max server memory，没有服务器运行它期望的负载时衡量其有效性并调整它重要。要做到这一点，一个简单的方法是使用性能监视器计数器，具体地说，MSSQL$<instance>:Buffer Manager\Page LifeExpectancy (PLE)和Memory\Available MBytes。这两个计数器之间的平衡将告诉您max server memory设置有多大效力。PLE显示了SQL Server希望一个页面保持在数据缓存中多少秒，它是一个好的衡量SQL Server内存压力的办法。Available MBytes显示了Windows有多少可用的物理RAM。

如果你的PLE低（<300），就检查Available MBytes，看看有多少未使用的内存可用。如果可用的少于5MB，Windows就会开始侵略性地裁剪所有的应用程序的工作集，这被认为是一个紧急问题。如果PLE低，并且你有大量Available MBytes，你就应该增大Max Server Memory的设置，从而增加PLE；相反，如果Available MBytes低，而PLE很高，那么你就应该降低Max Server Memory设置，从而把一些RAM返还给Windows。下面是一些示例场景来说明这一点：

• 有32GB RAM的服务器Max Server Memory是30GB，PLE平均为10000，Available MBytes是90MB。解决方案：至少降低Max     Server Memory 500MB。
• 有50GB RAM的服务器Max Server Memory是46GB，PLE平均是10，Available MBytes是1500MB。解决方案：增加Max     Server Memory 500MB到1000MB。
• 有64GB RAM的服务器Max Server Memory是60GB，PLE平均是50，Available MBytes是20MB。解决方案：降低Max     Server Memory 100MB，并购买更多RAM（尽快）。

Lock Pages in Memory

Lock Pages in Memory (LPIM)被用作应急措施（work-around），当Windows和SQL Server之间发生错误时，这在旧版本的SQLServer上特别不好。如果Windows没有足够的可用物理内存，服务来至来自驱动器或其他应用程序的资源请求，Windows将会裁剪在服务器上运行的所有应用程序的工作集（它指的是一个进程的物理内存使用情况）。这是正常的行为，应该不会有太显著的影响。Windows Server 2003中并没有很好地处理写得很糟糕的驱动程序（badly written drivers），实际上可以强制所有的应用程序清空他们的工作集。这就是所谓的侵略性工作集裁剪（aggressive working set trimming），它对SQL Server的内存分配有破坏性的影响 - 因此影响性能。所以，你可以看到，当发生这种情况时，微软增加了一个消息到SQL Server错误日志。下面是一个例子：

A significant part of sql server process memory hasbeen paged out.This may result in a performance degradation. Duration: 0seconds.Working set (KB): 1086400, committed (KB): 2160928, memory utilization:50%.

在Windows Server 2008中，这种行为得到显著改变，防止了最大的问题--写得很糟糕的驱动程序引起的应用程序工作集被清空。这不会影响SQL Server 2012，因为它只运行在Windows Server 2008+中。在SQL Server 2012中，当Windows执行工作集裁剪时，你仍然会得到记录的消息。几个消息能够表明SQL Server的工作集逐渐下降（这仍然是个问题）。有两种方式解决这个问题：

• 恰当地设置Max Server Memory，确保Windows和服务器上运行的其他进程有足够的物理内存来执行它们的工作，而不必要求SQL Server裁剪。
• 如果你仍然看到的问题（或者，如果它的影响是非常严重，你不想再冒险见到它），你可以使用Locked Pages in Memory     (LPIM)来配置你的SQL Server。

当LPIM启用，SQL Server的缓冲池页面是“锁定”且不可分页的（non-pageable），因此Windows在裁剪时不能占有它们。一旦页面被锁定，它们就不会被考虑成为用于工作集裁剪的可用内存的一部分。然而，只有SQL Server缓冲池分配才可以被锁定，不过Windows仍然可以裁剪其他进程的工作集，这影响了SQL Server所依赖的资源。如果设置了合适的max server memory后你还继续获得工作集裁剪，或者SQL Server的工作集再次被裁剪的成本过于冒险，LPIM就应该被使。

如果LPIM在工作，你会在SQL Server的错误日志中看到如下信息：UsingLocked Pages in the Memory Manager。你可以阅读有关Microsoft支持此功能，以及如何启用它的内容，在http://support.microsoft.com/kb/2659143。

优化Ad-Hoc Workloads

每次生成的执行计划存放在计划缓存，以希望可以被重用，这是SQL Server管理工作负载的有效办法之一。如果一个执行计划将不会被重用，那么它只是不必要地占用资源，使用未参数化（unparameterized）的ad-hoc T-SQL是最有可能的原因。当您在SQL Server中执行代码，它会生成代码的哈希值，并使用该值来决定计划重用。如果你执行一个存储过程，散列值会根据存储过程名称生成，计划将在每个后续的过程调用中被重用，不管其所使用的参数值。

如果您运行存储过程(ad-hoc T-SQL）之外的相同的代码，哈希会对整个语句采用，包括任何文字值。当您改变文字值再次执行时，哈希是不同的，因此SQL Server不能找到匹配的哈希值，并产生一个新的执行计划，而不是重用前一个。这种情况可能会导致一个场景称为计划缓存膨胀（bloat），由此可能会产生成千上万的ad-hoc计划，并且缓存了为1的usecount，即使代码基本上是相同的。理想的解决方案是使用存储过程或函数，或参数化所有的ad-hoc T-SQL ，但是这可能是非常具有挑战性的，而且往往无法实现，因为复杂性和公司策略。所以微软在SQL Server 2008推出了服务器级别选项Optimize for Ad-hoc Workloads来帮助解决这个问题。当启用了此选项，则一块ad-hoc T-SQL在首次被执行时，SQL Server将仅仅缓存一个计划存根，而不是完整的计划。如果SQL Server随后尝试重用这个计划，则计划将再次产生，但这个时候是全部被缓存。这就避免了这种场景，即数以千计的单次使用的计划占用了宝贵的缓存空间。

如下图，执行T-SQL语句得到所需结果，可以看到有超过3GB的内存用于缓存，因此很有必要查清它的使用细节。

下面将通过Cached Object Type来破坏计划缓存的大小，你可以看到有718MB的计划缓存被ad-hoc计划占用，平均使用数是20，很低。

因此，现在你想确定其中有多少是单次使用（single-use）计划，下图表明有678MB的缓存用于那些不被重用的计划，它虽不是巨量，但它完全被浪费，因此没有理由不清除这些计划。

Optimize for Adhoc Workloads选项可确保这种情况下不会发生，因为它仅影响ad-hoc计划，我们建议在所有SQL Server的安装中，默认开启它。