I like to call tempdb the “workhorse” of SQL Server. I’ve heard some other people call it other terms that were not so flattering, but since I like to keep things positive, I’ll stick with workhorse.

SQL Server uses tempdb for many things. The obvious uses are temp tables and table variables, but tempdb is heavily utilized with snapshot isolation and Always On Availability Group read-only secondaries when you use those features. It is also used for sorting, some index rebuilds, after triggers… The list goes on. It could be the busiest database on the instance, so it deserves some special care. Not only is it busy, there is often metadata contention in tempdb. Objects are created and dropped so quickly, that the metadata pages become the bottleneck even if tempdb is on the best storage. It’s not so much the objects that cause the performance issue, it’s keeping track of them.

One piece of advice to improve performance of tempdb is to create multiple database files to avoid contention of the metadata pages that keep track of the objects. (See this article to learn more.) My favorite explanation about why this works is from Erin Stellato (@erinstellato). To paraphrase, imagine 50 people in a room with one door. They can’t all go out the door at the same time. If there were four doors instead, the room would empty much faster. With correctly configured, multiple tempdb files, the load on these “gatekeeper” pages can be spread out, the bottleneck is alleviated, and tempdb works faster.

Creating multiple files is so important that Microsoft added Automatic TEMPDB Configuration with SQL Server 2016. During both the wizard and command installations, tempdb will be configured during the install with recommended settings, or you can override them. This auto-configuration has been available only with SQL Server on Windows until now. Starting with 2019, this feature is also available with SQL Server on Linux.

Even after following the recommendations, some shops were still seeing contention. To solve this problem, the engineers at Microsoft came up with a new option for SQL Server 2019, Memory Optimized Tempdb Metadata. This means that the metadata tables in tempdb will be stored in In-Memory OLTP tables. Just the term “in-memory” sounds fast, but transactions against this type of table are fast because there is no lock or latch contention. It doesn’t matter how fast the objects are created and dropped, it won’t cause metadata contention. Note that this feature is not turned on by default; you have to enable it. Like many performance features, it is available in Enterprise edition.

Memory Optimized Tempdb Metadata is just one of the many features that makes SQL Server 2019 great.