January 30, 2023 at 12:00 am
Comments posted to this topic are about the item The Complexity of Metrics
January 30, 2023 at 4:38 am
Great article. PLE reminds me of the "tree falling" riddle...
"If a tree falls in the forest (PLE crashes and starts coming back up) and no one was there to hear it (no one was watching Perfmon or whatever to see it happen), did it actually make a sound (did it actually cause a perceptible issue)"? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 30, 2023 at 6:58 am
People coming to SQL Server from a DB2 background may consider PLE to be a significant metric. But on DB2 the DBA can create multiple buffer pools and assign objects to a specific pool.
I remember back in the late 90s on DB2 setting up a small pool dedicated to lookup tables, another for the big sequentially-read beasties, with a third pool for the rest. PLE for the lookup pool was typically over an hour, with almost 10% of logical reads needing under 2% physical reads. The beastie pool had very low PLE as the tables in it mainly had start-to-end sequential reads and would never fit well into memory. In this one a logical read often resulted in a physical read, but pre-fetch kept response times acceptable. The third pool behaved much the same as any SQL Server buffer pool.
The main reason for saying this is to make the point that a single buffer pool cannot give a good PLE for every object that might be in it. Most organisations will have the big beasties that flood the pool and lower PLE and cause high-use lookup tables to be re-read. A good PLE for one object is a sub-optimum PLE for another.
A consistantly low PLE can be a sign that the server will benefit from more memory, but a variable PLE can be a sign that life is proceeding normally. The key to knowing if your PLE is right for you is to know your data, know your performance, and know your SLAs.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 30, 2023 at 2:58 pm
Ahhhh.... PLE.
I'm to the point where I actively dislike this metric. Not because it's problematic in and of itself. Instead, because of that doggone perpetual bit of "knowledge" that your PLE has to be above 300. Great googly moogly, that just ain't so and no matter how many times you talk people through it, it's still out there.
Heck, on a customer call I had someone argue with me that PLE is one of the single most important metrics. I didn't do it, but I almost, almost, got into a "do you know who I am" moment. Thank the gods I didn't (it would have been so very wrong). Their conviction was firm.
People honestly believe this metric is important, and nothing we do is going to change that any time soon I think. But, as Steve points out, it's part of the complexity we're dealing with in trying to understand SQL Server behaviors. No one metric can tell the tale. Which means, people have to learn more, making it all harder to do and understand. I get the desire for simplicity. It's just not there, sadly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2023 at 3:21 pm
Heh... another "metric" that I'm really sick and tired of is "index fragmentation", especially when it comes to 3rd party vendors, both good and bad. You call them and ask them for help and one of the first things out of their mouths is a request to provide proof that the indexes all have no more than 5% fragmentation. First, that's almost impossible because some indexes ("Sequential Silos" in particular) fragment to the max almost instantaneously. Second, it can and does seriously hurt performance because the use of Reorganize can and frequently does change the page density in such a fashion as to increase the page-split rate and perpetuate it.
Worst yet, they insist on that foolishness even when the issue being reported has NOTHING to do with performance.
Ironically, for performance issues, I've become quite willing to do exactly as they request because {insert drum role here} it actually does make the performance worse and makes my point about their performance challenged code even more pronounced. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2023 at 12:20 pm
I seem to remember that PLE was one of those metrics that you have to use in conjunction with something else and with an understanding of your system.
I also remember something about your server being up and running long enough for certain metrics to be worth anything.
A large data warehouse is likely to have a low PLE simply because the size of the warehouse > the size of memory.
Period end reporting suddenly hits data that has been dormant for the period and therefore not in the cache.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply