The Perils of Secondary Metrics in SQL Server

Posted on by Erik Darling

The Perils of Secondary Metrics in SQL Server

Chapters

Full Transcript

Erik Darling here with Darling Data, and in today’s video, we’re going to talk about one of my least favorite things to have to deal with in SQL Server, and that is secondary metrics. And by secondary metrics, I mean that when someone starts quoting problems to me, things that are going on with the server, there are a vast array of things that they will bring up that are just not really what you want to be looking at to figure out where your problems are, and what your problems are. And I’m going to talk through at least a few of those today, and then, I don’t know, maybe I’ll need to do some more in another video. But down in the video description, you will find all sorts of incredibly helpful links for you to, well, have me, I can come look at your primary metrics, fix stuff for you, right? You can hire me for consulting. If you want to learn a lot about all of the primary metrics that I care about, you can buy my training. There’s a coupon code down in the description below, even. You can become a supporting member of the channel. So right now, funny enough, in the background, I have, this is like one of those, man, we are cooked things. I’ll show you in a second. But in the background, I have Claude Sessions walking through and touching things in the GUI of my performance monitor, and figuring out if there’s problems and if things look good. I’ll show you that in a second. It’s neat. But anyway, if you would like free SQL Server and Postgres performance monitoring, I have just the treat for you. Totally free, open source, all the stuff that you would care about if you were monitoring a SQL Server, and you care a lot about good metrics that indicate you have problems, and having a tool that actually helps you find and solve your problems, not just one that says, I think there’s a problem, or one that says, hey, there’s a problem. If you buy some consulting, I can fix it, right? Because I don’t sell that with that. If you get this, you don’t need me, right? This is free. This is a labor of love, because the people of the world have suffered too long under the yoke of big, of big, awful, ugly, paid monitoring tools, or ones with in-app purchases that suck. So get a good one that’s free and doesn’t try to bend you over. I will be at Pass Summit West in Seattle, Washington from November 9th to 11th. I will be doing a regular session called Read Committed is Cruel by Default. It is all about my least favorite isolation level in SQL Server.

And I have a pre-con where I will be teaching advanced T-SQL that doesn’t suck. So you know, when you get back to your computer and you need to harangue whatever robot companion is being foisted upon you in your performance tuning endeavors or your performance analysis endeavors, you can be better informed and tell them when they’re being stupid. All right. Anyway, let’s just minimize this. Oh, I minimized the whole thing. That was silly of me. I hit the wrong button. So this is what I have going on in the background. This is some stuff that Claude is working on. At various points, you might see the GUI pop up and you might see it walk through. Otherwise, you’re just going to see a bunch of boring text behind me.

But I don’t really have anything else to put on the screen while I talk about this. So I’m just going to ramble off the top of my head a little bit. So secondary metrics, right? Things that, you know, don’t particularly hold any value when you are doing a SQL Server analysis. But still people maintain their value. These things have the, like this advice has the half-life of like your average Twinkie, like 2005, 2008, like mental level mentality.

People just aren’t looking at stuff that, in metrics that better suits the world around them, the realities of hardware changes. It’s all very strange and parochial. And I don’t quite get it. So like one of the, probably the most famous one is page life expectancy.

You know, like you can, you can tell someone like, like it’s just been so ingrained in internet lore and culture around SQL Server that PLE 300 good, PLE not 300 bad, or under 300 bad. Like people just feel very safe repeating it because it is such a meme that, you know, it’s just like, like they can get away with it, right? Like, like no one’s going to be like, oh, well, I mean, go find an article that disagrees, right?

It’s like, it’s just a very safe thing for people to say. Like, and they’ve been trained to do it. And unfortunately are, you know, the, you know, the, the robots, they have been trained in all of the same ways and on all of the same material that, that a lot of these people have.

And so they will, they will also come back and be like, ah, bro, PLE sucks. And you have to patiently explain to them that PLE is not what we care about. Um, instead of looking at PLE or page life expectancy, you should be looking at wait stats.

