为什么需要监控buffer cache？
内存使用会对性能产生重大影响。当内存不足时，数据页会经常从缓冲区缓存中清除。这会减慢查询速度，因为 SQL Server 必须到磁盘上查找数据页，将其恢复到缓冲区缓存，然后在返回查询结果之前读取该页。
查询开始运行缓慢的原因有很多。但是，如果您想排除内存问题，请查看缓冲区缓存内部发生了什么。对其内部的窥视将确定哪个数据库、表或索引正在占用内存并对缓冲区施加压力。
要查看哪个数据库消耗最多内存，请使用以下查询：
SELECT
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name, COUNT(1)/128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY megabytes_in_cache DESC;
要识别消耗最多内存的表或索引，请在您要检查的数据库中运行此查询：
SELECT COUNT(1)/128 AS megabytes_in_cache
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY megabytes_in_cache DESC;
使用指标管理内存
虽然抽查数据库和索引是否存在内存过度使用问题，但跟踪缓冲区缓存指标确实是识别和解决内存内部压力导致的性能问题的最佳方法。
以下是要监控以改善与内存相关的性能问题的前五个指标：
- Buffer Cache Hit Ratio
此指标显示 SQL Server 如何利用缓冲区缓存
命中率标识来自缓冲区缓存的数据页完成的页请求与所有数据页请求的百分比
缓冲区缓存中找不到的页面从磁盘中读取，这样会慢很多
理想的缓冲区缓存比率为 100（即 SQL Server 从缓冲区缓存读取所有页面，不从磁盘读取）
推荐的缓冲区缓存值大于 90 - Page Life Expectancy (PLE)
页面预期寿命衡量数据页面在缓冲区缓存中停留的时间（以秒为单位）
PLE 越长，SQL Server 从缓冲区缓存读取页面而不必转到磁盘的机会就越大
如果没有足够的内存，数据页会更频繁地从缓冲区缓存中刷新，以释放新页的空间
从历史上看，当系统的内存比现在少得多时，“正常”的 PLE 值为 300 秒
今天，使用一个公式来确定“好”的 PLE：页面预期寿命 = 服务器上每 4 GB RAM 的页面预期寿命 = 300 秒
如果随着时间的推移进行监测，PLE 应该保持稳定
快速、频繁的减少表明内存问题
下降超过 50% 应立即调查 - Page Reads/Sec (Server Level)
此指标显示一秒内在实例上的所有数据库中发生了多少物理读取（即从磁盘读取）
物理读取既昂贵又缓慢
通过使用更大的数据缓存、智能索引和更高效的查询，或通过更改数据库设计来减少物理读取
推荐值小于90
高于 90 的值表示内存不足和索引问题 - Page Writes/Sec
此指标显示一秒钟内在服务器级别将页面写入磁盘的次数
推荐值小于90 - Pages Input/Sec and Pages Output/Sec (Memory Counters)
Pages input/sec 是每秒从磁盘引入的页面数
Pages output/sec 是每秒写入磁盘以在缓冲区缓存中腾出空间的页面数
Pages/sec 是页面输入/秒和页面输出/秒的总和
如果 pages/sec 值始终超过 50，则需要进行额外调查
健康的缓冲区缓存是优化 SQL Server 查询速度的重要组成部分。尽管内存问题只是会减慢查询响应的几个因素之一，但它们很容易识别和解决。跟踪这五个关键指标可以帮助您将数据页保留在缓冲池中更长时间，这样 SQL Server 就不必在返回查询结果之前浪费时间搜索磁盘。
