图文详解：使用SQL Server自带工具诊断内存问题

分配足够内存对SQL Server性能至关重要。要确保可以优化SQL Server性能，我们需要定期监控SQL Server内存使用情况。本文介绍了其自带工具，可以使我们的这项工作更容易。

对于SQL Server的性能来说，内存量比其它硬件部分对其性能影响更大。没有足够的内存，SQL Server只能对磁盘读写数据来完成查询。内存访问比磁盘速度快1千到1万倍。

因此，我们需要定期监控SQL Server内存用量确保有足够的内存可以缓减严重的性能问题，并确保SQL Server运行的足够快。

幸运的是SQL Server提供了内建工具可以帮助我们跟踪SQL Server内存瓶颈的根本原因所在，判断有多少内存可用以及当前时刻SQL Server在如何使用内存。

动态管理视图

动态管理视图最早是在SQL Server 2005中引入的，它可以提供关于服务器和数据库状态的信息。这些视图用来监视整体SQL Server健康状况，判断SQL Server性能瓶颈根本原因之所在，优化SQL Server实例或者数据库性能。下面我们列出了SQL Server 2008 R2，SQL Server 2012和SQL Server 2014这些版本中动态管理视图的功能。你可以使用这些视图来查看SQL Server内存使用信息。例如：SQL Server实例目前有多少内存可用，或者SQL Server实例使用可用内存的状况如何。

• sys.dm_os_sys_info: 返回计算机资源使用情况信息。
• sys.dm_os_memory_cache_counters: 返回缓存条目分配运行时信息，它们的使用情况和缓存条目的内存来源。
• sys.dm_os_memory_nodes: 返回关于内存节点的信息。
• sys.dm_os_memory_cache_entries: 返回原始的和当前的任何缓存条目消耗。
• sys.dm_os_memory_cache_hash_tables: 把SQL Server实例中每个活跃缓存返回到一行。
• sys.dm_os_memory_cache_clock_hands: 返回具体缓存时钟每个处理的状态。
• sys.dm_os_memory_clerks: 返回SQL Server当前消耗所有内存记录的明细信息。
• sys.dm_os_memory_objects: 返回SQL Server当前分配的内存对象信息。
• sys.dm_os_performance_counters: 返回SQL Server专有的性能计数器信息。
• sys.dm_os_ring_buffers: 返回关于系统健康状态的详细信息。该信息对内存压力诊断非常有用。该视图目前只有在SQL Server 2014中支持。

除了这些视图，SQL Server 2014还有一组专门针对内存OLTP引擎的视图。

DBCC MEMORYSTATUS命令

MEMORYSTATUS命令并没有记录在文档说明中，它可以提供SQL Server当前状态的一个快照。该内存用量信息可以帮助你快速判断SQL Server中内存压力的根本原因，分析SQL Server使用所分配内存的情况如何。DBCC MEMORYSTATUS输出中包含的一些信息在动态管理视图中是没有的。这就是为什么微软公司客户支持服务团队和有经验的数据库管理员们仍然会依赖这个没有记录在文档中的命令来快速诊断SQL Server内存消耗问题的原因。

DBCC MEMORYSTATUS命令描述了SQL Server各种组件对其8k缓存的分配情况。它提供了SQL Server中目前内存分配情况的展示。如果你要诊断SQL Server内存消耗的问题，这些信息尤其有用，命令输出如下(图1)：

• 提交内存分配情况
• 缓存池大小和构成
• 存储过程缓存构成
• 动态内存用量分布
• 各种全局内存用量分布
• 查询语句内存分配信息
• 内存管理信息(比如VM保留内存，VM提交内存，AWE分配内存，预留内存和预留内存占用情况)
• SQL Server缓存如何分配，包括缓存区活动。
• 关于内存管理器，缓存管理器和其它一些SQL Server处理程序消耗内存的信息。

图1：DBCC MEMORYSTATUS命令输出内容样例

扩展事件

扩展事件是高扩展性轻量级性能监视系统，占用非常少的系统资源。你可以使用扩展事件系统健康回话监视内存相关的信息，比如内存代理器，内存节点溢出错误等等。

sp_server_diagnostics

系统存储过程sp_server_diagnostics最早是在SQL Server 2012中引入的。它可以捕获有用的诊断数据和SQL Server健康状况信息，检测到潜在的故障问题。例如，你可以利用该存储过程捕获物理内存和虚拟内存，缓存池，页大小，缓存和其他内存对象信息。

SQL Server 监测器

SQL Server监测器是一款图形用户界面产品，它可以捕获SQL Server和分析服务的活动。SQL Server监测器把事件捕获为跟踪数据，你可以把数据保存到本地文件、网络存储或者SQL Server的表中。你可以使用SQL Server监测器捕获运行于SQL Server实例上内存敏感的查询和压力负载。然后，你可以优化这些查询，减少内存消耗。

Windows 性能监测工具

Windows性能监测器(如图2)是微软管理控制台同步功能，它提供一些有用的计数指标，可以帮助跟踪SQL Server内存使用情况。

图2：Windows性能监测器

利用Windows性能监测器可以监视以下三种常见的内存计数情况：

• 系统内存中潜在的低内存条件
• SQL Server内存使用量，我们可以判断SQL Server是否出了问题
• 与内存中OLTP引擎相关联的潜在的低内存条件

对象	计数器	描述
内存	可用量字节数	进程可用内存量；应该永远高于5000KB。
内存	每秒页数	不应该总是超过0。
进程	每秒页故障	出现较高的值表示页过多，可能会导致内存和硬盘出现性能瓶颈。我们必须判断问题是由SQL Server引起的还是由运行在系统中的其它进程引起的。
进程	工作空间	可以用这个参数监视运行在系统中的SQL Server每个实例，判断被SQL Server占用的内存总量。这个值应该总是大于5MB。
SQL Serever内存管理器	缓存命中率	对于大多数应用，这个值应该是90或者更高，它表示大部分数据都需要放到缓存里提供服务。较低的值表示内存有瓶颈或者应用程序设计不良。
SQL Serever内存管理器	总页数	较低的值表示需要安装更多内存了。通常，这里的低值是伴随低“缓存命中率”值而出现的。
SQL Serever内存管理器	服务器内存总量（KB）	如果这个值与系统总内存量比较相对较高，那么就是一个明显的信号，需要增加更多内存了。

图3：展示了最常见的内存相关的性能计数器，如果你遇到了SQL Server内存压力问题，那你应该关注这些指标。

除了这些计数器，还可以监控性能监视器对象SQL Server内存管理计数器中的“内存授予等待”信息。这个计数器表示工作空间中每秒钟等待分配内存的进程总数量。通常，小规模OLTP事务不需要分配较大内存。内存分配值持续大于零是内存出现压力的信号。