A Faster tempdb

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.

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

Is DevOps central to your 2020 IT Digital transformation? How does your IT strategy underpin the business objectives? Read our latest blog to ensure you’re ready for success next year.

Compare the most common approaches to provisioning test data to database development teams and see how they stack up. Learn why traditional approaches like restore from backup are no longer suitable, and how successful organizations are implementing a combined data masking and data virtualization approach.

Developers and testers can be blocked from having a local, private database to work in because of disk space, refresh times, and data privacy. Download this free solution brief to learn how SQL Provision removes these blockers and enables production-like data to be delivered at speed. Download now.

Edge computing allows businesses to handle thousan...

Microsoft’s cloud-based, scalable Azure Machine ...

Paul Randal answers a reader question: I had a blo...

Two Dollars My Dear Friend™ Sean recently wrote ...

Milos Radivojevic takes us through a quick demonst...

Aaron Bertrand has put together a procedure which ...

Hasan Savran takes us through the OFFSET and LIMIT...

Anna Shrestinian, et al, explain how Azure Databri...

I get asked quite a bit about my thoughts on the i...

Creation or deletion files in your Azure Storage account may initiate data ingestion process and will support your event-driven data platform architecture.

The problemYou don’t know anything about Microso...

SQL Saturday comes to Bangladesh on Nov 16.

Join Redgate’s Arneh Eskandari and BMW’s Tony Maddonna for a free webinar on Nov 20: How to make your 2020 monitoring strategy a success. Learn how to prepare for successful estate growth, expected trends for the year, and the role monitoring plays in engabling growth and stability in your organization.

Earlier this year Microsoft announced the new Azur...

Overview Recently, I was facing an issue that a va...

Click here to learn more about author Gilad David ...

I run into the following Reddit’s question: My c...

One of Java’s big strengths, in my opinion, is t...

It is easy to attach details and documentation to ...

Andy Mallon needs to create groups of date ranges ...

Niels Berglund covers changes in SQL Server Machin...

Phil Factor describes how custom pre- and post-dep...

It is a non-null constraint, not a non-ish-null co...

The Azure-SSIS Team has done it again; they’ve a...

Have All Thirteen  

I received an email from a client who is having is...

This article is Part 1 in a series of articles sho...

Microsoft has released a preview of a new Microsof...

Microsoft says that since the general availability...

I was delivering training on SQL Server Performanc...

Erin Stellato shows how to analyze patterns in you...

The first key column as uniqueidentifier datatype....

Another post for me that is simple and hopefully s...

Originally posted on: http://blog.crip.ch/darrengo...

Back in 2016 I wrote the following blog post about...

Thanks for watching this week's Power BI news roun...

Marco Russo takes a look at how fast DISTINCTCOUNT...

Patrick shows a quick way to review your Power BI ...

Skilling up becomes more important (and necessary)...

The next time you need to look for a job, I’ve g...

PASS Summit 2019 starts up next Monday with pre-co...

Amit Banerjee has just made a bunch of DBAs happy:...

Niko Neugebauer is jazzed about SQL Server 2019 St...

Watch me comment on all of the features in this we...

K. Brian Kelley has the lowdown on skip-2.0: Probl...

Adrian Buckman has a new stored procedure: Sometim...

When we write string literal values, we need to su...

The other day during one of my old clients sent me...

I was fiddling around with some data, looking at m...

Sometimes you just want to get a quick row count f...

Ed Elliott has a pattern to try: We can select tho...

In Stack Overflow, I saw a poster with a query lik...

The precursor to the Internet carried its first lo...