SQL Server Performance Office Hours Episode 64

Posted on by Erik Darling

SQL Server Performance Office Hours Episode 64


To ask your questions, head over here.

Chapters

Full Transcript

Erik Darling here with Darling Data, and today’s video, we are going to do a whole bunch of office hours. Won’t that be fun for everybody? Because, you know, I like answering questions, and sometimes I can’t think of all the good questions, so I leave some of the questions asking up to you.

And so you, the fine people of the SQL Server, the greater SQL Server community, the greatest SQL Server community, can send them to me for free. Let’s talk about how you do that. Down in the video description, there’s all sorts of helpful links for you to do things.

So, one of those things is asking me office hours questions. There’s a link down there where you submit a question. It’s wonderful. And there are other links in there, too, that do require a little bit more, let’s just call it, money. Like, you can hire me for consulting, you can purchase my training, and you can even become a supporting member of the channel if you decide that, you know, you’re into, you know, my channel that much.

Like, four bucks a month or something. It’s very, very inexpensive. You won’t even notice it. It might even be tax deductible, because I am a charity. The darling data home for little wanderers.

Anyway, also down in the video description, another free thing you can do is download my open source SQL Server monitoring tool. It does all the performance monitoring that I would do if I worked at your company, and I was like, hey, we need to fix this. We need to fix this.

We need to figure out what’s wrong with these SQL servers. This is what I would do. And you can have it there for free. Again, like, you know, if you have a big expensive monitoring tool right now that isn’t helping you figure out your performance problems, try this one. Again, I make the same amount of money either way.

Zero. But you, you might actually get a good monitoring to a lot of it, which is a pretty good deal. Again, the link for that is down below as well. But, hey, come on.

Why didn’t you work? Anyway, so, like, actually, no, actually, I will be home from Chicago and in Poland when this publishes. So, forget those.

You know, time travel is weird. But coming up, I will still be at Data Saturday Croatia, and I will still be, as far as I know, you know, all things in the world considered, be at Past Data Summit in Seattle, Washington. So we’ll have, well, I mean, let’s just say I’m definitely going to have an advanced T-SQL pre-con here.

Here? I don’t know. Might happen, might not. We’ll just have to see what happens in the world.

Anyway, it is May, and ChatGPT thought May was a real gothy database rainy party, I don’t know, singing hymns to skulls. That’s… GPT got dark. I don’t know.

Anyway, let’s answer some questions. We got questions to answer here. Let’s see, is ZoomIt going to cooperate? How many layers of ZoomIt being uncooperative will we have to deal with? All right.

First question. How risky is plan forcing and QueryStore realistically? Have you seen it go badly? Oh, boy.

Doing what I do, I see everything go badly. So, like, I think the biggest risk that you run with plan forcing and QueryStore is, like, let’s say you’re in the GUI, right?

Because that’s the way most people approach QueryStore. I don’t know why, because I have, you know, SPQuickieStore, which does things a lot better than the GUI. But anyway, let’s just say you’re in the GUI, right?

Because one thing working with SQL Server is it’s a GUI mess. But you’re in there and you’re looking at, like, regressed queries or whatever. And you’re like, ah, when this query ID uses this plan, it’s always really fast and uses very few resources.

And when this query ID uses this plan, it really uses a lot more resources. And then you force the query that usually uses very few resources. And then you come to realize that the query plan that used very few resources did so because it was doing very little work.

And the query plan that was tied to that query ID that used more resources was, of course, doing a lot more work. And now you’ve just got, like, this awful parameter sensitivity problem. So, you know, what I say is always test carefully in those cases.

If you’re going to force a query plan, make sure that you understand what plan you’re forcing it for. Perhaps as far as you can get the compile time values for each plan. If you’re using, like, optimize for unknown or local variables or something like that, you’re not going to see them.

Other things might mess you up, like temp tables, table variables. You’re not going to know what data was in those. You know, if you use option recompile, you get the literal values.

You don’t need to know what data is in there, which you can figure out. But you can also use my free store procedure, SP Query Repro Builder, to help you figure that out. It’s another free tool in my darling data repo.

So if you go there and you get that, you can give it, like, a query ID or a plan ID or just about any other plan identifier in Query Store. And it will try to build a reproducible, executable query for you to run and look at the query plan for and all that good stuff. So, I don’t know.

That was more than I thought it would be there. Anyway. All right. All right. Zoom it. All right. Zoom it. You’re going to be like that today. Wow.

Okay. Is parameter sniffing actually a bug or just misunderstood behavior? I mean, look. I was going to say, Microsoft has had some pretty long-standing bugs. But I’m pretty sure if this were a bug, they would have done something about it by now.

In fact, they kind of did with the parameter-sensitive plan optimization. I don’t have my beefs and grievances with that thing. But, you know, we don’t need to get into that here.

At this point, you know, if anyone misunderstands the behavior, I think that they are just being willfully ignorant. There are 10 billion years of blog posts and informations out there. And, you know, especially now in the modern day of the robot, if you, like, robots love pointing at, I think, well-known problems, right?

Like parameter-sensitive. Ah, classic bug. Ah, classic parameter sniffing issue, right?

They love talking about that stuff. So, you know, I think it depends on how you look at it a little bit. There are other well-known databases.

Let’s say, like, Postgres where there is not really plan sharing. I know there’s, like, a reusable session thing. But, like, if you have, like, 50 different sessions all running the same query, they’re not going to, like, cache and reuse a plan.

There’s not really, like, the concept of that. Right? There’s not really a plan cache the way there is in SQL Server. So other databases do approach this differently.

