SQL Server Performance Office Hours Episode 74

Chapters

00:00:00 – Introduction
00:03:15 – Query Store Basics
00:06:28 – Query Store Configuration
00:09:47 – Performance Impact of Query Store
00:12:38 – Top and Row Goal Queries
00:14:57 – Parallelism in Top and Row Goal Queries
00:16:43 – Conclusion

Full Transcript

Erik Darling here with Darling Data, and it is another fine Tuesday here in the Darling Data household, the greater Darling Data area, and we are going to answer five user questions, because, you know, what else, what else are we going to do on a Tuesday? One of the most useless days of the week, right? Until you get to Wet Wednesday and Thirsty Thursday, you are just struggling. Down in the video description, you’ll see all sorts of helpful links, where you can hire me for consulting, buy my training, support this channel with as few as four buckaroos a month, ask me office hours questions, just like we’re doing today, you have the power to ask me questions of your own volition, your own, whatever, your imagination is the limit, my friends. And of course, if you enjoy this channel and the things that we get up to over here, please do like, subscribe, tell a friend.

Or punish a foe, I don’t know, whatever, whatever you want to do here. My free SQL Server performance monitoring tool, which has gone Enterprise Edition, we now offer a headless collector with a separate viewer and all sorts of other good stuff, so you can monitor millions of servers all at once without having to worry about it. Totally free, totally open source, no commitments, no emails, no phoning home with metrics.

I don’t want your metrics unless you’re giving me money. That’s money for metrics, I’m not looking at your crap. I have enough crap to look at.

All the valuable stuff that you would ever want to collect to monitor performance on a SQL Server. And there are built-in read-only, heavily contracted MCP tools, if you want to let your robot friends talk to your monitoring data and give you some phony baloney. That’s where we’re at in the curve again.

We are circling all over the place here. But let’s answer these questions. Let’s get these out of the way so we can carry on with the important work that we have to do in our lives. And here we are.

Why does SQL Server sometimes ignore indexes that seem perfect for the query? Every time I get a question like this, the answer is always the same thing. Optimize our costing, right?

It thought that using a different index would be cheaper. There’s no real reason for it aside from that. The optimizer looked at your indexes, thought about them, and said, I think this one’s the cheapest to go with.

And that’s what it chooses. I suppose, I’m trying to think. RavenDB gives you more specific reasons why.

An index was chosen, but SQL Server doesn’t have that. And I get it. Because the reason is always costing.

So what is valuable for you to do in these cases is take the query and test it, forcing the index that you think would be cheaper. You’ll see that the cost is higher if that index is chosen. I suppose there might be some narrower reasons where maybe the optimizer didn’t work.

I suppose there might be some narrower reasons where maybe the optimizer didn’t work. There might be some narrower reasons where maybe the optimizer didn’t work. Yeah, run the query, force the index, you’ll see that the cost for using that index is going to be higher, for one reason or another. And look at the ensuing plan shape, see where those costing goes.

But more importantly, more importantly, most importantly, for you, run the query and get an actual execution plan testing with the the query plan that SQL Server naturally chooses and where your index is forced. And if if forcing your index results in a faster query plan, that just might be the fix you need to do. Because SQL Server is pretty unlikely to change its mind about that.

