A Little About Automatic Tuning In SQL Server
Thanks for watching!
Video Summary
In this video, I revisit the topic of automatic tuning in SQL Server, addressing some of the feedback from a previous discussion. After Brent commented that he felt I wasn’t being fair in my critique, I decided to delve deeper into his example and provide a balanced perspective. The video walks through setting up the environment with Stack Overflow 2013 data, creating necessary functions and indexes, and altering database compatibility levels to observe automatic tuning in action. By using SQL Query Stress for multiple executions, we were able to generate enough data to trigger automatic plan recommendations, highlighting that five executions are indeed insufficient for meaningful results. This exploration demonstrates the importance of thorough testing when relying on automatic tuning features, ensuring that your queries receive optimal performance over time.
Full Transcript
Erik Darling here with Darling Data. And in today’s video, we are going to revisit automatic tuning again. Because, obviously, I talked about automatic tuning in, well, now yesterday’s video. And, you know, Brent was having problems with automatic tuning, Jessica. I’m just making fun of the transcript. The way that things get transcribed in YouTube, I’m sure things look dumb in mine. But then, Brent commented on YouTube, that I’m not being fair. And also on LinkedIn, that I’m not being fair. And so I had a little chat with Bats Maru. And Bats Maru said, be fair. So in today’s video, we are going to be fair. Because if there’s one thing we care about here at Darling Data, it is fairness. So let’s walk through Brent’s example, which involves, I’ve just I’ve just reformatted things a little bit because it’s not specific to Brent. Everyone else’s query formatting gives me a headache. So I just reformat, move things around a little bit. But this is, you know, we’re using Stack Overflow 2013, not the full Stack Overflow database. Apparently everyone who says disks are cheap has never bought disks from Lenovo. So we’re using a slightly smaller version of the database here. But I’ve created the function and I’ve added the isValid to the table.
And that’s not my typo. Don’t yell at me. I’ve created an index on reputation and I’ve created the getTopUsersStore procedure. Well, actually, maybe I cut that off when I was moving stuff around. Anyway, that thing’s in there. I promise. Otherwise this thing would just throw errors, right?
And so what I’m going to do is follow along from here where we alter the database compat level to 110. We change the database scope configuration and we mess a little bit with QueryStore. And in Brent’s example, he had only executed the store procedure five times, which is an inadequate amount of sampling for the automatic tuning feature. Now, automatic, no, I went hunting through the Query, not QueryStore, the extended events GUI because I wanted to figure out if there were any events that would fire around automatic tuning. And there are a whole bunch of them. So I just created a session with all the ones that I thought looked interesting in there. And that’s what, that’s the live data that we’re watching over here. All right, cool. So we’ve got this thing watching our query, watching our server very carefully. And I’ve also got the script from yesterday fired up, ready to go.
So the first thing I’m going to do is rather than just execute the procedure five times, we’re going to go a little bit, we’re going to go a little bit harder than that. And we’re going to use SQL query stress if it’ll actually show up on the screen. Where are you, SQL query stress? There you are. No, that’s a new window. Oh, apparently that other window was frozen. Okay, good. Let’s load settings. Thanks. Thanks. Thanks for making me look like an amateur SQL query stress. It’s real cool.
Let’s put that in there. And then I’m going to just for this one, I’m going to do 100 and 100 to make it an even a lot of executions. And that all does, I don’t know, pretty quick, right? And if we chop off a zero there and a zero there, we’re going to get ready to do the next one.
Now, right now, we don’t have any data in here, right? Nothing is showing up here, and nothing will be showing up in here. But what’s really funny is that in Compat Level 110, we can’t even run this query to check on things. Even if I add an optimized, like a Compat Level hint to this query for 150 or 160, we can’t run this query with JSON in it because it’s not available under Compat Level 110. Would that I had a big enough hand to smack everyone at Microsoft who makes these decisions? I would gladly do it. It would just be an endless slap for, I don’t know, probably 10 years.
Anyway, you’re just going to have to take my word for it that there’s nothing in here and there’s not, well, obviously nothing in the extended event. So that’s great. So now let’s flip the Compat Level to 160. And we’re going to just do this for 10 by 10. And this is going to be significantly slower. But if we come over here and watch this, eventually we’ll get some data in here. It takes a little bit though.
This thing takes a lot longer to run under Compat Level 160. And of course, you know, the actual feedback for the event takes a somewhat significant number of executions before it starts thinking about regressions and making guesses and figuring out if things need to be changing. But there we go.
Miraculously, around 20 executions now, we have a regression check. And now since we are in Compat Level 160, we can run our JSON query. And we have some advice in here. All right. Average query time changed from 7.56 milliseconds to one, well, 15.2 seconds. And we have some stuff over here where just like with my example query, there was, you know, some information about which query we should force and which query IDs were involved. So let’s take that and let’s put that in there and let’s stop SQL query stress so that we don’t have another weird crash thing going on in there. I’m not sure why SQL query stress had a problem. But if we look in query store, we will see query ID one had two plans. And since this is sorted by average CPU descending, this will be the slow one. And this will be the fast one. We got 50 executions out of that and 10,000 executions out of that. So yeah, there’s actually stuff in there. Now, sort of interesting, maybe, I don’t know, vaguely interesting is if we flip compat the compat level back to 110 and we add some zeros back in here.
I don’t know exactly how interesting this is. And we run this a whole bunch of times. The live data view from here will actually show this automatic tuning check abandoned thing for our query. Like, see, there’s query ID one. That’s the one we are looking at. So at some point, this thing does, see, SQL Server does sort of give up on this one, because there are a lot of errors in there. Now, that finished. And now you can see that I use a Lenovo and Lenovo did a software check. It was very interesting stuff in my life. And now if we flip the compat level back to 160, so I can run the JSON query again. Sometimes this will say that the query is too error prone. It’s not happening here, but at least, I don’t know, I ran through this a few times. And like, there was one time where it said for the under reason, it was like, this query is too error prone, we can’t we can’t handle doing this. It only happened to know, like I said, every once in a while, over here, it says it is not error prone. But at least one time, maybe probably actually at least two times, it did say it was error prone.
I’m not sure why SQL Server changed its mind. Maybe I just ran this thing enough that it’s changed its mind about that. I’m not sure I couldn’t tell you. But anyway, the moral of the story here is that five times is not enough, not enough executions to get the automatic tuning stuff to kick in.
But if you run stuff a lot using I don’t know, you could use O stress if you’re feeling command liney. But SQL query stress does a pretty good job of executing stuff enough to trigger the automatic tuning, at least recommendations. And if you have the automatic plan forcing stuff on, it’ll force the plan for you.
Anyway, I think that’s about that. So we can probably stop this here. Me and Bats Maru are achieved peak fairness for the day. So we’re going to go celebrate now. I don’t know what we’re going to do to celebrate. Bats has some crazy ideas.
Bats has some crazy stuff to say. But anyway, that’s that. Execute the stored procedure more. Something will happen eventually. Anyway, thanks for watching. Hope you enjoyed yourselves. I hope everyone learned something.
And as always, my rates are reasonable.
Video Summary
In this video, I dive into the world of automatic tuning in SQL Server, addressing some concerns that arose when my friend Brent struggled to get it working properly. I walk through a detailed demonstration using SSMS and a sample query provided by Microsoft, showcasing how to set up and test the feature while emphasizing the importance of having sufficient data in Query Store for the system to make meaningful recommendations. By exploring real-world scenarios and potential pitfalls like parameter sniffing, I aim to provide clarity on when and how automatic tuning can be beneficial, as well as offer practical advice for those looking to implement it effectively.
Full Transcript
Erik Darling here with Darling Data. And in today’s video, we’re going to talk about automatic tuning in SQL Server. And the reason why is because I’ve had a couple people tell me that my friend Brent ran into some trouble trying to get this feature to work. And I want to make sure that we can alleviate Brent of his worries and sorrows. So Brent released a video about not being able to get this thing to work. And it says, I struggle with this. I struggle with automatic tuning Jessica. Well, that’s, that’s worrisome enough isn’t it? It’s terrifying enough on its own. But then I, I posted a video about, uh, cardinality estimation feedback and Flagstar seven days ago said, this reminds me of Brent’s recent video. With him being unable to get automatic tuning to work. I have no idea what he was doing wrong. Okay. Fair enough. And then I opened a, a, an issue on my, my GitHub repo for SP Quickie Store. And then I opened a, a, an issue on my, my GitHub repo for SPQuickie store and then I opened a, an issue on my, my GitHub repo for SP Quickie store and it says, I don’t know what he was doing wrong. But then I opened an issue on my GitHub repo for SP Quickie store and it’s not And I’ve thought that maybe, you know, I don’t really do anything with this dynamic management view, but, you know, I thought maybe it might make a good addition to Quickie Store.
Maybe we get some additional query feedback stuff on it. And Reese Goading said, does Sys.dmdb tuning recommendations even work? Brent recently tried his best to get anything out of it.
As I recall, he failed. Oh, gosh. This sounds like real trouble. This sounds like a job for Darling Date. So let’s come over to SSMS.
And I’ve cleared out Query Store here because I don’t want anything interfering. And I’ve set CompatLevel to 150 because the parameter-sensitive plan optimization makes stuff in getting this demo to work really confusing.
You don’t want to be in CompatLevel 160 for this. And I’ve got this query over here that Microsoft provided. As you can see, it returns no rows currently, which is apparently Brent’s experience as well. But don’t worry.
We will get at least one row out of this. I promise. Now, I’ve already got this index created. So everything is good here. We’ve got that index.
And we’ve got a store procedure here that will get the total sum of scores based on a parent ID and a post type ID out of the post table. No, it’s not much to look at. But it’ll get us where we’re going.
It’s just enough to make this whole thing work. Now, let me show you why this feature will kick in for this procedure. If we run these two executions of the procedure back to back, the first one returns a result almost instantly, which is great.
Everyone wants instant results. Isn’t that nice? All right.
And the second one takes a little bit longer. All right. So here’s the first one. It takes exactly one millisecond there. That’s pretty good. And this one down here, this takes eight seconds. That’s not so good.
That sounds like parameter sniffing to me. Could be a big problem, couldn’t it? All right. So let’s make sure that we have a reasonable baseline sitting around in our database here for SQL Server to work with. The big idea here is to have enough of a baseline in Query Store for it to be able to do something.
Now, I’m going to kick this one off to execute. And this is going to take around about 45 seconds to a minute. And while this thing executes and does its thing, let’s come over here and let’s look at this wonderful free script that I got from Microsoft’s page on the Sys.dmdb tuning recommendations dynamic management view, where, you know, we got all this stuff.
We got a JSON value and we’ve got some ifing in here. That’s quite nice. And then down in here, we select from the, that didn’t frame up too nicely.
So we’ve got to select from the DMV and we’ve got to cross apply to some open JSON stuff. Why? I don’t know.
Some real big wrinkly brain over there thought, I have an idea. JSON’s new. Let’s cram a bunch of crap into JSON. Let’s make it, let’s make it as hard as possible for people to get reasonable information out of this.
All right. Yeah. Great idea. Why don’t, why don’t you just use some more XML? Why don’t you just follow query plans and, uh, and extended events and, uh, the block process report and the deadlock XML report. So I could at least reuse some of my XML knowledge here.
No, just jam it all in JSON. I’m sure it was three nanoseconds faster to do that. So anyway, of course, this thing runs and it finishes. Look at that.
44.8 seconds. I was almost right about 45 seconds. Now, with just those two things having run, we still don’t have a row in here. We don’t have a row in here because we still only have one plan. We’ve got nothing for SQL Server to compare it to.
So we’re going to come back over here and we’re going to hit SP recompile here. And then we’re going to run these two store procedures back to back. All right.
And this time, do they both run faster? Yeah, sort of. I mean, the one that was really slow before runs a lot faster. We went from eight seconds down to 650 milliseconds about. But the one that ran really quick before in like one millisecond now takes 600 milliseconds.
Okay. Fair enough. Let’s hit, let’s, to not interfere with, with query store or the plan cache or anything. Let’s hit SP recompile there.
And now I’m just going to run this one 30 times for the one that was kind of slow before. This is going to do some more. This is going to do more work a little bit more quickly than the last time around. It’s not going to take 45 seconds this time.
Excuse me. Because we’re using that clustered index scan plan, this should finish up in around about 20 or so seconds. Oh, 13 seconds. Oh, you know what? I’d rather under promise and over deliver than anything else.
But now with 30 executions of that around, when we run this, magically we have a row. Don’t worry, Brenty. I gotcha.
So what is this telling us? Well, average CPU time changed from 56 milliseconds to 7.689 seconds, right? 7,600 milliseconds.
And if we scroll all the way over here, we’ll have some advice from this DMV. And it will be that, oops, I, I messed that whole thing up. Ah, there we go.
Let’s slide that back. That we should force for query ID 10 plan ID 9. Okay. Well, here’s query ID 10. The regressed plan ID is 11.
The recommended plan ID is 9. Okay. Well, let’s see. Let’s take query ID 10 right here. Let’s copy that. And let’s use my free, amazing, immaculate store procedure. Oh, that didn’t, what the hell?
Oh, it must have copied the, must have copied the column name. I forgot that that’s a default thing you can set. So let’s look for a query ID 10. That is the right one, isn’t it? Query ID number 10.
Let’s just double check and make sure. Query ID 10. Wonderful. We’re going to run that. And now let’s, let’s, let’s, let’s refresh our mind of what SQL Server was telling us. The regressed plan ID is 11.
So let’s go look at the regressed plan ID, right? That’s going to be plan ID number 11 right here. And this is the clustered index scan. Okay. Now the, the good plan ID it says is nine, right?
That’s this one, the one that says we should force right here. If we come over here and we look for plan ID nine, that is the clustered index seek with the key lookup. Now, would you want to force this plan?
It’s a good question because for one set of parameters, that’s a pretty good, that’s a pretty good plan to force. For another set of parameters, that’s a pretty bad plan to force. So should you follow this advice?
Probably not. Probably not a great idea. Now I will, I will, I will grant SQL Server a little bit of grace here that in the query plan that it says was regressed, there is a missing index request that would probably help things out here, but it doesn’t, it doesn’t tell you to create the index.
It tells you to force the plan. And I don’t really know that I love that so much. So what should you do here? Well, what I would recommend is not, maybe not listening to the advice of the tuning recommendations query. Because if you force that plan for that query, you will have some that are very quick and some that are very slow.
What I would probably recommend you doing is tuning that query. Maybe that missing index request would have been the thing to do it. But I think what’s generally valuable about this view is not necessarily the advice that it gives you about which plan to force, but really that queries are ending up in there with some discrepance, with some, with some obvious signs of regression that you might want to address.
Right? So this is sort of a typical parameter sniffing scenario where something about the query plans that it generates for one set of parameters and another just don’t get along so well. So what I would recommend you looking at the two different query plans, figuring out what’s different about them, figuring out what you can fix in order to make that plan as shareable, as simpatico as possible across as many different parameter variations as you can think of, and then going from there.
So as usual, Microsoft wanted to do something like AI, ML, really, you know, a self-tuning database that we’ve been reading about since 2000. Nonsense.
Didn’t, doesn’t quite deliver. But we got kind of a cool new DMV that can help us find queries that could use our help. You, it’s, we still need to do the helping. And I’m sure that there actually might even be cases where forcing a query plan would actually do you some good.
It might happen. It just didn’t happen for me here. So, uh, it does work. And I hope that, uh, Brent can sleep well now.
I hope that we can all just move on from this, this, this tragic, tragic chain of events in the SQL Server community. I hope that we can begin to heal and process the devastation that has befallen us. Brent couldn’t figure it out.
Well, anyway. Uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you’ll watch other videos of mine. Because who knows what else I’ll figure out.
Alright. Goodbye. Goodbye. Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.