為什么需要監(jiān)控buffer cache采盒？
內(nèi)存使用會對性能產(chǎn)生重大影響旧乞。當(dāng)內(nèi)存不足時(shí)，數(shù)據(jù)頁會經(jīng)常從緩沖區(qū)緩存中清除纽甘。這會減慢查詢速度良蛮，因?yàn)?SQL Server 必須到磁盤上查找數(shù)據(jù)頁，將其恢復(fù)到緩沖區(qū)緩存悍赢，然后在返回查詢結(jié)果之前讀取該頁决瞳。
查詢開始運(yùn)行緩慢的原因有很多货徙。但是，如果您想排除內(nèi)存問題皮胡，請查看緩沖區(qū)緩存內(nèi)部發(fā)生了什么痴颊。對其內(nèi)部的窺視將確定哪個(gè)數(shù)據(jù)庫、表或索引正在占用內(nèi)存并對緩沖區(qū)施加壓力屡贺。
要查看哪個(gè)數(shù)據(jù)庫消耗最多內(nèi)存蠢棱，請使用以下查詢：
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;
要識別消耗最多內(nèi)存的表或索引，請?jiān)谀獧z查的數(shù)據(jù)庫中運(yùn)行此查詢：
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;
使用指標(biāo)管理內(nèi)存
雖然抽查數(shù)據(jù)庫和索引是否存在內(nèi)存過度使用問題甩栈，但跟蹤緩沖區(qū)緩存指標(biāo)確實(shí)是識別和解決內(nèi)存內(nèi)部壓力導(dǎo)致的性能問題的最佳方法泻仙。
以下是要監(jiān)控以改善與內(nèi)存相關(guān)的性能問題的前五個(gè)指標(biāo)：
- Buffer Cache Hit Ratio
此指標(biāo)顯示 SQL Server 如何利用緩沖區(qū)緩存
命中率標(biāo)識來自緩沖區(qū)緩存的數(shù)據(jù)頁完成的頁請求與所有數(shù)據(jù)頁請求的百分比
緩沖區(qū)緩存中找不到的頁面從磁盤中讀取，這樣會慢很多
理想的緩沖區(qū)緩存比率為 100（即 SQL Server 從緩沖區(qū)緩存讀取所有頁面量没，不從磁盤讀扔褡）
推薦的緩沖區(qū)緩存值大于 90 - Page Life Expectancy (PLE)
頁面預(yù)期壽命衡量數(shù)據(jù)頁面在緩沖區(qū)緩存中停留的時(shí)間（以秒為單位）
PLE 越長，SQL Server 從緩沖區(qū)緩存讀取頁面而不必轉(zhuǎn)到磁盤的機(jī)會就越大
如果沒有足夠的內(nèi)存殴蹄，數(shù)據(jù)頁會更頻繁地從緩沖區(qū)緩存中刷新究抓，以釋放新頁的空間
從歷史上看，當(dāng)系統(tǒng)的內(nèi)存比現(xiàn)在少得多時(shí)袭灯，“正炒滔拢”的 PLE 值為 300 秒
今天，使用一個(gè)公式來確定“好”的 PLE：頁面預(yù)期壽命 = 服務(wù)器上每 4 GB RAM 的頁面預(yù)期壽命 = 300 秒
如果隨著時(shí)間的推移進(jìn)行監(jiān)測稽荧，PLE 應(yīng)該保持穩(wěn)定
快速橘茉、頻繁的減少表明內(nèi)存問題
下降超過 50% 應(yīng)立即調(diào)查 - Page Reads/Sec (Server Level)
此指標(biāo)顯示一秒內(nèi)在實(shí)例上的所有數(shù)據(jù)庫中發(fā)生了多少物理讀取（即從磁盤讀纫陶伞）
物理讀取既昂貴又緩慢
通過使用更大的數(shù)據(jù)緩存捺癞、智能索引和更高效的查詢，或通過更改數(shù)據(jù)庫設(shè)計(jì)來減少物理讀取
推薦值小于90
高于 90 的值表示內(nèi)存不足和索引問題 - Page Writes/Sec
此指標(biāo)顯示一秒鐘內(nèi)在服務(wù)器級別將頁面寫入磁盤的次數(shù)
推薦值小于90 - Pages Input/Sec and Pages Output/Sec (Memory Counters)
Pages input/sec 是每秒從磁盤引入的頁面數(shù)
Pages output/sec 是每秒寫入磁盤以在緩沖區(qū)緩存中騰出空間的頁面數(shù)
Pages/sec 是頁面輸入/秒和頁面輸出/秒的總和
如果 pages/sec 值始終超過 50构挤，則需要進(jìn)行額外調(diào)查
健康的緩沖區(qū)緩存是優(yōu)化 SQL Server 查詢速度的重要組成部分。盡管內(nèi)存問題只是會減慢查詢響應(yīng)的幾個(gè)因素之一惕鼓，但它們很容易識別和解決筋现。跟蹤這五個(gè)關(guān)鍵指標(biāo)可以幫助您將數(shù)據(jù)頁保留在緩沖池中更長時(shí)間，這樣 SQL Server 就不必在返回查詢結(jié)果之前浪費(fèi)時(shí)間搜索磁盤箱歧。
