The Unpredictability Of SQL Server Performance
Thanks for watching!
Video Summary
In this video, I delve into the unpredictable nature of SQL Server performance issues, sharing a real-life client emergency that occurred on a Saturday afternoon. This experience highlights how slight changes in data or workload can significantly impact query plans and execution, making it challenging to maintain consistent performance. I illustrate these points with an example where two nearly identical queries exhibit vastly different performance due to subtle differences in literal values and statistics sampling. By discussing the nuances of index usage, modification queries, and locking strategies, I aim to provide insights into why SQL Server can behave unpredictably and how understanding these factors can help in troubleshooting and optimizing your database environment.
Full Transcript
Erik Darling here with Darling Data, and in today’s video I want to talk a little bit about the unpredictableness of SQL Server performance issues. And this came up because I actually dealt with a bit of a client emergency a little bit earlier today, and if you’re looking at your calendar, if you’re looking at the published date of this video, you’ll see that it is indeed Saturday at 3.10pm, which means that it’s a little bit more than a client. It means Erik Darling did some work on a weekend, which is a rarity, but you know, nice people pay me money and I try to be responsive. So there’s a couple different angles to this, and a lot of it has to do with making sure that your environment is sort of set up and maintained so that SQL Server has, is future-served, chances to screw-up, and chances to screw-up as possible. SQL Server is a big, gigantic, complicated piece of software, and slight changes here and there can have weird effects.
And even if you’re not making changes, your data most likely is changing. And as data changes, well, SQL Server makes adjustments. Things like auto-update stats, compile new query plans, and all sorts of other sort of things that, you know, seem like a good, you know, dynamic, like adjustment to whatever is happening with your data and your workload, but can really kind of make things hard to keep stable. And, you know, not to plug away too much at, you know, your humble YouTuber here, but this is the kind of thing that I do like helping people with. So let’s, let’s just take a look at these two queries right here. There is a one minute difference in one of these literal values. And if we run both of these queries, and we look at the query plans, one of them is going to do a clustered index scan, which takes 175, well, 177 milliseconds. And the other one’s going to do an index seek with a key lookup that takes 18 milliseconds.
Right? That’s a one minute difference there. And there’s a one row difference. And one SQL Server decided that it was going to tip that point. The one that does the clustered index scan estimated 13,496 rows. And the one that does a key lookup estimated, well, that was an improperly aligned blob, but the one that does a key lookup asked for 13,000, estimated 13,495 rows. Right? And I know you’re looking at this, and you see, you see this green text here. And this green text is asking for an index that would fully cover the query. Right? So it wants to include display name, display name is not in the index up here that I created. So asking for display name is really the thing that tips SQL Server over. If we had that green text index, SQL Server wouldn’t really have much of a choice. Like that key lookup just wouldn’t be an option.
Or rather, it would, I mean, I guess it would be an option if there were another index. But SQL Server would narrow down its choices to that one index that has everything in it a lot easier. Right? So it would just say, well, there’s no lookup to be done. So this is clearly the cheapest way. There’s not going to be that costing choice. So that’s kind of like one part of it. And the other part of it is that when modification queries are on, modification queries need love and indexes too.
They need to have good ways to find data. And one thing that’s terribly frustrating, before we move on, one thing that’s terribly frustrating is like I’ve, I’ve, I’ve, I’ve gone over this demo a few times and every time that I have dropped and redone this index, that the time and date that I need to put in here to get that one row plan switch over has been different.
So like different stats, stats, samplings and stuff can really make a big difference here. Like if I, if I rerun all this stuff and I rerun these two queries, there is a very good chance that either they’ll both get this, that they’ll both get the same plan and we won’t see that tipping point difference like we did before. And we, we don’t with that, with that, with that index, right? Where we resampled statistics, look what happened.
Now for both of them, we get a clustered index scan. And now these numbers are still have a one row difference, but they weren’t quite low enough for a SQL Server to, to, to go for the key lookup plan. Right? Neither one of them met whatever internal tipping point threshold SQL Server had for itself.
And that, that’s terribly frustrating. Stabilizing stuff like this is hard. Right? Figuring out that this was the problem is hard. Figuring out how to solve the problem is hard. Right? Because there’s not all, it’s not always as obvious as an index when a plan completely and drastically changes.
This is just a simple example. There are, you know, the queries that I deal with are much bigger, much more complex. And there are much bigger changes that happen just because of random set standpoint. But for instance, like a query that used to have a fairly, that used to have a cost that made it eligible for a parallel plan.
Something happened. And all of a sudden SQL Server is like, well, this, this, I think this thing’s going to cost like two query bucks. And you can’t just go drop cost threshold for parallelism down to one so that this query plan, this new query plan is like parallel all of a sudden. That would drastically change the entire workload.
Another thing that gets weird is with locking. So with just this index in place, if I run this and I do this update, SQL Server on the first run chooses to lock the entire object. Right? SQL Server said, nope, don’t want to manage a bunch of little locks.
That’s not fun. We’re going to lock the whole object. Right? And if I run this again, SQL Server chooses a different strategy. I ran the same update twice in a row and now SQL Server said, oh, I changed my mind.
Now we’re just going to take a bunch of exclusive key locks. Right? And then if I, if I come down, if I come down here a little bit and I’m going to lose a little bit of how the sausage was made going out there. But if I, if I come and rebuild the, that users table and I run this again, we are back to locking the whole object.
Right? And then if I run that update again, we’re back to using the key lock. So it’s like just weird stuff happens constantly all the time. Weird, like weird little things change.
Weird little bids and bobs just line up differently. And plans change and locking changes and all sorts of other things just kind of get really weird. And you can just have oddball problems.
Of course, if we add in a good index for this modification to query, this modification query is, we don’t even necessarily need creation date in here. That’s just sort of an artifact of when I copied and pasted this index down lower. Then we’ll get pretty consistent results from this where we’ll always take the key locks.
Right? Because SQL Server will have a good index that it can seek to, to find the, the, the reputation rows that it wants to update. So if you’re out there and you’re, you’re having trouble with these sorts of tiny little things, having profound cascading effects on SQL Server performance, if this is really gumming up your workload, well, I’m, I’m, I’m here for you.
This is the, this is the kind of stuff that I like doing. You know, it’s like a, like, like a coach likes telling you what’s wrong with your squat form. I like what’s telling, I like telling you what’s wrong with your SQL form.
So if you, if you do need this kind of help with stuff, um, you know, I’m, I’m here. I’m ready to work. I’ve got my, got my hammers and shovels, uh, a couple of flamethrowers, you know, some grenades.
We got some, we got some stuff that, uh, we got some, we got some good hardware. So, uh, anyway, uh, I’m going to go not work on a Saturday now. And, uh, we’re going to, yeah, I think, I think, I do believe it’s martini time.
It’s close enough. So, anyway, uh, thank you for watching. I hope you learned something.
I hope that, I hope that you, I genuinely hope that you don’t have these kind of SQL Server problems. But if you do, uh, young and handsome consultants are standing by. And, um, yeah, uh, if you like this video, uh, appropriately placed thumbs are, are appreciated.
Uh, as are nice comments, especially if you like my new haircut. I got one. My head’s not big and fuzzy anymore.
My head’s nice and sleek and streamlined and sort of aerodynamic. I look like I’m, I look like I’m ready to, like, be a, be a general or something. I could lead an army with this haircut.
Uh, and if you like this kind of SQL Server content, or if you just need, if you, if you see this kind of SQL Server content, and you’re like, wow, I, I, I, I, I identify with that. Well, um, I guess you, you could subscribe to the channel, which is free, but, um, that, that, that might not solve all your problems unless, unless you, unless you have a lot of time on your hands.
So, anyway, uh, thank you for watching. And, and please, please do go enjoy, uh, your Saturday. Hopefully you didn’t have any weird emergencies today.
And, um, I, I do hope that if, if you are the type of person who, who partakes in, uh, alcoholic beverages, that, uh, your martinis are cold and stiff. Way, way God intended. All right.
Uh, 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.