In Memory Table Variables In SQL Server: Still Pretty Stupid
Thanks for watching!
Video Summary
In this video, I delve into the often-overlooked world of in-memory table variables, particularly focusing on their behavior within SQL Server. Starting with a lighthearted introduction to my database named “Trash,” where I’ve set up memory-optimized data and created a simple procedure to demonstrate the quirks of these variables, I aim to highlight both their limitations and potential pitfalls. Through detailed query plans and cardinality estimations under different compatibility levels, I explore how SQL Server handles these table variables, revealing that even with full knowledge of the row count, it often fails to optimize queries effectively. By comparing behaviors across compat levels 140 and 150, I underscore the importance of understanding the implications of these changes for your database performance.
Full Transcript
Erik Darling here with Darling Data, and uh, doing my best to look like a real serious, real serious, take me seriously consultant. I’m gonna stand, look tough, right? I’m gonna butch things up a little bit. I’m not really sure what else I could do aside from get some face tattoos, but I don’t know. I think, I don’t think my mother would like that very much, so we’ll, we’ll probably skip on upsetting Mrs. Darling at this, at this point in her life. We’ll, we’ll wait, we’ll wait until it’s safe. Then, then we’ll get face tattoos. Alright. In this video, we’re gonna talk about how stupid in-memory table variables are. Uh, every once in a while, you know, some, some, some, some Microsoft added in-memory stuff to SQL Server in 2014, and every once in a while, they’ll write some, like, some, like, just lethargic driftwood blog post. Like, please use these, please use this feature. Someone, someone, please use this thing. Uh, there, there, there are, like, two good use cases for in-memory anything in SQL Server. Uh, I’ve yet to find a really good use case for in-memory table variables. Uh, in-memory regular table, tables, not table variables, regular in-memory tables can be pretty good shock absorber tables. You might find online gambling companies use them to some great effect. You might find, uh, online ordering companies use them to some great effect during, like, very busy times, like, you know, holidays, you know, special events, things like that. Um, where you have a small amount of in-memory data that stays hot for a short amount of time, which can then be transferred out to a disk-based table, uh, where once it’s, like, past the point where all the locking and latching stuff that, that goes on, uh, once that stops, right? Like, once, once the, once the hotness is over, once, like, you know, all the bets have been placed, you don’t need to avoid all the locking and latching anymore, because from then it’s just people figuring out if they want or not. It’s just a bunch of selects. It’s, uh, mostly the, the, the locking stuff and the latching stuff that people, people will care about for that.
In-memory table variables, I just never found a reason to care about them. Mostly because, uh, they’re not that different from regular table variables in a lot of important ways. So that’s what we’re going to look at today. So, before, before we get into that stuff, uh, if you, if you like the channel and you don’t want to start seeing, like, Geico commercials or something before, in the middle of, after every video, uh, you can, you can sign up for a, a, a membership here. They’re, they’re, they’re cheap. It’s, like, four bucks a month or something.
If you can’t do that, which I understand, not everyone can. Not, not, not everyone has an extra four bucks in their pocket at the end of the month. Uh, there was a time in my life when I, I, I usually didn’t have an extra four bucks in my pocket ever. Um, it was, it was all dedicated to a bar tab. Uh, you, you can do other things to, to let me know you care. Uh, you can like videos, you can comment on videos, and you can subscribe to the channel and join nearly 4,200 other data darlings out there in the, in the, in the, in the world who get notified when, when I publish these videos.
Uh, if you are in need of, uh, SQL Server Consulting of the performance, health, tuning, emergency, training variety, well, I got you covered on all that. If you need something else, let me know. My rates are reasonable. Uh, if you need low cost training, I got that too. You can get, well, 24 hours of performance, tuning content at the beginner, intermediate, and advanced levels.
I guess that says expert, huh? Expert levels, uh, for about 150 bucks US when you apply these, the discount code spring cleaning. Um, when I set that code up, uh, I, I, I had just switched to a new video platform. Um, and, uh, it was springtime for Eric and, uh, I used that code cause it seemed funny and it’s just kind of stuck since then.
So, even though it’s at least currently August 15th or so, middle, mid August, uh, it’s, it’s always springtime at the darling data sale. So, um, it’s springtime for you as well. Now, uh, I, I will also be speaking in person at a couple events in the near future.
Friday, September 6th, I will be at Data Saturday Dallas. The full day pre-con on the 6th and at the Saturday event on the 7th where I will be, uh, delivering a couple regular sessions. And then November 4th and 5th, I have two full day pre-cons at PASS Summit in Seattle, uh, where me and Kendra Little will be teaching you really all that you need to know about SQL Server performance tuning over, over the course of a couple days.
So, if you want to, if you want to get a whole lot of performance tuning knowledge, a nice condensed block of time, there are a couple great ways to do it. I can also teach you how to make great images that make a lot of sense with AI. ChatGPT never lets you down.
Um, you know, ChatGPT is a funny thing because, uh, a lot, a lot of people are really pushing AI hard, Microsoft included. You know, the whole, the whole co-pilot thing. And, um, you know, I think one thing that’s really a good exercise for anybody, especially executives, who are like, AI is going to change the world, um, spend some time with it.
Ask it questions about something you know really well. Uh, ask it to do something you know a really great way how to do. And, and see, see if AI gives you an answer that, that is correct.
Or, uh, gives you a process that, that is, that is correct. Because I think you’ll be really surprised to find most of the time it doesn’t do the, most of the time you ask the, you know, any, any AI LLM tool to do something.
Or you ask it about something where you have a significant amount of experience, knowledge, and you can, you can, you have a good BS detector about this stuff. The answers you get back would terrify you.
I know they terrify me. Uh, one thing that gives me hope about AI is that whenever I ask it to write a query, it does all the same dumb stuff that regular developers do.
So I feel like me as a performance tuning consultant, I love AI. Because I’m going to be fixing the same 12 problems over and over and over again. Because no one knows any better.
Not even AI. AI messes everything up. All the same stuff that like a junior developer would mess up, AI messes up. It’s wonderful for me.
The future is so, I mean, I don’t know if the future is so bright, but these recording lights are very bright. I’m not going to start wearing sunglasses on video like some kind of antisocial, but man, I’m excited.
I’m excited to see what happens. So anyway, let’s look at how stupid in-memory table variables are. All right.
So, on to SQL Server Management Studio, where you’ll see that I have created a database called Trash. And I’ve created a database special for this because Microsoft, in its infinite wisdom, despite having 10 full years to work on this SQL Server feature, have not given us a way to turn off in-memory anything once we enable it for a database.
You can’t turn it off. It’s on forever. It’s there forever and ever.
You’re stuck with it. It is like a herpy. It does not leave your body. It’s just always there. It’s like a Lego piece that you stuck up your nose when you were seven.
Things never coming out. You’re just going to have to live with it. I think I have a Lego piece on this side. At least it feels that way most days.
So, I’ve created a database called Trash for this specific exercise because I want a database that I can drop and not think about ever again. Right?
And I’ve told SQL Server that this database contains memory-optimized data. And I have created a file group for this memory-optimized data that should give you a really good sense of exactly how I feel about in-memory table variables.
So, after I did that, and there was really not a whole lot of reason to revisit a lot of this stuff, what I did was create just a very abridged version of the post table in the Trash database with just a couple columns in it.
I don’t want to recreate the whole thing. I don’t need the whole table to show you how silly this is. And then I inserted all the data that I have from the Stack Overflow 2013 post table for those two columns.
And I updated statistics with a full scan. The reason why I wanted to do this is because I want to show you that SQL Server gets cardinality right at first. And then as soon as it’s in that table variable, it gets kind of forgetful.
Right? So, after that, I created a type, a table type, that is memory-optimized with an index on the two columns in the table. So, apparently memory-optimized things don’t like clustered indexes.
That’s fine. Not everyone likes clustered indexes. A lot of people who have paid me a lot of money to tell them they need clustered indexes didn’t like clustered indexes at first either.
So, like, I totally get it. You’re in the same camp, right? Just dumb people, right? And now I have a procedure called table variable test. And this table variable test takes a single integer called ID.
And inside of the store procedure, we declare a table variable as the post thing table type that I created up there.
And we also create just a simple local variable in here that I’m going to use to swallow results, right? Because this thing doesn’t need to return results.
I just need to show you the query plan stuff from it. And so, what I do is I insert into my memory-optimized table type variable here for any records that match the ID column.
And then I get a simple sum from the table type variable where the ID equals the ID that I pass in. And then I get a full sum from everything in there, right?
So, absolutely everything in there gets summed up, right? So, the first thing I’m going to do is I’m going to show you what happens under compat level 140.
And this is important because Microsoft changed some stuff about table variables in compat level 150 assuming that you have paid Microsoft money, Microsoft enough money to like you, which is by using Enterprise Edition.
So, when we run this and we look at what happens in the query, starting the query plans, we already know what happens in the query. Insert some data in the sum sum data.
Sum sum data. SQL Server knows exactly how many rows are going into the table variable here. Here.
2,000… 27,901 rows. Enter our table variable. Over here, right? Now, one table variable limitation that I end up talking to a lot of people about is that when you modify data in a table variable, you cannot use a parallel execution plan.
Granted, for this particular insert, a parallel execution plan would probably not bias anything. It’s already short and small and fast and we don’t really need to worry too much about it.
But I’ve run into a lot of situations where people would willy-nilly choose table variables or temp tables, just flip a coin, use whatever, I don’t know, you know, kind of two-face it.
And they would be very surprised when queries that they had that ran very fast to like, you know, say the select portion of an insert slowed down a lot when they started inserting data into a table variable.
They couldn’t quite figure out why. And usually it’s because the insert query that does a whole lot of work to get the rows together that you need to insert can’t go parallel anymore.
If you see this big, oh, actually my finger disappears a little, pretty early on, trying to point up that way. But if you look at the long word up there over my head that is in desperate need of some spaces, you will see a non-parallel plan reason that table variable transactions do not support parallel nested transactions.
Huh. Kind of missing something at the end there. So anyway, in-memory table variables have the exact same limitation in that regard as regular table variables.
Bummer. Okay. Well, do they have any good sides? Not that I can figure out. So under compat level 140, both of these queries, despite SQL Server having full knowledge of the 27,901 rows entering the table variable here, make no attempt at doing any better here.
When we ask it how many rows will qualify for the owner user ID that we just used up here, SQL Server still says one. When we ask it about the whole entire table, SQL Server still says one.
Thanks, SQL Server. Great, great use of, great use of resources there. Great use of all the smart people, all the mathematicians, all the PhD students, all the everyones who have ever worked on SQL Server.
One. One. That’s it. Under compat level 150. Wow, compat level 250? What version of SQL Server will that be?
If things follow along, let’s see, vNext would be 170, because that would be the next highest one. So that would be 180, 190, 200, then 1, 2, 3, 4, 5.
So that would be eight versions from now. So we would probably be somewhere around SQL Server 2045 or 2050 if we had compat level 250.
Unless Microsoft does a 1, 2, skip a few, because, for whatever reason, I don’t know, maybe counting by tens would get boring. Maybe, maybe, I don’t know, whoever is in charge of SQL Server will get bit by something and end up with 20 fingers, and they’ll start counting compat levels by 20.
I don’t know. I can’t possibly, I can’t possibly guess why that might happen. So, under compat level, starting with compat level 150, again, assuming that you have paid Microsoft’s friendship tax, so they pretend to like you, like that Patrick Dempsey movie where he, that girl spills red wine on her suede, mother’s suede outfit, and he pays to get it dry cleaned, so she pretends to be his girlfriend for the summer.
If you’ve paid Microsoft’s Can’t Buy Me Love tax, that’s the name of the movie, if you’ve paid Microsoft that tax, they will do something a little bit better for you, starting with compat level 150.
Let’s just make sure that ran. Where, it’s not that, SQL Server will allow a parallel execution plan to a table variable.
No, we still don’t support table variable transactions and not support parallel nested transactions. It’s not that. Does this get much better?
Wow. 27,901 of an estimated 167. Again, even though we are reusing this here and we are reusing this here.
Uh, 167. Thank you. Thank you.
Thank you. Thank you. Where things do get somewhat better is here. where now we get without a where clause we get full table cardinality.
So we say SQL Server says, oh, well, 27,901 rows went into that table. I guess 27,901 rows are going to come out.
The funny thing is that now table variables are sort of like parameters where you can get table variable sniffing.
So if we do this, right, and we look at what happens, well, now we reuse this plan, right, because this is totally what happened before in SQL Server with parameters sniffing.
But the first time we ran this, 27,901 rows came out. This time we only got nine rows. So SQL Server used the cardinality estimate from before for this plan. Well, the thing is that now it uses it again for this plan, right?
So now we get nine out of the 167 that it guessed before. And now we get nine of the 27,901 that it guessed before.
So even the small favor that Microsoft did for us starting with Compat Level 150 in SQL Server 2019, again, assuming that you’ve paid Microsoft the Can’t Buy Me Love tax, we’ll just call it the Patrick Dempsey tax for short.
Now, instead of just worrying about parameter sniffing, now you have to worry about table variable sniffing. So ain’t life grand?
Ain’t life just grand? So things haven’t really gotten the lot in life for table variables has not really improved all that drastically.
Now, that’s about all I have to say here. I need to go drop this database immediately because I’m starting to feel dirty. I don’t want this cold sore on my server anymore.
We’re going to take some Valtrex and rub some Abriva on this thing. Get it in there good. Fix it all up. Hopefully never to return again.
Is there a herpes vaccine? I don’t know. I’m not a doctor. I’m not qualified to say if there is or if there isn’t. Anyway, I hope you enjoyed yourselves.
I hope you learned something. I hope you will not fall for terrible blog posts promoting the use of memory-optimized table variables because gosh, they are useless and gosh, you don’t want to turn on this feature for your database that you can’t turn off again and I still don’t understand that.
I still don’t understand that feature, that decision, whoever’s decision that was. Man, I hope they work for Boeing now.
About it there. What did I say? I hope you enjoyed yourselves. I hope you learned something. I hope that you will like and subscribe and comment and buy training and hire me to do consulting and all that other good stuff.
And I’m going to go, I don’t know, this is, I think, this is the last one I’m going to record today because honestly, this one has gotten me down a little bit. So I need to go pep myself up.
I’m going to, I don’t know, I’m not sure what I’m going to do yet. Something that makes me feel better. 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.