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!
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.
- 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