Page Life Expectancy can tell you whether your SQL Server has enough memory for its bufferpool to function properly. Extremely low Page Life Expectancy values can be an indication of:

• Too little memory on the system
• Too little memory configured for SQL Server’s use
• Poorly written application code

Brian Moran wrote an article for SQL Server magazine on this back in 2004 entitled Page Life Expectancy a Reliable Indicator of SQL Server Memory Pressure that sums up how you can use Page Life Expectancy to determine whether adding memory is warranted, and things haven’t changed since he wrote the article. Essentially, you might find that there is too little memory installed on the server to enable SQL Server to function properly or that SQL Server hasn’t been configured to use enough of what’s available.

Still, you can throw memory at a server all day long (memory’s cheap, right?) but if your application code is behaving badly it might not help. While you might see a temporary improvement, you’ll likely start running into other issues as the application scales. By addressing application-specific problems like stored procedures that are recompiled due to:

• Underlying schema changes
• Statistics changes
• Including SET options in the procedure definition
• Temporary table changes
• Use of the RECOMPILE query hint or OPTION (RECOMPILE)
• Including data definition language (DDL)

Or by addressing queries that read an entire table into the bufferpool to modify a small number of rows, you can save lots of arguments with systems administrators and managers about adding memory, and improve your application’s performance at the same time. Read Microsoft’s technical document Troubleshooting Performance Problems in SQL Server 2005 for details on these and other tips for improving the performance of your SQL Server, and the Microsoft TechNet article Top SQL Server 2005 Performance Issues for OLTP Applications for OLTP-specific tuning tips.