TempDB Archives - Brent Ozar Unlimited®

Performance Tuning

The Version Store Won’t Clear If ANY Database Has Open Transactions.

October 28, 2025

Short story: what the title says.

This is especially problematic for folks who merge multiple databases onto the same server. All it takes is one badly-behaving application to leave its transactions open, and suddenly it causes the rest of the databases to run TempDB out of space. That app's transaction might not even be changing anything, and it might have never caused problems for that application before - but once it shares a TempDB with other apps, it causes cascading problems.

Performance Tuning

TempDB Filling Up? Try Resource Governor.

October 21, 2025

TempDB is one of the banes of my existence.

Anybody, anybody who can query your server can run a denial-of-service attack in a matter of seconds just by filling it up with a simple query:

Performance Tuning

[Video] How to Troubleshoot Someone Else’s Temp Table Contents

November 12, 2020

Let's say you need to troubleshoot someone's query performance, and they're using temp tables. You want to peek in at their temp table contents from another session while their query is running. SQL Server makes that a little bit challenging. Nic Neufeld showed how to use DBCC PAGE to do this, but today I'm going to cover another way: reading their statistics histogram.

Performance Tuning

Paul White Explains Temp Table Caching 3 Ways

November 11, 2020

Paul White (Blog, @SQL_Kiwi) is a legendary SQL Server blogger who's written a ton of good material. You should read all of it. You won't, and I can say that with authority because...I don't read it all either. It's too much.

But when your job eventually requires you to understand temp tables way better than you do today, read these 3 posts in this order - which is probably different than what Paul would recommend, but I think they flow better this way:

Performance Tuning

How to Move TempDB to Another Drive & Folder

November 24, 2017

Every time I have to do this, I Google for a script. I might as well write my own and put it here so at least I find myself in the Google results:

[crayon-6aab525d66d0c451551745/]

Performance Tuning

Using Trace Flag 2453 to Improve Table Variable Performance

February 7, 2017

I recently saw a server with trace flag 2453 configured. I hadn't come across this trace flag before, so I did a little research. Microsoft says it allows "a table variable to trigger recompile when enough number of rows are changed". This can lead to a more efficient execution plan. Trace flag 2453 is available in SP2 or greater for SQL Server 2012, CU3 or greater for SQL Server 2014 and RTM or greater for SQL Server 2016.

Performance Tuning

High Compiles and Multi-Statement Table Valued Functions

January 26, 2017

Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…

Performance Tuning

Analyzing Temporary Stored Procedures with sp_BlitzCache

January 24, 2017

All the cool kids Know that when you're trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc. We frequently point people to Erland Sommarskog's "Slow in the Application, Fast in SSMS", because it's an excellent…

Performance Tuning

Do DDL Triggers in TempDB Survive Restarts?

January 11, 2017

Of Course Not
Nothing survives in tempdb. But this kind of stinks for those of us who don't want certain things created in there.

In my case, when I'm working on Blitz scripts, I have a terrible habit of accidentally re-creating the procs in user databases. Usually whichever I'm building test data in, like Stack Overflow. This can lead to several minutes of temporary insanity, where none of my changes are working.

Performance Tuning

Indexing Temp Tables

December 15, 2016

People often don't give this thought
Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it's a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join 10,000 rows to another temp table with another ignored nonclustered index on it.

Performance Tuning

Is your SAN’s cache killing tempdb?

June 3, 2016

Let's start with definitions
Many SANs have caching built in. What kind of cache is important, because if you're dealing with non-SSD storage underneath, you could be waiting for a really long time for it to respond.

Let's start with some definitions of the most popular caching mechanisms available for SANs. I'm not going to say 'only', because some vendor out there might have some proprietary stuff going on that I haven't heard of.

Performance Tuning

Creating Tables and Stored Procedures in TempDB – Permanently

May 4, 2016

No, not #tables - actual tables. Here's how: [crayon-6aab525d6bb5c049557111/] The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted. Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data…

Performance Tuning

When Shrinking Tempdb Just Won’t Shrink

February 3, 2016

I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that database is tempdb.

It used to be that we were warned against shrinking tempdb because it could cause corruption, so your only recourse was to restart the SQL Server service. Paul Randal let us know that this is no longer a problem.

Performance Tuning

Cheat Sheet: How to Configure TempDB for Microsoft SQL Server

January 14, 2016

The short version: configure one volume/drive for TempDB. Divide the total space by 9, and that's your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance.

The long version is a little more complicated.

Performance Tuning

Tracking tempdb growth using Extended Events

December 2, 2015

Everyone knows tempdb is a wierd [sic] place
Brent refers to it as a public toilet. I agree with that sentiment. It will let anyone in to do whatever they want.

Recently I was trying to track down what was causing tempdb's log to grow. I was thinking about using a Trace to do it, but then I remembered that it's at least 2015, and I should be using Extended Events. Don't worry, there won't be any PowerShell. You can keep reading.

Performance Tuning

The case for Query Store in tempdb

November 3, 2015

Query Store is so cool
Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI.

You can read all about what it does and what you can do with it around the internet. You can be suitably impressed and enchanted by the promise of data that's persisted between restarts, being able to quickly and easily address plan regression issues, and so forth.

Performance Tuning

Trace Flags 1117, 1118, and Tempdb Configuration

June 19, 2014

SQL Server has a few options for configuring tempdb. It's great that Microsoft gives some advice for how to battle some kinds of contention that can impact tempdb in KB 2154845, but things can still be a little confusing.
Trace Flag 1118 - Full Extents Only
KB 2154845 advises that Trace Flag 1118 can help in some situations. That trace flag tells SQL Server that it should avoid "mixed extents" and use "full extents". (Learn more about extents here.)

Performance Tuning

Temp Tables vs Table Variables vs Memory Optimized Table Variables [Video]

June 7, 2014

Should you use temp tables or table variables in your code? Join Microsoft Certified Master Kendra Little to learn the pros and cons of each structure, and take a sneak peek at new Memory Optimized Table Variables in SQL Server 2014.

This video is a recording of a live webcast, so please excuse any audio and video imperfections. Want the scripts from the webcast? Scroll on down.

Performance Tuning

How to Tell if You Need More Tempdb Files

May 22, 2014

You may have read that you need to have more than one data file in SQL Server's tempdb. This can happen even if you're using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. For certain workloads, adding more tempdb files speeds up SQL Server.

Performance Tuning

Memory and IO in Tempdb: SQL 2014 and SQL 2012 SP1 CU10 Improvement

April 29, 2014

Update on 5/25/2014: At least parts of this improvement have been backported to SQL Server 2012 SP1 CU 10. Read the KB here. After applying CU10 to SQL Server 2012 SP1, the test query shown in this post performed as it did against SQL Server 2014. I was excited to read Bob Dorr's recent post…