How to find current SQL buffer cache hit ratio and page life expectancy
Buffer cache Hit Ratio:|
Buffer Cache Hit Ratio shows how SQL Server utilizes buffer
ItIndicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.
After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.
How to find current SQL buffer cache hit ratio
Solution :
Step 1: Open QueryWindow
Step 2: Run Commit (to commit all the data from dirty pages to disk)
Step 3: run DBCC DROPCLEANBUFFERS
To deep digger in to the problem: find the below for further trouble shooting. If the SQL Server Memory looks good
After finding the quarries
Based on Query plan needs to
Create Indexes / rebuilding the indexes
Update Status
Page Life expectancy:
Indicates the number of seconds a page will stay in the buffer pool without references.
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
- See more at: http://www.sqlshack.com/sql-server-memory-performance-metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/#sthash.LPsKszhE.dpuf
Buffer cache Hit Ratio:|
Buffer Cache Hit Ratio shows how SQL Server utilizes buffer
ItIndicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.
After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.
How to find current SQL buffer cache hit ratio
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio')
a
,
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base')
b
OR
SELECT
(CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /
CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT)) * 100
AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters
Solution :
Step 1: Open QueryWindow
Step 2: Run Commit (to commit all the data from dirty pages to disk)
Step 3: run DBCC DROPCLEANBUFFERS
To deep digger in to the problem: find the below for further trouble shooting. If the SQL Server Memory looks good
After finding the quarries
Based on Query plan needs to
Create Indexes / rebuilding the indexes
Update Status
Page Life expectancy:
Indicates the number of seconds a page will stay in the buffer pool without references.
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
2
3
4
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
