Update!
You can now configure size and autogrowth settings for tempdb, but the article doesn’t mention anything about in-memory settings.
Twelve Angry Files
This post is admittedly short, but since we’re short of First Party Solutions™ to gripe to Microsoft with… I need to save this stuff somewhere.
- You get 12 tempdb files, even if your instance has 16, 24, 32, 40, 64, or 80 cores
- There’s no way to turn on in-memory tempdb
If your workload is tempdb-heavy, and you have a special configuration for it on-prem, you might have a tough time moving it to Managed Instance. Also consider that disk performance (even though tempdb is “local”) is garbage, and you can’t do anything about it.
I think the lack of in-memory tempdb is odd too, since many features are cloud-first. One may wonder aloud why a performance feature that’s a couple years old now still isn’t available in flagship business critical cloud offerings.
It was only recently (September 2021) that scalar UDF inlining became available up in Azure SQL DB and Managed Instance.
Who’s really on the cutting edge? All those stodgy holdouts hanging about in their earthly garb.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% 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 performance problems quickly.
Related Posts
- SQL Server Community Tools: Changing The Output Of sp_WhoIsActive In Different Ways
- Even If SQL Server Table Variables Were Always In Memory, It Wouldn’t Make Them Better Than Temp Tables
- How #Temporary Tables Can Cause Plan Cache Pollution In SQL Server
- LOB Data, Recompile, And Too Much tempdb Usage In SQL Server
Erik, correct me if I’m wrong, but if you’re referring to tempdb with memory optimized metadata, then that feature doesn’t work well with memory optimized user databases.
Coincidentally, all user databases in a managed instance are created with in-memory enabled by default.
So, I’m guessing that’s probably the reason for this weird limitation in managed instance.
Are you talking about the second point here, about transactions? Or something else?
If it’s just the transactions, I’d consider that pretty narrow.
Yes, that’s what I’ve been referring to.
This means that if someone migrates a user database into a Managed Instance, which utilizes both in-memory tables as well as access to tempdb, then having tempdb with memory-optimized metadata would break it.
I mean, obviously, the best thing would be to have it configurable, just like it is in on-prem.
But who knows, maybe there’s some kind of technical limitation when it comes to Managed Instances that doesn’t make that possible.
This is the breaking example in the docs:
BEGIN TRAN;SELECT *
FROM tempdb.sys.tables; -----> Creates a user in-memory OLTP transaction in tempdb
INSERT INTO..
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
COMMIT TRAN;
That doesn’t seem like a very likely pattern, but perhaps you’ve experienced something more common causing issues?
I didn’t get to test it personally.
But couldn’t something like this cause the same problem?:
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL DROP TABLE #temp;
That’s a pretty common pattern and theoretically, it should be accessing tempdb metadata as well.
We ran out of reply space, but I’ve tested several variations of your assertion and none of them result in an error. The only thing that generates the error in is accessing the DMVs there directly, like the docs lay out.
Let me know if you’d like me to delete your comments.
Cool. Thanks for checking, Erik.
No need to delete any comments. I think our correspondence here could be useful for other visitors.