Page Life Expectancy is the number of seconds the average page of data has been in the buffer pool.SQL server keeps data pages in memory for quicker access instead of having to read from disk every time.
Now, keep in mind that Page Life Expectancy doesn’t prove or disprove anything on its own. It’s simply a symptom of potential memory pressure. If you think that your server is suffering from some sort of memory pressure, this is just one piece of a bigger puzzle that can help point you in the right direction.
You can find Page Life Expectancy information in the sys.dm_os_performance_counters DMV, or you can run perfmon. (Hint, it’s a great idea to collect perfmon data indefinitely in order to have historical and baseline information).
If you do a quick google search you might come across some old posts indicating that a value of 300 sec or under is a indicative of a problem. This is outdated information and I suggest you ignore it. In recent years, a more accurate number for Page Life Expectancy has been defined by Jonathan Keyhayias. You would calculate using this formula (Max Server Memory/4GB) * 300.
This will give you a more accurate number as to what your server specific Page Life Expectancy should be. From there, you can view the actual PLE and compare/contrast.
So, potential solutions can be quite simple such as running proper maintenance on fragmented indexes. If you have room in your server and your budget, you can think about increasing the physical memory.
Or you can investigate your buffer pool. Find out which queries are doing the most logical reads and tune them accordingly. This could involve index analysis such as creating new ones, dropping unused indexes, or just making the queries themselves more SARGable.
Remember that PLE isn’t the end all be all of memory pressure. It’s one symptom that can help lead you in the right direction.
#sqlserver #Hardware #index #DBA #bottlenecks #PerformanceTuning