Questions tagged [page-life-expectancy]

Page Life Expectancy is a Microsoft SQL Server-specific term relating to the average amount of time buffer pages can be expected to remain in memory before being replaced.

From MSDN:

Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object. Monitor it every 3-5 seconds or so. There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

PLE can be a measure of how much physical IO your SQL Server is doing.

27 questions
12
votes
1 answer

SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days

I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup: A 2-server SQL 2012 SP1 installation SQL AlwaysOn HA has been enabled for a few…
CRCerr0r
  • 239
  • 2
  • 4
9
votes
2 answers

What does Page Life Expectancy say about the instance?

I have installed monitoring software on a few SQL Server instances in the environment. I am trying to find bottlenecks and fix some performance issues. I want to find out if some servers need more memory. I am interested in one counter: page life…
8
votes
2 answers

Can SQL Server's Page Life Expectancy be too high?

I've read several articles about SQL Server's Page Life Expectancy and what it means and what kind of information you can glean from it. In most of these articles that I've read, a common, healthy PLE value is somewhere around 1000-2000 seconds.…
Ryan
  • 183
  • 1
  • 1
  • 5
8
votes
1 answer

Why is the page life expectancy 0 (zero) on SQL Server 2005

We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked the SQL Server DMV query SELECT…
ttomsen
  • 289
  • 2
  • 6
6
votes
2 answers

DateTime2 and Page Life Expectancy (PLE)

As I understand it, when you define a column on a table you define its precision. This precision takes 1 byte and is stored at the column level. If you use a precision of 5 or more, then a DateTime2 column will take 8 bytes per row. (The precision…
Vaccano
  • 2,550
  • 5
  • 31
  • 56
6
votes
4 answers

How can I Debug a Buffer Issue?

I have a production "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" that is showing weird buffer and page life expectancy (PLE) symptoms. I am running this every minute on my server (to track this issue): SELECT @ple = CAST([cntr_value] AS…
6
votes
2 answers

Buffer Size Changing?

I have a production database that is experiencing wildly fluctuating Page Life Expectancy (PLE) issues. (It crashes to zero at random times.) I have been researching the PLE issue and have found something that seems to point to a VMWare issue, but…
Vaccano
  • 2,550
  • 5
  • 31
  • 56
5
votes
1 answer

What units is Page Life Expectancy in?

I'm currently using the following query to get the Page Life Expectancy from SQL Server: declare @PLE int select @PLE=cntr_value from psql1.master.sys.dm_os_performance_counters where counter_name='Page life expectancy' and…
DForck42
  • 3,068
  • 3
  • 38
  • 67
5
votes
3 answers

How much RAM should SQL Server have

I have a database which takes up 500gb, I have noticed that my ple is low even though I have 100gb of RAM. I have searched in google to find reasons of that and I found some interesting queries. I have a query which runs on very big table even…
whd
  • 61
  • 1
  • 1
  • 9
5
votes
1 answer

Does Resource Governor help protect page life expectancy?

I have several large tables that I need to copy into partitioned copies on a transactional database. I am worried about the hit that this will cause to performance of my production applications. In an attempt to mitigate this, I am looking into…
4
votes
3 answers

SQL Server Memory allocation and max server memory setting

For one of our SQL Server, we recently add more memory. The initial value was 12GB. The server experienced a lack of physical memory so we added another 12GB, for a total of 24GB. Then I have set the followings values : min. server memory 1024MB -…
Mickael
  • 325
  • 1
  • 7
  • 17
4
votes
1 answer

Page Life Expectancy Wildly Fluctuating

For months now, the PLE on one of our servers has hovered around the 2 million seconds. If varied slightly from day to day but was pretty stable there. This last weekend, we added 12 GB of virtual RAM and 1 virtual CPU core to the server. We did…
PseudoToad
  • 838
  • 2
  • 12
  • 23
3
votes
0 answers

Significant PLE Drop

This morning at 4am we experienced a huge drop in Page Life Expectancy on a new sql server 2012 box. No jobs (backup etc) ran at this time and traffic was very light compared to say 20:00. Current DB size is around 20gb but writing at 5gb per day.…
3
votes
4 answers

SQL Server Page Life Expectancy sharp drop

I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far. It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of VMware 5.0. The VM has 16GB of memory with max server…
3
votes
2 answers

Dropping PLE on query

In company we are working in project on very big database. It uses 100GB RAM. What's weird before first running a query PLE is 11k~, after running it drops to about 70, anyway when after 15 mins I check PLE again its about 1k~ and when I run query…
whd
  • 61
  • 1
  • 1
  • 9
1
2