SQL Server 2019’s In Memory tempdb Improves Performance

And I’m Pins And I’m Needles

---

Thanks for watching!

Video Summary

In this video, I explore an interesting enhancement in SQL Server related to TempDB metadata being stored in memory to reduce contention issues. I demonstrate how enabling this feature requires turning on advanced options and setting a specific configuration setting to one, which necessitates a server restart for the changes to take effect. Despite these improvements, I also show that not all TempDB metadata is memory-optimized, leading to continued locking and contention problems even with the new settings in place. Through practical examples using SQL Query Stress, I highlight the ongoing challenges and potential areas for further optimization within TempDB management.

Full Transcript

Hello. Is this thing on? Better be on. I paid for this thing to be on. Erik Darling here with Erik Darling Data. I’m hoping that someday I will get a free personalized Swiss Army knife with my company logo on it, but I am not that successful yet. I am still only getting pens. Erik Darling here with my company. Anyway, big day. It was a big day yesterday for SQL Server stuff, and I know it was a big day because my wife was annoyed with how long I spent in my office poking around at CTP 2.4.

Erik Darling here with my company. So what I wanted to show you was kind of a neat improvement to TemptDB, where some TemptDB metadata is able to be stored in memory in order to reduce contention. Erik Darling, if you remember with the whole in-memory thing, it’s a latch-free or lock-free or something-free. Erik Darling, all I know is that it’s never free. It’s going to cost you money either in licensing or in memory or something else. I don’t know. Time, probably. Your sanity.

But some TemptDB metadata is now able to be stored in memory. Now, there’s a sys.configuration setting for this. So you’ll have to go in and you’ll have to make sure that you turn on advanced options, and you’ll also have to make sure that you set this setting to one. So it’s now TemptDB metadata memory optimized with fun, fun stuff. Now, when you turn this on, it does require a restart. You do have to reboot SQL Server to get this to take effect. And I didn’t see a way in the installer to make that turn on by default, like when you first install SQL Server. Maybe you don’t have to restart it after you hit production or something, but maybe that’s coming.

You know, the installer is still the 2017 installer, so that’ll probably, hopefully change by the time this thing hits release candidate or RTM or one of those crazy acronyms. Anyway, after you turn that on, some TemptDB system tables are now stored in memory. So if we look at this helpful DMV up here, sys.dmdbxtp object stats, we will now see a whole bunch of TemptDB stuff in memory. So sys.dmdxt.com. So sys.dmdxt.com. So sys.dmdxt.com. So sys.dmdxt.com is called obj.dxt.com. You can read that on your own. I’m not going to read all that for you.

You’re at least semi-literate people if you can type YouTube in and SQL Server. If you just ended up here by accident and you were looking for porn and got turned around. But yeah, so now some of this stuff is in memory, which is pretty cool. Now, what I wanted to show you next is that even though some of this stuff is still in memory, not all of it, this doesn’t solve fully TemptDB contention problems in memory.

So what I’m going to do is I have the lovely open source SQL query stress here. And what I’m going to do is just create a bunch of tables in TemptDB. I’m not going to put any data in them. There’s no select. There’s no insert. There’s no update, delete. There’s nothing going on.

It’s just me creating a lot of TemptDB objects. Now, I’m going to create TemptDB objects across 200 threads. So I’m going to have 200 workers just going in and creating TemptDBs and then leaving.

Or creating TemptTables, not creating TemptDBs, creating TemptTables and then leaving. And I’m going to do that just for effect. I’m going to do that 200 times just to make sure that I can catch lots of stuff happening. Now, the first thing I want to show you is I’m going to kick this off, and hopefully this won’t fry my computer, is if we run spwhoisactive with getlocks equals 1, we’ll run this for a little bit and we’ll start to see some blocking.

Now, the blocking, oops, come on, zoom it, work with me here. The blocking is all happening just on these table creates. We can see blocking session ID is populated.

If we go over here, we can see session ID 273 is just creating a TemptTable. Go away. It’s just creating a TemptTable the way all the other ones are. And if we look carefully at spwhoisactive’s output, if we go over to the locks column, normally this would be populated with XML that would tell us exactly what was locked, what was the page level, key, object, what it was.

But this is null for us. It means we’re not showing any locks here. But we’re still being blocked. We still have all these sessions that are getting blocked when we try to run this.

Is that finished? It’s finished. I talked for too long and that’s what happened. That’s what I get for talking. And then sometimes SQL query stress doesn’t like coming back to life when I beat it up terribly like that. Let’s see what this is doing.

Oh, you don’t need to see that. All right. What are you up to? Did you crash again? You probably crashed again. All right. The perils of open source, ladies and gentlemen. All right.

So let’s go into task mugger and let’s end task there. And I’m going to pause this video while I set that back up. All right.

We’re back now. So I have SQL query stress back after a magnificent crash. And what I want to show you now is, so spwhoisactive does not show us any locks. But if we go into tempDB and we run, not that query, if we run this query, this will show us all sorts of funny locks.

Now, it’s not going to return anything right now because there’s nothing going on right now. But if I fire up SQL query stress and I start running and I hit F5, we’ll start to see some locks on some tables in tempDB. And we’ll wait until we hit some fun numbers in there.

Give it one more run. All right. So that looks pretty good to me. So if we zoom in now and we look at exactly what is getting locked and what kind of locks, we’ll ignore all the childish acronyms that we could have. We could spell out looking at the locks that go on there.

And we’ll look at what actually got locked. So these two system views, sysalloc units and sysrowsets, are not currently memory optimized. So we are still taking locks against them and we still can hit contention when we’re hitting these system views to get stuff going.

Now, if I run, I have no idea if SQL query stress has crashed, if it’s still running, if it’s still doing anything. It might not be. Yeah, it’s not responding again.

It doesn’t matter, though, because I showed you everything that I want to show you. And it’s just kind of stuck there again. Darn you, SQL query stress. This is why I usually use O stress, but I wanted you to be able to see the create table thing in there. Anyway, let’s kill that.

Because we don’t need to see that anymore. And hopefully that’ll work. Anyway, so cool step forward for Temp TV. I think it still has a little bit of work to do.

I’m not sure why those two system views weren’t included in the memory optimizationing of Temp TV. Maybe they will be if I gripe about it enough. Or if the right people are watching this.

I don’t know who those people would be, though. Maybe they’re on Twitter. I’ll have to go find out. Anyway, thank you for watching. I hope you learned something.

I hope you thought this was kind of cool. And I will see you, I guess, next time. Probably. If there isn’t next time. You never know. You never know. What if I get abducted by aliens?

I think that’s the best case for me. Some outer space women. Anyway, see you next time.

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.