Stored Procedure IF Branching and Deferred Compilation In SQL Server
Video Summary
In this video, I delve into the concept of deferred compilation in SQL Server, specifically focusing on how it can mitigate issues related to if branching within stored procedures. I explain that when SQL Server initially compiles a plan for a procedure containing if branches, it often does so based on initial parameter values, which can lead to unexpected behavior later when different parameters are passed. To illustrate this, I demonstrate an example using temporary tables and show how deferred compilation can help in such scenarios by deferring the actual compilation until the temp table is populated. Additionally, I provide tips for those interested in consulting services or training, including reasonable rates for my consulting work and membership options to join our channel community.
Full Transcript
Erik Darling here with Darling Data. And this video is going to be about how deferred compilation can help with some if branching issues that you might see in SQL Server. Primarily, one of the big problems that you run into with if branching is that all of the plans in your procedure will get compiled when SQL Server first compiles a plan for the store procedure, right? So if you have if branches and you’re thinking, well, the, like the compile time things that I pass in, SQL Server is never going to look at this branch because we’re not going there yet. Wrong. It compiles a plan for the entire procedure based on the initial set of compilation values. So if some of your parameters have null values assigned to them, well, you might, you might run into some very strange stuff when the, when that branch actually does execute because you will be executing it with values. So that can get very strange. So that can get very strange. But there are, there are of course exceptions to that. And I don’t mean like option recompile at the statement level or with recompile at the store procedure level. There are things in your store procedure that might make that untrue that cause deferred compilation. So we’re going to look at an example of that today. If you would like to hire me for consulting, because that’s what I do for a living. I consult and I make SQL Server.
I deliver faster in exchange for money. It’s my, it’s my gig. It’s my whole thing. And remember, my rates are reasonable. Yeah. All right. Been a while since I said that on camera, I think. Uh, if you’d like to buy my training also at a reasonable rate, you can do that. Uh, if you would like to become a channel member, you have a variety of reasonable rates to choose from. Uh, monthly plans are as low as $4. Four. Crazy, I know. Uh, if you would like to become a channel member, uh, that’s, that’s the way to do that. Uh, you can ask me office hours questions, uh, you, where that, that is free. Um, if you, it’s gonna be $25, $50, because, um, you’re spending clearly, you, you’re all spending far too much money on illicit substances. And, uh, it is degrading your ability to form coherent questions.
So, you need to start giving that money to me instead. I’m, I’m a safe keeper for, I am, I, I will get incoherent. Uh, and of course, if you like this channel content, please do like, subscribe, tell a friend, all that good stuff. Uh, you can tell a family member too, if you have a family member who you would like to torture with. These videos. Uh, advanced T-SQL training will be coming to DataTune Nashville March 6th and 7th and March 13th and 14th at Data Saturday Chicago. Uh, two wonderful events. Get out there in the world, you know, wear some sunglasses, look cool. Maybe we can get incoherent together. Who knows?
Uh, two wonderful events. Don’t know what that would look like. But, uh, for now, we must, we must muddle through December, January and February, and we must do that somehow. All right. Let’s look at this thing. So, um, when you use temporary objects in store procedures, um, for everybody, that’s going to mean temp tables. For people in certain situations, uh, certain SQL Server situations, uh, that would also mean table variables.
If you are getting the table variable deferred compilation, uh, intelligent query processing feature, uh, in your query plans. Um, only you can find that out. I can’t tell you that. You have to figure that out. But for temp tables, uh, you can see this happening. So, um, let’s actually add, uh, down here. I want to add a dbcc free proc cache.
Go just in case. So we’re going to look at this store procedure. Uh, I mean, a temp table is going to get created, but it’s not going to get used, right? So like we’re going to have two joins to a temp table, the stuff, I don’t need to put data in the temp table. It’s just to show you that the temp table, uh, will cause deferred compilation. Right.
And we’re going to have that in both of these queries, but let’s make sure this store procedure is run to not use a temp table. And let’s do a dbcc free proc caching and let’s run the store procedure using the reputation parameter first, right? So if we do this and we look at the execution plan and granted, I haven’t created any helpful indexes for this.
That’s really not a performance demo. It’s just a behavioral demo. And we look at the parameter list. We will see that, uh, over here we have, uh, compile and runtime values for, uh, reputation is 500,000, right? So, uh, the initial compilation for the store procedure was done with reputation at 500,000.
And that is also getting the actual execution plan, the runtime value for this execution. The reputation parameter gets used in this query against the users table. The score parameter gets used against the post table in this query.
So, uh, if we run this now and we say, uh, for score equals nine, nine, nine, nine, that’s four nines. And we look at the execution plan and we do the same thing as last time. We look at the parameter.
Where are you hiding from me? There we go. We look at the parameter list. We will see that the runtime value was nine, nine, nine, nine. But that’s not right.
That’s not right. The, uh, compile time value for this is null. All right. So SQL Server sniffed a null value and it’s doing cardinality estimation based on that null value. We have a one row estimate here, right?
So we got 12 rows back. There was one when we SQL Server estimated one row for null. We found 12 rows, uh, uh, for based on what we were looking for, which I don’t know, is that weird? Maybe, but, um, you know, not, not really.
There were four rows with a score greater than or equal to nine, nine, nine, nine. But, uh, there was a one row estimate from the null compile time value. So, uh, this is, you know, something worth noting.
If you are, if you have lots of if branching in your store procedures and there are lots of different parameters that get used in different if branches. And as you pass things in, you might actually supply different values up here.
Things can get pretty weird, right? It’s not, it’s like, like a, like another layer to a parameter sensitivity issues. But what I want to show you now is if we go and we say we put these left joins in, and I’m only using these very spurious left joins to show you that the behavior of involving a temp table.
This is the deferred compilation that you get, uh, from all temp tables and some table variables depending on your SQL Server version edition and, uh, all that good stuff. Uh, we rerun this and recreate the store procedure.
Let’s clear out the plan cache just to make sure. And what we’re going to do is the same old boring thing here. Well, we run this for reputation equals 500,000 first. And we’re going to get about the same thing happen for the first execution where the, um, compile and runtime values are both 500,000.
But now if we run this for score equals 9, 9, 9, 9. And we look at the, look at the execution plan. Of course, you got to flip that around a little bit.
Now in our parameter list, we have a compile and runtime value of 9, 9, 9, 9. So if you’ve heard me go on and on about if branching and store procedures and how, you know, like, like, like the behavior that we looked at without the temp tables can mess you up. Um, if you’re using temp tables in your if branches, you might not see that exact behavior because SQL Server might be deferring compilation of those, uh, queries until, uh, the temp table is populated, uh, and the query runs against them.
Right. So until SQL Server has to compile a plan that uses that temp table, then you might see this instead of the other behavior where it’s sniffed and null, like in the last set of, uh, runs of this. So just kind of something kind of interesting there.
Um, that’s about it on this one is usual. I don’t know if that there’s a very loud truck horn outside. I don’t know if that’s picking up on the microphone and it won’t know until the recording is complete, but it was perfectly timed with the silence there.
Anyway, I hope you enjoyed yourselves. I hope you’ll learn something and I will see you in tomorrow’s video. Uh, I forget what day tomorrow is.
I usually do, but we’re going to be there and we’re going to have fun. All right. Thank you for watching.
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.