They are a primary metric, uh, page IO latch waits, uh, will absolutely tell you when, uh, if you are spending a lot of time reading pages from disk into memory, um, and that could be happening either because your server is far undersized for your workload, or it could be because you have, you know, big memory grants, which could be accompanied by resource semaphore waits, uh, that are popping up and see, this is no hands. This is, we are cooked, right?

This thing is just clicking around GUIs, looking at things on its own. Uh, so, uh, uh, it can do all this stuff, right? But it still thinks PLE is a good metric to look at.

Anyway, uh, yeah, look at wait stats. Page IO latch waits tell you when you are, uh, reading pages from disk. They could be accompanied by resource semaphore waits of query memory grants are ganging up on your server.

Otherwise, it could, you know, it could be either, you know, again, your, your, your data is far oversized for the amount of memory you have, or you have queries asking for big memory grants that might be, uh, knocking a lot of buffer pool data out. Uh, disk queue length is probably another, um, another one that comes up, probably not, obviously not as frequently as, um, as, uh, page life expectancy.

But just one of those things where, like, people are like, oh, my disk queue length, it’s huge. And, like, just no one knows what’s good or bad or what a number is. But, like, just modern storage, like, sans, like, like, like, disk queue length probably made a lot of sense to look at when you could, like, hear your hard drive, like, like, you walk into a server room and play your, you’re, you’re up to something and you can even, like, click, click, click.

Uh, well, actually, when it clicks, you have to replace it. But, uh, like, when you, like, when you could point at a drive and say that’s what, like, it had, like, a little, like, sticky note on it, like, L, right? Like, this is where the logs are.

Like, like, that’s when you would have cared about that sort of thing. Sans, NVMe, um, flash drives, you know, anything where storage is sort of, like, obfuscated or, uh, there’s an indirection or, like, an abstraction layer, uh, disk queue length just doesn’t make as much sense to look at anymore.

Um, query books, you know, uh, anytime you’re looking at a query plan with someone, uh, and they start talking about costs and percentages, uh, just hit them with the proverbial fish because those are all estimates, right? They are pre-execution estimates.

They are how you got to the plan you’re looking at. If you’re looking at a query because it’s slow, there’s a pretty good chance that those percentages are wrong, right? Because that you got a plan that didn’t fit, right? So the percentages were off, the estimates were not correct, and you got a plan that was not good for, uh, your, um, not good for your query.

Uh, logical reads are another one that just will not go away. Um, like, I, I absolutely agree that they tell you if your query is doing logical reads. What it’s not telling you is some other important stuff, like, how long did your query run?

Like, they don’t tell you if your query was fast or slow. If you look at CPU and duration, that tells you, that gives you a better gauge of a lot of things. Uh, one, how long the query actually took duration, that’s wall clock time, and B, CPU effort, which, you know, if you’re in the cloud and you’re tuning for a lot and you’re trying to reduce costs, tuning down logical reads isn’t going to necessarily turn down, tune down CPU.

You have to work on the CPU, you know, if you want to get us and use fewer DTUs or downsize your servers, stuff like that. So, like, like, they just don’t tell you fast or slow. And the better, the even better part about looking at CPU and duration is you can make other assessments along the way.

Like, like, is duration much higher than CPU, right? Because that means that something was in the way of your query doing meaningful CPU work. You were not, like, uh, your query just wasn’t able to make progress on CPUs.

If duration was much higher than CPU, if you have a parallel plan and, uh, CPU, is not, like, close to DOP higher than duration, then you had a pretty ineffective parallel plan. So, there are, like, better things to look at, right?

Because just doing reads, like, sure, sometimes it means you can create an index and do fewer reads, but, uh, for me, it’s just, like, not a very interesting thing. If you want to look at any reads metric, physical reads are really the interesting one because if you start looking at queries that do a lot of those, those are most likely the ones that are pushing the page I.O. latch waits that we were just talking about up.

Uh, uh, what else? Um, uh, index fragmentation, uh, that still comes up quite a bit. Um, people get very, very worried about that.

