A Little About Table Variable Deferred Compilation Depth In SQL Server
Video Summary
In this video, I delve into the fascinating world of table variable deferred compilation in SQL Server, specifically exploring how multiple inserts affect its behavior. I walk you through a stored procedure that demonstrates inserting rows into a table variable and then querying it to see if SQL Server defers compilation after each insert. The results are quite enlightening, revealing that while initial executions show promising deferred compilation, subsequent runs start behaving more like parameter sniffing, retaining cardinality estimates from previous queries. This video aims to shed light on this lesser-known aspect of table variables and might help you troubleshoot strange behaviors in your own SQL Server environments. Whether you’re using SQL Server 2019 or higher compatibility levels, understanding these nuances can be crucial for optimizing query performance.
Full Transcript
Erik Darling here with Darling Data. And we’ve spent some time this week talking about different sort of cardinality estimates for things that don’t really get great cardinality estimates. And one of the things that I talked about earlier in the week, I think it was on Tuesday, was that up to a certain point, table variables behaved a lot like local variables and that you would, they would sort of compile with the batch, but would not get cardinality estimates. a lot of information until table variable deferred compilation came along. And this made me think a little bit because, you know, in the past I’ve definitely tested stored procedures just to be like, okay, if I run this once and the table variable gets a thousand rows in it, and the queries that touch it now can see that there are 1000 rows in the table variable, and then I rerun this and I put like a hundred thousand rows in the table variable, what happens? And it turns out that… the behavior of table variables turns into what was essentially the behavior of table valued parameters in the past, which was that they would sort of act like parameters and the cardinality of the table value parameter would get sniffed and reused over the course of a query plan.
So like to me that was sort of interesting, but then it made me wonder like, well, what if I insert rows into a table variable multiple times? Will SQL Server defer compilation for multiple inserts? And so that is the question we are going to answer today. We are going to judge the depth of table variable deferred compilation. We are going to judge its depth like we are at a powerlifting meet and we want to make sure that it is squatted deep enough to get all white lights. We want to see the crease of the hip. We want to see the butt a little below parallel. We don’t want anyone cheating on their squats saying, oh, I squatted 1300 pounds.
Your butt didn’t even break even, pal. Anyway, if you look down in the video description, you will see many helpful links. Most of the help that those links will provide is in the form of giving me money. Hire me for consulting, buy my training, become a supporting channel member.
And if you want to ask me questions for my office hours episodes, you can do that. Otherwise, please do like, subscribe, tell a friend. Assuming that your friends are physically existent in the world and capable of subscribing to a YouTube channel.
But if your AI girlfriend has a YouTube account, I’m willing to accept AI girlfriends. They remain your girlfriends. They remain your girlfriends, but I’m willing to accept subscribers in the form of AI girlfriends. All right. Married man. Don’t mess with me.
All right. So leaving the house, of course, I get to go to Utrecht. I don’t know. I made what I thought was a funny joke, but I don’t know if anyone laughed at it. It was, I trekked, you trekked. We all trekked to Utrecht. All right. That was funny.
I don’t know. I’m a little deflated on that one. Sorry. Delivery was lacking a little life. October 1st to 2nd, I will be there with the nice folks from Red Gate who are bringing Pass on Tour. Well, they brought me to New York City. That was very nice. Dallas and now Utrecht.
And of course, I will be in Seattle for the Pass Data Community Summit, November 17th to 21st, with two rock’em sock’em days of pre-cons with Ms. Kendra Little about T-SQL and all of the wonderful things that happen when you use T-SQL.
All right. The life changer that is T-SQL. There we go, PowerPoint. Only took you five seconds to catch up with that. Click. Great. Wonderful. Let’s party. Let’s look at table variable deferred compilation depth.
So what I wanted to test with this store procedure is, like I said, multiple inserts. So here we have a store procedure that accepts one user input, right? So we are still going, we’re not using a table valued parameter here.
We are just going to use a regular old table variable. But the table variable we are using will acquire the table variable deferred compilation intelligent query processing feature. And we will get on initial compilation a guess, right?
SQL Server will give us table cardinality for that. So if I run this and, you know, run all the code, we should see something interesting, shouldn’t we? So here we have user IDs, right?
We’re going to insert into that table. We’re going to select all of the user IDs that match our user ID first from the users table. And then do a count. So we got some cardinality to estimate.
Then we’ll do everything from the badges table. And then we’ll do everything from the comments table, right? So we got that here.
Then we’ll do everything from the post table. Oh, post, that’s a big one. And then since there are two columns in the post tables that have user IDs in them, I decided to do both of them, right? So we’ll do last editor user ID too.
And then finally, we will do votes. And what I want to see here is if after each insert, SQL Server actually defers compilation of the count query until all the rows are inserted, right? So that’s a good one.
So let’s run this. We have actual execution plans enabled. And when we run this, this will take a couple of seconds to run. No, because we don’t have any good indexes in place. That’s fine, though.
We got time to kill, right? You’re not busy. So we have every other query will be interesting to us. So let’s look at how these pair up. So we have the clustered insert from users, right, into our table variable.
That inserts one row. And we get a one row estimate when it’s selecting from the table variable. That is correct so far. That is not incorrect.
One might see a one row estimate from a table variable and think, my goodness, but the insert actually did only insert one row. So as we get down further and we insert user IDs, say, from now the badges table, we add another 9,363. And now look, our clustered index scan says 9,364.
This is great news. We have actually deferred compilation of the second select until later. Right?
This is great for us. Now when we hit comments, we add 46,7… Can you imagine leaving 46,737 comments? My Lord.
What are you doing all day? But this is interesting though because now our clustered index scan says 56,101. Which, you know, I’m not a math person, but I’m fairly sure that if you added that to that, you would get that.
So that makes sense to me. And if SSMS reframes this one more damn time, I am going to lose my entire mind. So now, let’s see.
Now we’re inserting into user IDs, selecting from posts where owner user ID fits up there. And we add 27,901. So this person has left almost 47,000 comments and posted almost 28,000 questions and answers. So that is insane to me.
But when we add the 27,000 to what we had before, look at our estimate. It holds up very nicely. This is great. And if we scroll all the way down here, we will see this pattern repeat where every time we add rows, we get the right sort of guess from our select from the table variable.
All right. Well, that’s good for a first execution, right? But now let’s do a second execution.
My favorite user in the Stack Overflow database is someone named Eggs McLaren. They’re a dummy account. And let’s see what happens now. So let’s do this.
We’ll enjoy ourselves here a little bit. And if we look at this execution plan now, well, this is still good, right? One of one, one of one. But if you notice down here, we seem to be retaining cardinality estimates from the past execution.
And so we’re not really deferring the compilation anymore, are we? We have sort of given up on deferring compilation because now we’re getting guesses of like 69 of 9,300. And if we scroll down a little further and we click here, so hopefully SSMS stops moving, then we’ll start getting, you know, like we don’t get a good guess here.
I mean, I realize this is the insert, but like we’re retaining cardinality estimates from like not only the insert queries, but also the select queries. We can only defer compilation so much.
So now we’re getting 91 of 56,000 and 65 of 27,901. And if we scroll down further, we will see this pattern repeat itself. So at least on the initial execution of a stored procedure, we do okay, right?
SQL Server is like, well, every time I do an insert, I’m going to defer compilation of the select from the table variable immediately afterwards until after that insert completes and we get table level cardinality. Golly and gosh, that’s wonderful.
I’ve never, never been so happy in my life. The problem becomes that on further executions, the table variable acts like a parameter. And now we sort of have table variable sniffing, which I don’t know if that’s good or bad, but it seems, it just seems like a fun new problem to be aware of and have to go solve for people.
So maybe this video will arm you to go do that. I don’t know. I don’t know if you’re using SQL Server 2019. I don’t know if you’re using compat level 150 or higher.
I don’t know if you have disabled the table variable deferred compilation database scope configuration. And moreover, I don’t know if you’re using table variables, but if you are and things are behaving strangely, this might just answer some questions for you.
Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where, I don’t know. I don’t know what we’re going to do yet.
I believe it’s going to be a Friday though. So it will be necessarily amusing, right? Because Fridays have to be fun. Fridays aren’t fun. Might as well all just…
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.