SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026

[Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white paper, I was corrected by SQL Experts immediately for my error in judgment and incorrect information. I accepted my mistakes and corrected it. I just shared this story with my good friend Tim Radney and he was very kind to give me guidance on this subject. I asked him if he can write further on this topic and help people understand this complex subject in simple words.

Linchpin People are database coaches and wellness experts for a data-driven world. In this 26th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in very simple words what should be the good value for Page Life Expectancy (PLE).

When troubleshooting SQL Server performance issues one of the top places to look is memory. One of the more common methods to check for memory pressure is to check to see the memory counter ‘Page Life Expectancy’ (PLE). The value returned is represented in seconds. A very old and dated recommendation is that this value be 300 or higher which is only 5 minutes. With the standard servers in place today, this value is too low.

What is the proper value to monitor for your server, you may ask, well it really depends on the amount of memory allocated to your instance of SQL. When working with my clients the value I start with is taking (max memory in GB) / 4 * 300. This would be the minimum value I would expect to see. On a server with 32GB allocated to SQL this value would be (32/4)*300 = 2400 seconds or 40 minutes.

For a server with 32 GB of ram, maintaining a PLE value of 2400 or higher is a good value, but what is equally important is to know what the baseline value is for the instance. What is the average value the server has during certain times during the day? If you capture this value and alarm/alert when you have big dips then you will be more equipped to get an early detection of a potential issue. Things like improper indexed queries, large data pulls, etc.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Exit mobile version