Being a production DBA means having to quickly look at a SQL Server and diagnose issue with it. We know that a quick look at perfmon or task manager can tell us of high CPU usage, but what is a good way to quickly look at proper memory usage.

Checking out the Page Life Expectancy (PLE) counter is a great way to check for memory pressure. A value too low for your system indicates that pages are being flushed from the buffer pool too quickly. The longer a page can stay in the buffer pool and be read from memory the better. We all know that having to constantly go back to disk to fetch data is costly.

I won’t get into the specifics of which number is too high or too low for a PLE counter, but I will say that any system in the double digit seconds is bad.  I have seen systems in single digits and some systems that I measure PLE in days.  I have updated my query to reflect those situations. I also like to include the uptime counter just in case I get on a system that SQL was recently restarted, if the server has only been up for 10 minutes, I cannot expect the PLE counter to be more than 10 minutes.

I highly recommending trending this value during the day to monitor usage on your servers. If you find this value consistently low it does not mean you need to rush and add more memory, you should first look at your queries and indexes to make sure you are not reading unnecessary data.  One bad query “select * from bigtable” can crush your PLE counter.

I have included two screen shots showing the results from two servers, one showing in days and one in hours.

Notice on the image above that the PLE is the uptime of the server.

In the image above the server has been up for almost two weeks and has a PLE of 235 minutes.

SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= ‘Page life expectancy’
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd=‘LAZY WRITER’)
      ELSE ”
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE ‘%Manager%’
          AND[counter_name] = ‘Page life expectancy’