Now, one thing that I will point out here is, you know, of course, forcing indexes is not always possible for people, right. But, and one hint that I am, so in the case where maybe, let’s say you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, and you have two indexes, nonclustered indexes on a table and one of them there’s one that you think would really help the query and when you force that index you get a seek with some other query plan and it’s faster and in the alternative version the one that sql server naturally chooses there is a scan and the the query is slower you might be more comfortable using a force seek hint on the table than choosing a specific index often the force seek hint will evaluate to the correct index or another competitively structured index and use that one to to seek into rather than the scan plan that’s that’s my advice there all right oh man how can logical reads go down but total runtime still go up logical reads is a stupid metric you i i’ve never tuned a query based on logical reads and uh well i want to say i have not tuned a query based on logical reads since probably 2014. uh i care about cpu and i care about duration right cpu is a good measure of how much effort is going into the query and duration is a great measure of how long the query takes right and there are all sorts of interesting things that you can look at within that right if you have a series of queries that you can look at within that right if you have a series of queries that you can look at within that right if you have a series of queries that you can look at within an editorial query and uh an editorial query and uh duration is much higher than cpu there’s some other work going on in an editorial query and uh duration is much higher than cpu there’s some other work going on in there right either maybe reading from disk uh physical reads not logical reads uh you might be getting blocked in some way there might be some other resource that uh is getting chewed on uh on your server right um in a parallel plan if cpu and duration are neck and neck then you might have very ineffective paralysm but to me cpu and duration are the two most interesting mechanisms using a compute and a dummy i flipped that i’ve used it for decades as a method to run real-time ыш that a query emits. Now, you might be tuning for other things, right? Like memory grants at some point, like memory usage might at some point come into play when you’re tuning a query. If you have to make sure that this query no longer asks for a giant memory grant that blows the server up when it’s run concurrently and wipes out your buffer pool and all that. But to me, just for like find queries that need help, CPU and duration are it for me. Logical reads, not in like over a decade.

I don’t think. It’s old folks home stuff, right? Antiques village metrics. In your experience, what are the hardest performance problems to diagnose? Well, I mean, I think maybe diagnose is not the right way that I would go with this. My main problem, the hardest performance problems to figure out are the ones that go unobserved.

If you can’t see a problem, you can’t solve a problem. And like diagnosing things once you can see a problem becomes light years easier. But I think, you know, things that are hard to diagnose are things that, you know, like feel like the database, but aren’t the database. So like a connection timeout or like an underpowered app server where like the queries and SQL Server are constantly, but then like people on the application side are just sitting there like, what’s going on?

I’ve been waiting 30 seconds. And you’re like, the query’s done. It’s sitting there waiting on async network IO. This is nonsense, right? Like what’s wrong with you? Stuff like that. You know, there’s also maybe some like, like really internals-y stuff. You know, like if you get really, get into some really weird situations around like latches and spin locks, those can be like, those usually aren’t first on the checklist. So you go through a lot of other stuff before you get to them. But as far as like diagnosing, really, it’s like, I feel like that’s pretty easy once you have the correct things observed. I think the, like the question for me is always, what do I need to observe in order to diagnose this? And sometimes figuring out what you need to observe is the toughest. Especially if a problem happens like randomly and rarely, and you’re like, oh, wait a second. This is all going horribly.

Um, I had a client recently where, um, they had an AG and the primary node in the AG would, uh, like abruptly, uh, just like, like bankrupt on memory and performance would tank and they would fail over to the secondary and the secondary would be fine forever. And they were like, oh, okay, I think we’re safe. We’re going to fail back over to the primary. Primary be cool for like a few days a week. Then out of nowhere, tank, right? And I was looking at the two certs. I was like, oh, you know, there are two servers and the primary had in memory, temp DB, metadata on and the secondary didn’t. And I was like, well, you should change that. And I bet your, your stuff goes away.

No, this isn’t a knock against in memory temp DB metadata. There are, there are bugs and there are weird things that can happen with it. Most people don’t run into them. These folks just happen to have quite a lot of memory and have quite attempt DB heavy workload. And, um, they, they ensure they have a Microsoft support ticket now.

