SQL Server内存

Available MBytes

该计数器监测还有多少可用内存，是否操作系统存在内存压力。一般我们调查是否这个计数器持续在500MB以下，这说明内存过低。如果持续低于500则说明你需要增加更多的内存。

这个计数器不能通过T-SQL查询，只能通过性能监视器观察。

Buffer Cache Hit Ratio

缓冲命中率，这个计数器记录平均多少频率从缓冲池中取得数据。我们在OLTP数据库中一般这个比率是90%-95%（该数值经由@MSSQL123 指出发现是错误的，再次进行修改）。由于sqlserver 把预读也作为缓冲比例，所以导致该值很高，所以该计数器只做理解，不能作为真实性能瓶颈参考了。如果该计数器持续低于90%，则需要增加内存。

在可以使用下面的T-SQL语句查询：

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%Buffer Manager%‘ AND [counter_name] = ‘Buffer cache hit ratio‘

Target & Total Server Memory

服务器当前总内存（buffer）以及目标内存，在缓冲池初始化增加内存的时候，总内存会比目标内存稍低一点。这个比例会逐渐接近1，如果总内存没有增长很快，就会显著低于目标内存，这就表示如下两点：

1)  你可以分配尽可能多的内存，SQL能缓存整个数据库到内存中，然后如果数据库小于机器内存，内存不会完全用光，在这种情况下，总内存将永远小于目标内存。

2)  SQL不能增加缓冲池，比如系统内存有压力。如果这种情况你需要增加最大服务器内存，或者增加内存来改善性能。

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%Memory Manager%‘ AND [counter_name] IN (‘Total Server Memory (KB)‘,‘Target Server Memory (KB)‘)

Memory Grants Pending

这个计数器测量等待内存授予的SQL的进程数量。一般推荐阈值为1或者更少。如果大于1这说明内存不足按顺序等待内存释放再操作SQL。

一般工作中出现这种等待可能是由于糟糕的查询，缺失索引，排序或者哈希引起的。为了查明原因可以查询DMV --sys.dm_exec_query_memory_grants 这个视图，将会展示哪一个查询需要内存授予执行。

如果不是以上原因引起的内存等待，则需要增加内存来解决这个问题。此时就有理由增加硬件了。查询的T-SQL语句如下：

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%Memory Manager%‘ AND [counter_name] = ‘Memory Grants Pending‘

Pages/sec (Hard Page Faults)

这里也使用数据库级别计数器：当需要读取或写入的页不在内存中，需要到磁盘中读取时计数。这个计数器是一个记录读和写的总和并且不能直接在内存中获取只能从因盘中读取（导致resulting in hard page faults），这个问题是由于操作系统必须交换文件在磁盘上，当访问内存时，内存不足则需要交换文件到磁盘上，由于磁盘读写速度远低于内存，性能就会受到严重影响。

对于这个计数器，推荐阈值为<50（或者某个稳定值）,如果看到高于这个值，不过需要注意，只要这个值能够稳定在一个较低的水平，没有持续性的大批量数据的写入（磁盘）于读取（从磁盘载入内存），都可以接受。相反，如果长期在一个高位水平，并且观察到PLE不能稳定在参考值范围内，说明内存可能存在瓶颈。当然，如果数据库备份或者还原，包括导出、导入数据以及内存中映射文件等等这些也会导致性能计数器超出某个稳定值。

Batch Request & Compilations

该计数器包含两个检查

• SQL Server: SQL Statistics – Batch Request/Sec.  传入查询的数量（批处理数量）
• SQL Server: SQL Statistics - Compilations/Sec.  新建立的执行计划数量

如果Compilations/sec是25%或者相对Batch Requests/sec更高，则执行计划将被放到缓存中，但是永远不会重用执行计划。宝贵的内存就被浪费了，而不是缓存数据。这是糟糕的实践，我们要做的就是阻止这种情况，

如果Compilation/sec 很高比如100，表示有大量的即席查询正在运行。这时可以启用“optimize for ad hoc”把执行计划缓存，但是只有在第二次查询时才能被使用。

使用如下T-SQL可以得到相应的指标:

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%SQL Statistics%‘ AND [counter_name] = ‘Batch Requests/sec‘;

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%SQL Statistics%‘ AND [counter_name] = ‘SQL Compilations/sec‘;

同样可以获得比率:

SELECT ROUND (100.0 * (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%SQL Statistics%‘ AND [counter_name] = ‘SQL Compilations/sec‘) / (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE ‘%SQL Statistics%‘ AND [counter_name] = ‘Batch Requests/sec‘) ,2) as [Ratio]

关于如何设定数据库可用的内存最大值，如图所示：

推荐阈值：一般来说，我都是采用10%用于操作系统其它90%分配给数据库。当然如果内存很大可以调整这个比例小于1/9，对于内存较小的通常我都预留4-6G左右给操作系统。

我们看一下实际例子：

在性能监视器中看一下这个计数器，我们可以看到这个服务器处于健康状态下，有11GB的可用空间，没有PageFaults（I/O只从缓存中没有交换到磁盘），缓冲的比率为100%，PLE超过20000s，没有内存等待，充足的总内存和较低的编译比率（编译数/查询数）.

这个测量数据很容易理解，这要比任务管理器更具有作用，能依据此做出判断是否有足够的内存在这台SQL Server服务器上。

总结

    如果只根据任务管理器来做出判断，我们很容易出现错误决定。因为不管系统多少内存，SQL Server 会尽可能的使用占用内存，这不是bug。缓存数据在内存中有很好的效果，意味着服务器是健康的，也为用户提供了更好的执行效率。在实际数据库环境中，一般突然遇到的性能问题多半是因为T-SQL语句引起的，就如我前面提到糟糕的查询（缺失索引、排序、哈希等等），这个时候通过语句优化可以很好的解决突发问题，这里就不详解了。如果服务器普遍存在文章中出现的内存性能计数器问题，那就写报告提交内存增加需求吧。

SQL Server内存

标签：接受   sys   gpo   bsp   div   exe   文章   pen   lan