Azure Managed Instance Doesn’t Manage tempdb Well

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.

  1. You get 12 tempdb files, even if your instance has 16, 24, 32, 40, 64, or 80 cores
  2. There’s no way to turn on in-memory tempdb
death of auto tune

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.



8 thoughts on “Azure Managed Instance Doesn’t Manage tempdb Well

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

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

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

          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?

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

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

Comments are closed.