SQL SERVER DBA, Linux and Azure: Page Life Expectancy, Buffer Cache Hit Ratio

Page Life Expectancy, Buffer Cache Hit Ratio

 Page Life Expectancy (PLE) in SQL Server:

Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.

If your page stays for more time in the buffer pool your PLE is high which leads to high performance. Every time request comes there are more chances that it may find its data in the cache itself, instead of going to the hard drive to read the data.

PLE measures in seconds.

PLE is one of the performance counters of SQL Server.

Higher the PLE, performance of SQL Server is good.

As per recommendation, the value of PLE counter is around 300 seconds.

You can check this value for your SQL server using below DMV.

SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'page life expectancy'

Buffer Cache Hit Ratio

Buffer Cache Hit Ratio indicates the percentage of pages found in the buffer cache without having to read from the disk.

The ration is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.

Buffer Cache Hit Ratio is one of the performance counters of SQL server.

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.

You can check this value for your SQL server using below DMV.

SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'Buffer cache hit ratio'


No comments:

Post a Comment