SQL Server has excellent feature called Buffer cache or Buffer pool. The buffer manager copies data into memory whenever it is written to or read from the SQL Server database. Because of this, SQL server can read or write data very fast. You can also say that this improves the performance of the SQL server.
Page Life Expectancy (PLE) and Buffer Cache Hit Ratio are performance counters. They are related to the time spent by data pages in this buffer pool.

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.
In simple words, if your page stays longer in the buffer pool, your Page Life Expectancy is high. This leads to high performance. Every time a request comes, it is more common to find its data in the cache. This prevents going again and again to the hard drive to read the data.

• Page Life Expectancy measures in seconds.
• Page Life Expectancy is one of the important performance counter of SQL Server.
• Higher the Page Life Expectancy, better the performance of SQL Server.
• As per the Microsoft’s recommendation, value of Page Life Expectancy counter is around 300 seconds.

You can check the Page Life Expectancy value for your SQL server using below Dynamic Management View (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 shows the percentage of pages found in the buffer cache. These pages are found in the memory and there is no need to read it from the disk.
The ratio is calculated by dividing the total number of cache hits by the total number of cache lookups. This calculation is based on the last few thousand page accesses.

• Buffer Cache Hit Ratio is one of the important performance counter of SQL server.
• You can increase the buffer cache hit ratio in two ways.
  1. One is by increasing the amount of memory available to SQL Server.
  2. The other is by using the buffer pool extension feature.

You can check the Buffer Cache Hit Ration value for your SQL server using below Dynamic Management View(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'

Check all related articles