You could consider that good or bad depending on, you know, what problem you’re dealing with. I generally consider parameter sniffing a good thing. I consider parameter sensitivity a thing that I like to fix.

But, you know, you can make the case for either one being a good approach depending on the workload, depending on the characteristics. I do have one friend of the channel. Who I will not name.

Be decent about this. Who was experimenting with getting his application migrated from SQL Server to Postgres. It’s called Extreme OLTP.

Make him happy when he hears that. But the cost of the connection and compilation of these tiny little queries on every connection for Postgres is a very big penalty for them. Where in SQL Server, it is, you know, like, almost instantaneous because we have the plan cache and we have plan reuse and stuff like that.

Anyway. What kinds of performance problems only show up under heavy concurrency and never in test? Geez.

So many. You know, I think probably the first one that I can think of is, like, deadlocks. Right? Because, like, not every deadlock is just a predictable, like, table A, table B, table C. Right?

Table B, table A. We got in a fight. Like, some of them really are just, like, bad luck and bad timing. And they only show up under, like, you know, actual heavy, like, you know, real world concurrency. You know, ditto some blocking problems.

Though I think blocking problems are somewhat easier to immediately forecast because you’re like, well, this modification query takes forever when I test it. What could go wrong? So there’s that.

But there are all sorts of other ones, too. You know, primarily amongst sort of, like, resource exhaustion. Resource semaphore would be one of them. Thread pool would be another one. Resource semaphore query compile when you have all the queries in the world trying to get through those compile gateways.

So there are a lot of things that show up. And even, you know, before you get to anything that’s, like, monstrously catastrophic, like, you know, like resource exhaustion, big blocking, deadlocking stuff. You know, you’ll run into little things, too.

Like maybe even queries. Maybe even queries will just be a bit slower because there is just resource contention for things, right? Whether it be objects in the buffer pool, whether it be access to CPUs, things like that. You know, something I talk about a lot in my training is, you know, the difference between tuning one query to be as fast as humanly possible or as fast as SQL Serverly possible.

And, you know, sort of tuning a query so that it is a good citizen in a workload, right? Because, like, you know, let’s put it into two pieces. Let’s say you have a very big query, like something reporty, data warehousey, right?

But you’re reporting off OLTP because you’re that kind of crazy person. And you might, you know, look at that query and that query plan. And you might say, oh, well, I have, like, you know, maxed up is set to four.

All right. Well, I have four threads working in this query. Then each processing, like, I don’t know, let’s say two billion rows apiece, right? And you’re like, yeah, that’s not good.

If I run this query at maxed out. All right. I’m going to stop eight. Then I can cut that in half because, you know, we’ll have twice as many threads for the queries to sort of divide the rows to divide up on. So that’s one way of looking at it.

But then the other way of looking at it is sort of like, well, like, maybe not every query can get, like, all the maxed up and all the memory it wants, right? Maybe some of these queries are going to have to run a little bit imperfectly so that when we have our larger workload running, the green screen over there is getting real annoying. And I don’t understand why.

It’s wiggly. I’m just standing here. Like, you might have to, like, sort of have them sort of run in a more cooperative way. So, like, you know, some queries may run imperfectly so that the larger group of queries can all run together. But, yeah, you know, there’s lots of stuff that will only that only shows up if you have that only shows up under, like, real user workload. I think accurate load testing is one of the hardest things in the world right now. I mean, I don’t know. I don’t know. I don’t know. I don’t know.

I don’t know. I don’t know. I don’t know. I don’t know. I don’t know. I don’t know. I don’t know. I don’t know. I don’t know. I don’t know.

I don’t know. I don’t know. I don’t know. But, you know, a lot of that just just simplyب shows up if you have that only shows up under like real user work load. I think accurate load testing is one of the hardest things in the database. How do you approach tuning when you have zero control over the application code.

Hardware, Hardware hardwa. Now let’s get sometimes it comes sometimesit is Hardware, but you know that’s that’s more of a generalized thing. Most of the time it would be indexing.

You know. and various other things that you can do in SQL Server that can have queries do a little bit better. Let’s say indexing is the first one that would include index views, that would include filtered indexes, that would even include indexed computed columns.

So if I have zero control over application code, but I have carte blanche for the schema and anything else in the database or that the database is sitting on, then that sort of becomes the realm that you have to work in, right?

Is it ever safe to fully ignore missing index DMV suggestions? Sure, I do that all the time. The thing with the missing index DMVs is like, there’s a lot of gaps and holes in them.

They are very opportunistic. They are part of query optimization. So like SQL Server isn’t spending a long time thinking really hard about the index that it’s asking for. It’s very where clause, centric, will often ask for very, very wide indexes, like the sort of normal spate of complaints about the missing index suggestions.

So yeah, I do ignore them a lot. Where I don’t ignore them is in query plans that I’m tuning.

So the chances are, if I’m looking at a query that is not running as quickly as I want it to, then there’s probably something that I want to do to fix that. One of those things might be adding an index or modifying an existing index.

So what I’ll usually do is I will take the query, I will get an actual execution plan for it, and I will see if the missing index suggestion or any of the missing index suggestions line up with a slow part of the query plan.

And if I think that adding that index will indeed positively affect that slow part of the query plan. But from there, sometimes I will go and look back at the missing index DMVs and say, okay, how often would a query theoretically use this?

I can see how many times a query is executed in various ways, whether it’s the plan cache or query store, but I still need to deal with figuring out, is this index good generally for the workload?

Not just once a day if this query runs, but are there many other things that would use this? Anyway, this is really annoying me, so I’m gonna go try to fix it.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something, and I will see you in tomorrow’s video where we will talk about something T-SQL-y, I promise. God, green screen is a real.

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 *