Capturing Query Recompilations With sp_HumanEvents
Video Summary
In this video, I delve into the intricacies of query recompilations in SQL Server using SP_HumanEvents extended events to track them effectively. I explore why recompilations occur and how they can impact performance, especially when a query that was previously humming along suddenly starts using a poor execution plan. By understanding these recompilations, you can better diagnose issues and optimize your queries, ensuring smoother operations even in high-volume workloads. I also discuss common causes of recompilation such as schema changes, statistics updates, option recompile hints, and temporary table modifications, providing practical advice on how to address each scenario.
Full Transcript
Erik Darling here with Darling Data. And I think this will probably be my last video of the day because what I have discovered is the natural light that comes in through my window has a profound effect on the effectiveness of the green screen behind me. Contrary to popular belief, I do not live in SQL Server Management Studio land. I live in SQL Server Management Studio land. live in my office. And I just have SQL Server Management Studio as a backdrop for everything. I know there’s a green screen behind me and the later in the day it gets, the more distortion there is when I move around a little bit. And I don’t want you to have a bad watching experience on account of it being later in the day. So we’re going to make this the last one. And we’re going to talk about using SP underscore human events to track queries that are in the day. So we’re going to talk about using SP underscore human events to track queries that are recompiling on your server. Are query recompilations the biggest deal in the world? Maybe not exactly. But what I’ll tell you here is that if your query is humming along, reusing an execution plan, everything is doing great, and then something happens, you swear to me nothing has changed, but something has happened.
And all of a sudden, and all of a sudden, and all of a sudden, your query starts using a poor execution plan. Well, guess what? You might want to know why it recompiled. Because what you’re going to do is you’re going to send me an email, or you’re going to ask a question on stack exchange or stack space overflow. You’re going to say, hey, nothing changed, but all of a sudden, this query got slow. And everyone’s going to say, well, something changed, because you stopped using a query plan. It didn’t, and the recompilation could be a lot of things, right? Like a plan could get evicted from the plan cache because of memory pressure or something like that. Someone could clear out the plan cache. There’s a lot of reasons why you might find things happen. But those things would fall in our query compilations.
Recompilations, probably the most common reasons you’ll see would be from this list. Schema changing, statistics changing, you know, automatic statistics updates, or manual statistics updates during the course of your high volume, four batch requests a second volume workload. So, you know, temp tables changing. So, you know, temp tables changing. This doesn’t mean that, like, the definition of your temp tables change. What it means is that you have hit a threshold and modifications to the cached temp table that have call a SQL Server to recompile, which is an interesting one.
And then, of course, the ever-present option recompile requested, which, because I’m a bit of a lazy bones, is what I have asked queries to do, is request an option recompile via the option recompile hint. And that’s what I’m going to show you over in the extended event data. There are a lot of other reasons why you might see a recompile happen, depending on the, depending on local factors, the way that you use SQL Server, the way that the vendor has written queries to use SQL Server.
Query store stuff, obviously, that’s, you know, there’s a lot of, well, there’s a few query store things in here. Not a recompile is a funny one. But, anyway, there are a lot of things in here that you might see, but, you know, these are the most common, I think, that I see.
Probably statistics changed, temp table changed, option recompile. There are not a lot of people who are adding and dropping indexes or columns or other things like that in the middle of the day. So, maybe this one is not as common as I think.
But, anyway, this query setup will look pretty close to the query setup that I used for the compilations event that we looked at in the last video. Except this one is properly written dynamic SQL. It is parameterized.
Here, we have used sp execute SQL to pass a parameter value in on each execution. But, in this case, we have asked SQL Server quite nicely to recompile this query every single time it shows up. And, what we will see in the extended event that SP Human Events has so graciously set up for us is this data.
We see the name of the event. Very helpful. Thank you.
Extended events. We see when it happened. Already dating this video. We see the statement. And, now, since there’s an option recompile on here, it’s going to be fairly obvious what happened. But, in other cases, maybe where there’s not an option recompile, either at the statement level or at the store procedure level, it might not be so obvious.
And, then we will have the recompile cause over here. Option recompile requested. So, we’ve gotten a wealth of useful data out of this.
How we go about addressing the recompilation stuff kind of depends on the situation. There might be some queries where the option recompile is a good idea. Might be some queries where it’s not and you can get rid of it.
If the recompile cause is something that is not the fault of the query, something like schema changed or statistics changed, you might look at looking at what processes are changing tables in the middle of a workload. I imagine there might be some blocking involved.
Sorry, tables are indexes, columns, indexes, something like that. That might cause some significant blocking depending on other local factors. The statistics update thing, we might look at choosing to manually update statistics at some other interval if it’s causing a problem.
We might look at, if the temp table is changing, we might look at the keep plan or keep fixed plan query hint. And if it’s option recompile, we would just have to figure out if the recompile is a necessary hint for the query in order for it to run well. A lot of the times you’ll see the option recompile hint perhaps on reporting queries where you don’t care.
But if the option recompile hint is attached to a query just to fix parameter sniffing, like you decided you like to be like the one person who doesn’t use the local variable thing incorrectly to fix parameters. Like fix parameter sniffing, local variable, do this to fix parameter. Again, babies.
Then that might be another way around it. There are lots of things in here. If you run into one of the weirder ones, like four brows or set options changing or cursor options changing, well, you’ve got a really strange set of problems that you have to deal with. Boy, oh boy.
Don’t envy that. Anyway, before this green screen artifacting gets any worse, I’m going to end this video. Say thank you for watching. I hope you enjoyed yourself.
And I hope if you’re able to, if you have the constitution and the will and the means that you’re able to start drinking like I’m about to do. So if not, I don’t know, have a cup of tea. Eat a cookie, whatever you’re into.
Make sure it’s healthy and you have the consent of those around you. Consent and support of those around you. Anyway, have a good night.
Listen, you have to know if I can talk about something. I’ll show you. You have to see that dish half minhawhat. 공vakia now. There’s three minutes where I can think of this60нос around you. So let’s get started.
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.