CODEWITHSUNDEEP
sql-server
Mohamed El-Touny
Mohamed El-Touny

Reputation: 347

Set Page Life Expectancy for SQL Server 2012

I get this error from SCOM for Monitoring SQL Server 2012.

Alert: SQL DB Engine 2012 Page Life Expectancy is too low Source: MSSQLSERVER Path: SCOM-01.. Last modified by: System Last modified time: 9/17/2015 11:33:18 AM Alert description: Page Life Expectancy of SQL instance "MSSQLSERVER" on computer "SCOM-01.." is too low.

I need to know how to extend the Page life for MS SQL Server 2012. Any notes will be great.

Upvotes: 0

Views: 17332

Answers (2)

Charlie_M
Charlie_M

Reputation: 86

I see no answer has been marked as the best one, so let me try and help.

As described in this article http://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/:

The only true answer to the PLE question is that a good value of PLE is one that represents optimal server performance with enough headroom to account for growth and spikes in usage. For example, let’s take the server from earlier, which has 163GB of memory devoted to the buffer cache, an average PLE of 2000 seconds, and the extrapolated throughput of 83MB/second.

Hope I helped.

Upvotes: 5

Simon Byles
Simon Byles

Reputation: 36

Page Life Expectancy (generally shortened to PLE) is a SQL Server performance counter that measures how the number of seconds that a page of data will stay in memory once it has been read from the disk. This alert is telling you that the SQL Server you are monitoring has memory pressure.

The PLE will periodically drop on most SQL Servers. This is because, whenever a query runs that requires data to be read from disk, the oldest pages will be removed from the buffer pool to make space. You can see this by monitoring the "Buffer Manager:Page Life Expectancy" Perf Mon counter (or the "Buffer Node:Page life expectancy" for each NUMA node on a NUMA machine).

The traditional advice from Microsoft used to be that the PLE should remain above 300 seconds (i.e. 5 minutes.) However, this is by no means a hard-and-fast rule and the ideal values will vary according to the server's workload. The situation also more complicated for a NUMA server with multiple NUMA nodes

For more information, I would recommend speaking to your in-house DBAs (if you have them). Also see the following references:

http://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy

http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

Upvotes: 1

Related Questions