Uh, like, like I could die. Okay. diagnose a problem but i can’t solve that problem i could say use less temp tables or use fewer temp tables but you know who wants to hear that like i gotta rewrite all this stuff bug report right which is which was the right thing to do because that shouldn’t happen you should performance features should not cause performance problems so there we go um let’s see here when does query store become more dangerous than helpful uh you know there’s some workloads that the query store like you have to have a really crazy batch request a second or even be like like compilation batch request would probably do it uh workload for query store to um to beat you up microsoft has in fairness to be fair microsoft has done quite a bit to make query store less of a burden since its inception in 2016 um and so i don’t really find uh many people uh having problems with uh query store the way they used to there are some things that i if you if you do have a very heavy workload there are some things that i do recommend though um i think turning off query store weight stats is uh is pretty much a must if you have a if you have a heavy workload uh collecting those is not fun and i find quite often they don’t add a lot to the query tuning picture um sometimes they’re useful because you’ll see stuff like buffer io in there you’ll see stuff like um like lock weights in there but like if you know like most of the time it’s just like some cpu and parallelism you’re like oh wow well i knew that i can see that from the duration and cpu i knew that it’s obvious right um like even buffer io you’re like yes i see the physical reads that’s that’s great even locking if if if duration is much longer than cpu you’re like well something happened in there probably locking so like there’s like i just find them unnecessary in query so if they’re there then you’re not having a problem cool but i i usually turn that one off um and then of course 2019 i think 2019 or so maybe yeah 2019 offered like different collection profiles where you can like uh like like like adjust the thresholds at which things go to query store which is which is nice too but um when does it become more dangerous you know the thing the thing i get the thing of it is um if you have that type of workload uh you’re gonna care quite a bit about performance for that workload um you you might be able to uh assemble uh other ways of collecting that sort of performance data perhaps a free open source sql server monitoring tool would be of some value to you um like mine just saying uh but uh because you know like that’s not like you know like it does use query store if it’s there but it’s also collecting stuff from the server that’s not in that way so um like you would have to figure out a different way to um like really catch good performance issues there because just looking at the plan cache once in a while um you know you have a big heavy workload your plan cache is going to be blown out seven ways to sunday it’s like you’re not going to be able to find like historically treacherous things in it like just like most most plan caches that i look at do not have a very good lifespan all right last question here why do top or roll goal queries sometimes run slower than full scans uh well lots of reasons um let’s see uh often like you like like let’s say you get a query that does like a full parallel dop 8 scan of a big table and you’re like oh cool i found everything pretty quickly um top and roguel query sql server sort of makes a little bit with itself and says hey i think i can find this data in three rows but then it can’t find that data in three rows so i think the what the the thing that you want to look for in those queries might be like um like rarely occurring or non-existent data patterns that would be one of them um sometimes you’ll see sql server do something really silly and put like a top above a scan and it’s like single thread and it’s a big table and you’re like oh wait no uh don’t do that right top above the scan one of like if you see that in the query plan you you have you have a fix immediately because that should be if a top above a seek is like much much much easier to cope with than a top above a scan especially on a big table because most likely that top above a scan in a rogo plan is going to be single threaded and it’s like some 80 million row table and you’re just sit like single threaded scanning it like every time trying to find a table and a row in sql server is like i can do it in three rows and then like they’re like 3 000 rows you’re like you’re not doing it you’re just scanning this giant table you’ve scanned this 80 million row table 3 000 times now single threaded we are we are quitting done here um so usually it’s that um uh usually that’s my experience with it um you i’m gonna i’m gonna say this but i’m gonna preface it with saying like there’s a big asterisk here a lot of times uh uh SQL Server users um because of these very low number of steps to split um top middle third row and let’s say they go to the top and rogo queries um because of the very low row estimates where sql server is like no i can do this in three rows uh they will not get a parallel plan because sql server is like i can do this in three rows why do i need a parallel plan i don’t need multiple threads for this and three rows we’re done so a lot of times they get costed incorrectly and so you or like i mean they get costed correctly for what they are but for the they get costed correctly for the estimate but not for the reality they will be costed very low and not be eligible for a parallel execution plan. That’s another thing that ties into it. So I think that’s probably the most common stuff that you’ll see out in the world. I don’t know. Perhaps there’s something in there that I am not thinking of immediately, but I think that’s probably good enough. So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where I’m going to talk about something with change data capture that’s not fun to deal with and a good way of dealing with that. So with that out of the way, thank you. I love you. I will see you tomorrow. Good night. Sleep tight. If you have bedbugs, man, get out of the house. Don’t not like don’t like just leave. Burn it down. All right. Goodbye.

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.

SQL Server Performance Office Hours Episode 74