SQL Server Performance Office Hours Episode 89
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:30 – Office Hours Overview
- 00:03:45 – Monitoring Tool Update
- 00:05:15 – QueryStore and Performance Monitor
- 00:07:00 – Plan Cache Issues
- 00:08:25 – Query Store in Cloud Environments
- 00:10:00 – Conclusion
Full Transcript
Erik Darling here with Darling Data, and thanks for noticing, I did just take a shower. Anyway, it’s time for Office Hours, where I answer five questions from you happy, lovely people, and hopefully make your lives better. So, we’ll do that. Down in the video description, as always, you’ll find very, very helpful links for you. To interact with me in various ways that will be advantageous to the both of us. You can ask me Office Hours questions, down there. You can purchase training from me, which is great training, at a very reasonable price, and you get it for life.
The rest of your God-given life, it’s yours, in your hands, forever. You can also hire me for consulting, which, I mean, if you want to do that for the rest of your life, too. Who am I going to argue? I argue with you. That would be silly.
And of course, if you enjoy this content, please do like, subscribe, tell a friend, and all that good stuff. My free database performance monitoring continues to grow, get better, improve. I am fully reworking, right now, the analysis engine.
SQL Server always had it, but I wanted to rework some of the stuff in there. And as part of that, I am also reworking the analysis engine for the Postgres side as well. Not reworking, implementing, porting over the SQL Server analysis engine to Postgres, the Postgres data collection as well.
So you should see that coming up in the next release this week. Big things afoot here at Darling Data. If you’d like to try it out, go to code.erikdarling.com.
It’s the performance monitor repo. Again, totally free, totally open source, doesn’t require you giving me an email address or any of that stuff. And it’s got all of the best stuff that you would want, desire, stuff that you might not even know you wanted in a monitoring tool.
It’s absolutely phenomenal piece of work. My only remaining speaking engagement of the year is Pass Summit West. That is happening in Seattle, Washington, November 9th through 11th.
I’ve got a pre-con and a regular session there. Pre-con is T-SQL that doesn’t suck. We’re going to teach you how to overcome the dumbness of the robots.
And then I’ve got a regular session called Read Committed is Cruel by Default. And it’s about my least favorite isolation level. But for now, it is still September.
We are still wiping the sweat from our shiny visages. Under carriages and trying to just make it through the day. So let’s go answer these questions.
Well, hello, Eric. Hello, howdy. How’s it going? In our application, in every update, we drop create all procedures and triggers. That’s, you know, you can alter them.
You could do it that way. We do not use QueryStore. What?
God’s green earth is wrong with you. Maybe you don’t use QueryStore because you’re on an older version of SQL Server. I don’t know. Could we trust Performance Monitor or it may get confused result-wise with the drop create thingy? No, it just resolves the object IDs when it collects stuff.
So it’ll figure out your procedure and object names based on the ones that come in. Assuming that you’re actually making changes to procedures and triggers. No, actually, no.
That’s like all, that’s either, yeah, it’s all like query hash, query plan hash. So like if you make changes and stuff generates new plans, then it’ll, you know, it’ll re-register those as new. But no, it doesn’t get confused so easily.
All right. Next question. Is it ever appropriate to put ketchup on a hot dog? Absolutely not.
If you put anything other than mustard on a hot dog, I don’t, I don’t want to know you. All right. Uh, why does SQL Server sometimes switch between good and bad plans every few hours? Well, uh, cause you, you lost a plan.
The plan cache is not, not a very, uh, dependable or reliable place in SQL Server. It being an ephemeral in memory construct, all sorts of things can happen, uh, that make plans disappear. New plans arrive, uh, show up and give you a bad time.
Uh, it could be auto stats updates. It could be manual stats updates. If you’re being aggressive with them, temp tables are a frequent reason for that. But then all sorts of other internal mechanisms and like, like memory pressure may, uh, relieve your plan cache of query plans.
And then SQL Server is forced to compile one on the next go around. And that plan may not be great. Um, I see a lot of, I see a lot of that problem for, uh, the ascending key problem, but, uh, you know, the initial compile time parameter, uh, I don’t know.
Maybe it could, maybe it’s good for that initial compile execution of the query, but, uh, further executions do not do so hot. So, um, that’s usually what I see. Um, if you have, if you have query store, unlike, unlike this person who says we do not use query store.
Um, you might try forcing plans that you know, love, and, uh, are familiar with. Uh, one could also make a reasonable attempt at using plan guides to stabilize things. Um, if the problem is around temp tables, you could try the option keep fixed plan hint.
Uh, otherwise, tune your damn queries. Or hire me to do it. That’s, that’s my job.
Uh, let’s see. Is it safe to leave query store always on and high throughput systems? Uh, for me, that depends a little bit more on, uh, plan volatility than high throughput. Uh, you could have very, very high throughput systems that have, uh, very, very stable execution plans.
And, uh, not see too, too much overhead. Um, if you have very high throughput systems that generate a lot of new query plans. Uh, and you’re constantly having to write new XML gobbledygooks into the, into query store.
Uh, then the overhead does increase. Uh, for me though, uh, the, for me, query store collection has, I mean, it’s not going to say never. Of course it’s had some stuff over the years.
But, um, for me, it’s more query store cleanup and internal maintenance that I see, uh, having problems in SQL Server. Uh, on top of that query and query store data, uh, exposes a lot of plumb foolishness that I don’t particularly care for. But, uh, in general, I like query store.
I use it, use it a lot. I think it’s a good thing. Uh, wish that it would get some of those rough edges cleaned up. Uh, what kinds of performance issues only show up in cloud environments?
Well, uh, I don’t, I don’t know about cloud only. Um, I think very specifically, uh, I mean like, eh, you can underpower. So, like, I don’t even know why you would put SQL Server on that though.
Like, like Amazon has like a T series of, of VMs that, uh, or, uh, EC2 instances that, like, uh, throttle CPU and stuff. I’m sure Azure has some of those same, same types of things. Uh, you don’t really want to put your SQL Server workload on a throttle VM though.
That’s probably not a good idea. Um, usually, uh, for me, I think a lot of it would be IO stuff. Um, cloud storage is, uh, often, uh, quite underwhelming, especially in Azure.
Uh, Azure storage is, uh, almost always a nightmare. When you look at it, even in the business critical tier, there are very, very strange, uh, things that happen. Uh, um, it’s rather unfortunate, but I don’t, I don’t think that’s cloud only.
You could have really bad IO on-prem. Um, I, I think probably the, the, uh, the biggest potential for cloud only stuff would be if you are using, uh, like an Azure managed service, like, uh, Azure SQL database, hyperscale managed instance, something like that, because, uh, you, you are essentially guinea pigging for Microsoft.
They might release a new feature. They might change something under the covers, uh, including your hardware surprise. Uh, and, and, you know, uh, you might, you might hit some oddities in there, uh, as, as new code hits, uh, your database that is maybe not as well tested as you would hope.
Um, but as far as like cloud only stuff, um, I can’t really think of anything that, that, that, like performance problems that I’ve only seen in the cloud. Uh, you know, I’ve certainly seen some performance problems get exacerbated by, uh, by the choices of cloud hardware, uh, you know, the underlying infrastructure stuff. But I don’t, I don’t think that I’ve, I’ve ever noticed anything in the cloud that’s like, wow, that could only happen here.
All right. I’m going to ramble long enough. I’m tired.
I’m going to go do something that makes me untired. Uh, 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 some performance monitoring stuff. 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.