Find PLE & Datacache values

I started reading much about PLE (Page Life Expectancy) and Datacache when I had issues with my PLE values. I believe i mostly followed Jonthan’s blog (http://sqlskills.com/blogs/jonathan) for advices and inputs.

– Query to find Datacache Usage (Replace ‘Your_Database_Names’ with the DB Names you like to filter)

SELECT top 5 count (*)*8/1024 AS 'Data Cache Size(MB)'  , db_name(database_id) AS 'DatabaseName'
FROM sys.dm_os_buffer_descriptors
WHERE db_name(database_id) IN('Your_Database_Names')
GROUP BYdb_name(database_id),database_id
ORDER BY 'DatabaseName' desc

– Query to find PLE.

SELECT
object_name,counter_name,cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy'
and OBJECT_NAME=N'SQLServer:Buffer Manager' ;

 As per Microsoft, PLE is supposed to be a minimum of 300. This is modified as per current day requirements by Jon in his blog.

The formula he has mentioned is

Calculated PLE = (DatacacheinGB / 4GB) * 300

If this value doesn’t match with the average values for PLE in your system, then it needs some tuning (which I am also wondering, how to). Well in my case, system was starving for memory and WIN guys added some memory as a part of their proactive monitoring which helped my PLE values to jump up 7 times. Still, I am looking into what can improve the PLE values. I believe this is specific to each environment.

MvM