Friday, September 5, 2014

High PLE high Performance.

Page Life Expectancy commonly called as PLE,  is number of seconds a page will stay in the buffer pool. 
The longer the pages stays in memory cache, the higher the PLE is, the high PLE gives high performance. Each time request comes, first SQL Server will check in the memory cache if it exists or not. If the results or data is found in memory cache (which is also called as buffer pool) decreasing the search time, then it will display the results that is read from cache. If sql server could not find the information in cache then it will look for the data in pages (that is stored in hard disk) now we are increasing the search time. So the high PLE leads to high performance. Below is the query to check the PLE value. 

select 
[object_name],
[counter_name],
[cntr_value] as Current_value
from
sys.dm_os_performance_counters
where
[object_name] LIKE '%Manager%'
and
[counter_name] = 'Page life expectancy'

Also always Buffer cache hit Ratio should be 100 or close to 100.
Below is the query to check the Buffer cache hit Ratio.

Both values can be obtained from Perfmon tool by adding respective counters.

select 
[object_name], 
counter_name, 
cntr_value as current_Value
from 
sys.dm_os_performance_counters
where 
[object_name] LIKE '%Buffer Manager%'
and
[counter_name] = 'Buffer cache hit ratio'

No comments:

Post a Comment