There’s still a lot of panic at the, panic at the, the, the disco about, um, you know, a lot of pages being logically out of order. But, like, a lot, for the same reason that looking at stuff like disk queue length doesn’t make sense on modern storage hardware.

Like, to the, to the, the same point, like, like, caring about logical fragmentation or pages being out of order doesn’t make sense when there’s not a drive physically spinning around. This leads to people doing all sorts of goofy things like tinkering with fill factor.

Uh, it’s another, like, if you’re at the point in your workload tuning process where you’ve taken care of everything else and there’s not a single other problem and you, the only thing left for you to do is go table by table, index by index and figure out if fill factor is your problem, there’s a good chance that you should just move on to something.

Like, like, like, like, you’ve, you’ve, you’ve crossed, like, you’ve made it to the pearly gates of performance tuning if that’s where you’re at with things. Um, I’m not saying that it’s never anything that you should look at, but, like, if you, the chance, like, most people who I run into who are looking at it, you know, boy howdy, they have, like, 15, 20 things that are actually problems that they should be addressing, but they’re just like, no, no, no, no, this fill factor thing, I gotta, I gotta work this out first. Uh, nothing, nothing, nothing, nothing gets done until fill factor is decided. Um, you know, uh, you know, people still put a lot of, um, emphasis on, like, having a lot of missing index requests and, or, like, like the, like the missing index requests themselves. Um, you know, they’re, like, I guess if you look at the total number of missing index requests, you could sort of use that to take the temperature of the server a little bit, but, uh, you know, like most likely you’re better off looking at the tables themselves and you, because, you know, if you’re looking at a table that just has like, uh, like maybe like a clustered primary key and not really any other nonclustered indexes, um, like there’s probably a whole bunch of indexing you could do, but, you know, just like the sheer number of missing index requests, um, you know, you could, I see missing index requests, like, like 10, 15 of them on a table that already has 25 indexes on it. So, like, sure, it probably means that you could adjust your indexes a bit, but, you know, those missing index requests, they’re very opportunistic. They have, they, they’re judged very quickly. They are not well thought out. Um, so you could use them to correlate a little bit, but don’t, like, don’t go crazy overemphasizing on those. Um, let’s see, uh, you know, uh, for wait stats, you know, people still tend to get worked up about CXPACKET waits and they try to, you know, really over-engineer, um, like the, the best max stop and cost threshold for parallelism settings, uh, you know, it’s, it’s, it comes back to, like, something that I’ve said a lot of times in that, like, a lot of the settings that you are setting globally at either the server level or the database level, they’re not going to be perfect for every query, right? That’s maxed up, that’s cost threshold for parallelism, that’s, you know, isolation levels, so many things. Um, they’re, they’re okay for the majority of the workload.

They are reasonable guardrails for most of the queries that run. It’s our job to kind of get out there and figure out, like, which queries need to break those rules, which queries need to abide by a different set of rules in order to run, like, more, run better, right? Just break free. Um, you know, but a lot of people do still look at CXPACKET waits, freak out, and just start changing numbers randomly. They confuse which one is which. The number of servers, uh, I’ve, I’ve looked at that had maxed up set to 50 and cost threshold for parallelism set to eight. Uh, I’m just amusing at this point. Um, maybe a little, maybe not so amusing. Um, I don’t know. I guess there’s still a place in the world for people like us. Um, let’s see. I don’t know. I think, I think that’s probably good enough for, for now. Uh, but again, um, you know, all of these things are, uh, all of, all of the things that we’ve talked, uh, rather, uh, we’ve talked about both sides of it, so I can’t say all of them, but, uh, all the main points are that, um, there are a lot of secondary silly things that people and robots confuse with, uh, good metrics to look at in the SQL Server to judge performance, uh, what you need to do to fix performance problems. And, uh, I just see people messing this up constantly and, uh, you know, running to all the wrong things and not, um, not paying attention and focusing on the stuff that actually makes a difference to queries running better and people being happier with the server in general. So, uh, that’s probably good enough for now. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you, uh, next Tuesday for office hours. All right. Thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *