When To Use DBCC DROPCLEANBUFFERS When You’re Tuning SQL Server Queries
Thanks for watching!
Video Summary
In this video, I delve into how to determine if a query has been effectively tuned, addressing common questions from clients and colleagues alike. I discuss various initial steps like using recompile hints or experimenting with cardinality estimators before diving deeper into the query logic. The video also explores the use of `DROP CLEANBUFFERS` as a tool for demonstrating server performance issues, particularly when queries exhibit inconsistent speeds due to varying data availability in memory. By running this command and comparing query execution times, I illustrate how hardware limitations can significantly impact query performance, even with well-optimized indexes. This practical demonstration helps highlight the importance of balancing index design with sufficient server resources to ensure reliable query execution.
Full Transcript
Erik Darling here with Darling Data. And in this video, we’re going to talk about how to tell if you’ve tuned a query. Now, this is a question that I get a lot when I’m working with clients, the nice people who pay me to, you know, be able to afford this nice equipment to make these nice videos for you and to not run commercials during my videos and stuff like that. Because, you know, like when I’m tuning a query, there’s a lot of different stuff I’ll try to do. Some of it might be some upfront stuff just to see where things are at. Like, I might throw a recompile hint on there. I might mess with the cardinality estimation model. I might, you know, either force the new cardinality estimator or more commonly force the legacy cardinality estimator to see if I can improve things. There are a lot of different things that you want to try before you start, like, really digging in and restructuring stuff, unless there are some very obvious structural issues with that. The query. Obvious structural issues would fall under the domain of things like joins that have OR clauses in them, CTE with, like, multiple references throughout the query, where you might want to, like, you know, fix the OR clause in the join to do, like, you know, out or apply union, union all type thing.
You might want to dump CTE into a temp table. You know, you might throw a recompile hint on there if you want to test the efficacy of, you know, maybe you have a local variable in the query. Maybe there’s a table variable in the query.
You know, and, you know, maybe using a table variable, you know, you might want to dump that into a temp table and go from there. So, like, there are some obvious, like, some obvious, like, tweaks that don’t involve really messing with the query logic all that much. There’s some domain knowledge stuff that, you know, some of the developers that I work with have about the nature of the data that can make, like, a query, that can make a query tuning exercise a lot easier.
You know, sometimes a developer will come along and say, we actually don’t even need to run that, or this query doesn’t make any sense. Like, I can’t imagine why we’d be doing this. Other times they’ll say, oh, we don’t actually need to join to this other table, which is slowing things down.
We have that column in this other smaller table. It’ll be much easier to go get it from here. You know, there’s some domain knowledge stuff that’s good to have, you know, when I’m working with clients because they can point these things out.
In this video, we’re going to talk a little bit about a question that I get a lot, where it is, you know, someone will say, oh, you know, I thought I made the query faster, but every time I run drop clean buffers, it’s slow again. Okay, well, that’s fair, because when you drop clean buffers, which hopefully you’re not doing in production, you do clear out all the data that’s currently in the buffer pool, which is really only a fair test if you are doing something like, if you’re on a server that has, you know, queries are showing lots of high physical reads, when you look at their metrics from the plan cache or inquiry store, and that’s backed up by a very, very high page IO latch underscore SH or EX weights, where that shows that queries are constantly going out to disk to bring data into memory before they can run.
So, a little bit of housekeeping, as the professionals say. If you like my channel and you want to support me in a very minor way, there are very low-cost memberships that you can sign up for. If this is beyond your means, if the dollar sign is not something that you can contribute, likes, comments, subscribes are equally as profoundly gratifying to me, because I like the engagement, to be honest with you.
I like knowing that every time I put a video out there into the world, lots of people are going to be able to benefit from it. So, if you are in the market for SQL Server Consulting, these are the kind of things that I typically help clients with. If you need something else, let me know what it is. We can figure that out, too.
If you need training that is affordable, that lasts a lifetime, you can go to that link up there. There’s also a link down in the video description where you can purchase my training with the 75% off code. It brings all that stuff down to just about $150 USD, which is also a plenty fine way of buying me a bottle of wine.
Well, you can also do that. I do have some live events coming up in, oh boy, just about two weeks. I will be at Data Saturday Dallas, Friday, September 6th, doing my full-day pre-con, and also at the main event on Saturday, September 7th.
And then November 4th and 5th, I will be co-presenting two days of pre-cons with Kendra Little on November 4th and 5th. And that’s, of course, in Seattle. There are no online versions of these things, so if you want to go, you’ve got to show up.
One is in Dallas, one is in Seattle. If those are helpful locales for you, well, I look forward to seeing you. So now let’s get on with the show here.
Let’s talk about when I might use drop-clean buffers to show people the kind of state that their server is in. Now, for the other stuff that I talked about, recompile, cardinality estimation, temp tables versus table variables, I’ve covered a lot of that stuff in a lot of other videos.
What I haven’t really talked about is how you can use drop-clean buffers to show people how their server, like what’s hurting queries on their server. So sometimes when you do this, one thing that you have to keep in mind is that you might not have good indexes to support your queries.
We’ll talk about that. Let’s say you have a query that’s usually fast, but sometimes it’s slow. So you might blame parameter sniffing, so one thing that you might try is a recompile hint, which is fine. If you try the recompile hint, and sometimes it’s fast and sometimes it’s slow, you probably don’t have a parameter sniffing issue.
What you might have is just sort of a more pathological server configuration issue. One of the places you might look is, like if you look in the query plan and you see, oh, I have good indexes for everything, nothing is particularly painful with anything that I’m doing.
It’s just sometimes seeking into this index takes like five or ten seconds, and sometimes seeking into this index takes a few milliseconds, and you might ask why. Well, this is a pretty good example of why.
What I’m going to do is I’m going to run drop-clean buffers, and then I’m going to run the same count query twice. And so we’ve cleaned the buffers, we’ve scrubbed them clean, we’ve gotten a little soft toothbrush and some toothpicks and q-tips and stuff.
We’ve really gotten the crevices, we’ve detailed those buffers, and they are so clean now. They are spotless, they are sparkling, they are just magnificent buffers.
And if you look at the query plans for these two things, we have, you know, two very different query timing things here. This one runs for about 6.3 seconds, and this one runs for about 631 milliseconds.
That’s a big difference, right? Like if you multiply the 631 milliseconds by some number with a few zeros in it, you will get to about 6.3 seconds like we have up in the first query.
If you have people complaining that queries are sometimes slow, one thing that, you know, some things that you want to dig into on the server are, okay, well, you know, if you have a specific query to look at, you can look at that query and you can look at the resource usage of that query in QueryStore, which SPQuickieStore, MyStore procedure, makes very easy for you.
Or you can look at the plan cache, and you can check out the physical reads for that server. And if you notice that sometimes it’s doing a lot of physical reads, like it might have a high, like, max physical reads and a low min physical reads, that’s a pretty good sign that this query runs sometimes, and all the data that it needs is already in the buffer pool where it’s helpful.
And sometimes it’s not in the buffer pool where it’s not helpful, and you have to go to disk to get it. When you have to go to disk to get it, sometimes this query is going to take 6.3 seconds.
When you don’t have to go to disk to get it, sometimes that query is going to take 630 milliseconds. So this is, drop clean buffers is a pretty good way to say, okay, if I already have good indexes in place, and sometimes this thing is fast and sometimes this thing is slow, maybe we just don’t have enough memory on this server to accommodate the workload we’re asking it to run.
Right? That can totally happen. Depending on the size of the table and index that you’re dealing with, even reading a big nonclustered index can be pretty painful.
So always keep an eye on that. And for the sake of this argument, let’s just say that we quite obviously don’t have a good index.
Both of the queries that ran above, regardless of whether data was in memory or not, both of those queries were saying, hey, an index, please.
S’il vous plaît, as they say in bonjour land. So we can create an index. And when we create an index, we make it easier for a SQL Server to read the amount of data, a smaller amount of data, right?
The post table is a pretty big, gnarly table with like a lot of string data in it, including a big and bar car max column. And even just like reading through all that stuff can be pretty slow when you’re reading straight from disk.
With drop clean buffers in place, what we see is a much different situation. Even when we have no data in memory, right? We drop clean buffers, we select a count, and we do this, we’re able to seek to just the data that we care about.
If this index were set up a little bit different, let’s say that there were another column in the key of the index before score, so that we had to scan the whole index.
The one that reads from disk would take a little bit longer. When we can seek to write to what we care about, we sort of take away some of the pain of having to go to disk for anything. Not only do we have to read far less from disk into memory.
Remember, when you do a seek into an index, you only have to go and get those relevant pages. It’s when you have to scan a whole index. And I don’t mean like a scan with a top or an offset fetch in it where you can stop reading pages after a certain point.
I mean a true index scan where you actually have to scan through the entire thing. Even reading a big enough nonclustered index can be pretty slow. I could create another index and put the body column from the post table in the includes and slow it down even more because I would be inflating that index size.
So obviously, when you’re tuning queries, make sure that you have a reasonable index to find your data with. If you don’t have that, if that is for some reason eluding you, always go with creating the indexes first.
If you already have good supporting indexes for your query and the query is still slow, sometimes, sometimes, fast, sometimes, then one thing that you want to keep an eye on for the workload as a whole are weight stats.
You want to always be looking at the page.io latch underscore something something weights. If those are more than like, you know, 10, 15, 20% of your server’s uptime, that means that most of it, like that percentage of time that queries are running, they’re going out to disk.
So like, let’s say that page.io latch weights are 25% of your server’s uptime. That means that 25% of the time, your queries are a lot slower than usual, right?
Just doing some brute force math there. I’m not that good at like statistical probability stuff, but if page.io latch weights are 25% of your server’s uptime, one out of every four times your query runs, it could be slow.
The other three times that it runs when data’s already in memory, it could be fast. Pretty wild to think about. Another brute force way to think about this is that every time you double memory, you will cut page.io latch weights in half.
So if you have 64 gigs of memory and you go up to 128 gigs of memory, you might see those page.io latch weights go from 25% of your server’s uptime to 12.5% of your server’s uptime.
Yeah, I did it math. On the spot, on the fly. 12 plus 12 is 24 plus 0.5, 225.
I did it. I did it just for you. So these are things to consider. When you’re dealing with, depending on the server situation that you’re dealing with, these are the kind of things that you might have to prove out to either your boss or if you’re a consultant like me that you might have to prove out to clients to say, hey, if you want this to be reliably fast, well, we’ve already got the good indexes in place.
What we need now is hardware that can actually accommodate the workload that it’s running on. You can even run those exact same numbers by them. You can say, hey, page.io latch weights are 30% of your server’s uptime.
We double the memory, it’ll go down to 15%. We’ll have queries that are, instead of being, you know, slow once every three times, they’ll be slow once every some other number of times.
That math I completely skipped out on. So sorry about that. Anyway, these are the kind of stuff, these are the kind of things that you might have to deal with either if you’re a full-time employee or a consultant or, you know, you’re just some schlub that got pulled in off the street and someone said, hey, you look like you can tune a query.
What’s your name? Come look at this. You might be able to find all of these things. You might be able to present reasonable options for them. The use of drop clean buffers, specifically, can help illustrate what happens to a query that, like, you know, one out of every, however many executions, the data isn’t in memory.
And that can be a really powerful thing to show people to get them to buy into either, you know, changing the indexes so that we have a better index to support the query or adding memory to a server so that we have a less variable workload where some, like, we increase the odds that the data that we care about is already in the buffer pool and we don’t have to go to disk for stuff.
So, that is when I use drop clean buffers during a query tuning exercise is to either say, look, we can add an index and we can have it be pretty reliably fast or if we already have good indexes and we still have that variability, say, this is what happens when our data isn’t in memory, this is what happens when it is in memory because going back to that first example and I’m just going to backtrack a little bit here, you know, and, you know, these numbers are pretty small because I am on fantastic hardware.
Fantastic hardware. But let’s say that, you know, you’re on less fantastic hardware or let’s say that you have, there are way more tables involved and these things are way more out of control.
You know, just showing someone that, you know, a query is this slow when the data isn’t in memory, which happens once out of every however many executions depending on what those page IO latch weights are and this is how fast the query is when everything’s already in memory can be a really good tool for showing someone that, you know, either, again, we don’t have good indexes in place or we don’t have a good amount of memory in place.
So you don’t need to use drop clean buffers all the time. Drop clean buffers, recompile, cardinality estimation hints, these are all very situational tools that you want to use to fix very specific problems or to see if, to see if you can, see if, like, adding that in addresses whatever the problem with the query is, right?
There are all sorts of things that, you know, recompile can help with that might say, okay, well, you know, we can recompile this query all the time or we can turn it into dynamic SQL or a substore procedure or something like that.
So there’s all sorts of very situational query tuning tools that you can use for this stuff. Drop clean buffers is one of them, but it’s not going to, it’s not going to make your, it’s not going to prove that you tuned a query by using it necessarily.
It might actually just show that, you know, what is wrong with the query sometimes, right? So keep this stuff in mind. You know, I think, you know, it’s really important, it is important to show that a query is faster sort of regardless of where data lives, but it’s also important to show that the data might not always be where you want it to be and you might have to, you just might need more memory or you might need to make better use of the memory you have with better indexes, better index compression, stuff like that.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will like and subscribe and hire me. I hope that, I don’t know, I just, I want the best for us both as a wise Canadian lady once said.
Or, that wasn’t quite it. She was being a little bit meaner about that, wasn’t she? Yeah, she was mad about something about a movie theater. I forget the details.
I haven’t heard that song in a very long time. Anyway, I’m going to go, oh crap, I got work to do so I’m going to do that. Anyway, 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.