What is Page Life Expectancy?

Published On: 2019-10-14By:

If you’ve been a DBA for a while, you’ve heard people talking about PLE.  PLE stands for Page Life Expectancy; most DBAs (and really anyone that works with SQL Server for more than 5 seconds) called it PLE. I think this is for two reasons; 1 Expectancy is hard for a lot of people to say; 2 we’re lazy efficient.

PLE

PLE is reported by SQL Server as a number, somewhere between 0 and ~2B.  The number that it’s showing you is a number of seconds.  Those number of seconds are the amount of time that SQL Server estimates that it will be able to keep a page loaded in memory for before that page is swapped out.  So if a page of data was loaded into memory right now, and PLE was 100 seconds, then in ~100 seconds we could expect that page to be removed from the buffer pool in order to make room for another page.

I want to be really clear here, 100 is not a target number (neither is 300). It’s just a number that I am using for my explanation here.

When you look at PLE on a larger server, you may see a few numbers. That’s because each NUMA node has a PLE value, and those numbers are calculated together to get the servers PLE.  The actual calculation isn’t an average or anything quite that simple.  You take the PLE value of the node and multiply it by 1000.  You do this for all the NUMA nodes. Then you sum up these values, and divide the total by the number of NUMA nodes, then divide by 1000.

This means that PLE for one NUMA node can drop, while PLE for another NUMA node can be stable, and the server-wide PLE number may not change (or change much).

The reason that each NUMA node has its own PLE value is that SQL Server manages each NUMA node independently.  Understanding why PLE for each node can vary requires an understanding of how SQL Server works.  So, let’s dive a little deeper.  (For this article we’re going to assume a 2 CPU server, which has 2 NUMA nodes. It keeps things easier, but all this applies if you have 4 or 8 nodes as well.)

So let’s calculate PLE for our sample server. If both NUMA nodes have a PLE of 100, then the math looks like this.

((100 * 1000) + (100 * 1000)) / 2 / 1000 = 100

If one node has a PLE of 100 and the other has a PLE of 60, then the math looks like this.

((100 * 1000) + (60 * 1000)) / 2 / 1000 = 80

If you have a four-node box, then the value gets even odder looking. Let’s assume we have three NUMA nodes with a PLE of 100 and one node with a PLE of 60 and see how the math looks.

((100 * 1000) + (100 * 1000) + (100 * 1000) + (60 * 1000)) / 4 / 1000 = 90

NUMA assignments

When a table is loaded into memory, that table is assigned to a NUMA node; and that’s the only NUMA node that the table will be assigned to.  The reason for this is that SQL Server only wants to assign CPU processors from that NUMA node to work on that table.  Because of this, if there’s a report against that table (or a bad query that causes a scan of the table), that’ll impact the PLE of that NUMA node because the table needs to be loaded into memory so that the CPUs can process that table. (All sorts of things can cause PLE issues to crop up, this is just one example.)

So if we’ve got one really large table in our database, and lots of small tables (and everything in SQL Server and the OS set correctly) we should expect to see two very different numbers for PLE. One for the NUMA node that is working on the large table, and one on the NUMA node that’s hosting all the smaller tables.

You may be asking yourself how can I assign tables to a specific NUMA node, and the answer is that you can’t. Even if you could, there’s pretty much no reason to. SQL Server does a really good job of assigning tables to NUMA nodes all by itself.  If you think you’re having a problem with SQL Server because it’s assigning to many tables to a NUMA node, you’re probably got other problems that need to be dealt with (hint, we can help you with that).

PLE Recommendations

There are lots of PLE recommendations out there. Most of them are built on the number 300 which you’ll find in Books Online. Any advice about PLE, which has a specific number as a target, is worth the amount of money you paid to read the blog post (if you did pay to read that number I’m sorry you got screwed).  The target PLE is very system-specific, and it should be treated as such. Some systems should have a really low PLE because they churn data so often.  Other systems need to have a really high PLE so that they never have to hit the disk.

One of our clients has a PLE of somewhere around 35,000. If it gets below 28,800 (8 hours) we start to worry.  When we were designing this system, we decided that when customers ran the end of day reports (the customers are only open for 8 hours a day) for their office, we didn’t want to hit the hard drive. That means that all the data over the last 8 hours needed to be loaded into memory and it needs to stay there until the end of the day.  Is this a guarantee that there are no queries that will hit the disk at the end of the client’s day? No, of course not, but we’ve done everything we can to make this a reality for the client.  And given that this client has thousands of customers, and tens of thousands of unique users per day (if not more) and the system has been stable for years, we’ve done a pretty good job getting to that point.

So what number should you shoot for when looking at PLE?  As high a value as you can afford to get. Increasing PLE is a matter of index tuning and buying more RAM.  If you can afford to get more RAM, get it.  The more data that SQL Server can cache, the higher PLE is going to go, and the happier SQL Server will be.

Wrap-Up

While you probably don’t need to know what PLE is in the long run, it’s one of those things that you’ll want to keep an eye on, because if it drops you want to know why. PLE dropping isn’t a problem or even a symptom of a problem; unless it is. It’s a potential warning that some investigation on the server needs to be done and from that investigation, you might need to do more, or you might not.

Denny

The post What is Page Life Expectancy? appeared first on SQL Server with Mr. Denny.


Contact the Author | Contact DCAC

Cloud Field Day–Solo.io #CFD6

Published On: By:

As I mentioned, I was in Silicon Valley a couple of weeks ago for an analyst event, and got to meet with a variety of the companies. The final company we met with on Friday, was Solo.io, and I have to say they knocked it out of the park. Their technology was super interesting and their founder Idit Levine, and their CTO, Christian Posta were excellent presenters who were clearly enthusiastic about their product.

SoloIO

So what does Solo.io do? In the modern microservices oriented world, we have distributed systems which are nearly all API driven. Solo.io has a number of products in this space, but their core product Gloo is a modern API gateway that securely bridges modern applications like Lamba or Azure functions to both legacy monolithic applications as well as modern databases running in Kubernetes pods.

They also have another open source project called SuperGloo, which is an abstraction layer for service mesh architecture. A service mesh provides modern applications with monitoring, scaling, and high availability through APIs rather than discrete appliances. Istio from Google is best known tool in this space, and SuperGloo can work with it, and other service meshes in the same architecture.

The other really interesting tool that Solo.io highlighted was called Squash, which is a debugger for distributed systems. If you’ve ever tried to troubleshoot a distributed system, even figuring out where to start can be challenging. By acting as a bridge between Kubernetes (drink) and the IDE, you can choose which pods or containers you are debugging and set breakpoints, or change variables during runtime.

 


Contact the Author | Contact DCAC

I’m Speaking at Microsoft Ignite 2019

Published On: 2019-10-09By:

I’m happy to be speaking at Microsoft Ignite this year. I have an unconference session and a regular session, both focused on accessibility in the Power Platform.

The regular session, Techniques for accessible report design in Microsoft Power BI, will be Wednesday, November 6 at 2:15pm. In this session I’ll discuss the features available in Power BI for making accessible reports and demonstrate techniques for making your reports easier to use. This session will be recorded, so if you can’t make it to Ignite, you can catch it online.

My unconference session, Accessibility in the Microsoft Power Platform, is a chance to have a discussion about accessibility in Power BI and PowerApps. It will be held on Thursday, November 7 at 10:45 am. Unconference sessions at Ignite include facilitor-led discussion and exercises that encourage audience participation where everyone can share their experiences and opinions. If you will be at Ignite and want to share struggles or successes in improving accessibility or raising awareness of accessibility issues, please join me.

This year at Ignite there is a reservation system for unconferences. You can RSVP while you are building your schedule on the website. Walk-ins will be accepted just before the session, assuming there is room. But please RSVP if you want to be sure to get a seat in an unconference session. Unconference sessions are not recorded, so this will be an in-person session only. But I will post materials through the Ignite website once the session is over.

If you will be at Ignite, please stop by and say hello and meet Artemis the Power BI accessibility aardvark.


Contact the Author | Contact DCAC

I’m Speaking at Microsoft Ignite 2019

Published On: By:

I’m happy to be speaking at Microsoft Ignite this year. I have an unconference session and a regular session, both focused on accessibility in the Power Platform.

The regular session, Techniques for accessible report design in Microsoft Power BI, will be Wednesday, November 6 at 2:15pm. In this session I’ll discuss the features available in Power BI for making accessible reports and demonstrate techniques for making your reports easier to use. This session will be recorded, so if you can’t make it to Ignite, you can catch it online.

My unconference session, Accessibility in the Microsoft Power Platform, is a chance to have a discussion about accessibility in Power BI and PowerApps. It will be held on Thursday, November 7 at 10:45 am. Unconference sessions at Ignite include facilitor-led discussion and exercises that encourage audience participation where everyone can share their experiences and opinions. If you will be at Ignite and want to share struggles or successes in improving accessibility or raising awareness of accessibility issues, please join me.

This year at Ignite there is a reservation system for unconferences. You can RSVP while you are building your schedule on the website. Walk-ins will be accepted just before the session, assuming there is room. But please RSVP if you want to be sure to get a seat in an unconference session. Unconference sessions are not recorded, so this will be an in-person session only. But I will post materials through the Ignite website once the session is over.

If you will be at Ignite, please stop by and say hello and meet Artemis the Power BI accessibility aardvark.


Contact the Author | Contact DCAC
1 2 3 435

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link