SQLskills SQL101: Temporary table misuse

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 […]

Investigating the proportional fill algorithm

This is something that came up recently on the Microsoft Certified Master DL, and is something I discuss in our IEPTO1 class because of the performance implications of it, so I thought it would make an interesting post. Allocation Algorithms The SQL Server Storage Engine (SE) uses two algorithms when allocating extents from files in […]

Survey: tempdb file configuration (code to run)

I’m running this survey to help the SQL Server team at Microsoft, who would like to get a broad view of current tempdb configurations. I’ll editorialize the results as well in a week or two. Feel free to run the code below any way you want, and also add a single preceding column to the result […]

Correctly adding data files to tempdb

It’s well known that one of the common performance issues that can affect tempdb is allocation bitmap contention. I discuss this, and ways to alleviate it, in these posts: The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention Misconceptions around TF 1118 Tempdb configuration survey results and advice The current best advice around adding […]

Shrinking tempdb no longer prohibited

For the longest time the guidance around tempdb is that if you shrink it on a live system then it could cause tempdb corruption. A few months ago I was discussing this with my good friend Bob Ward from Product Support and neither of us could remember the last time we’d seen a case of […]

Are I/O latencies killing your performance?

(Check out our Pluralsight online training course: SQL Server: Improving Storage Subsystem Performance.) In this post I explain some methods for investigating and reducing high tempdb and transaction log I/O latencies that can severely hamper the performance of your workload. Back at the end of August I kicked off a survey asking you to run […]

The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in […]

Understanding data vs log usage for spills in tempdb

Earlier this morning I noticed a discussion on the SQL MCM distribution list (that all the original MCM instructors are part of) that was trying to make sense of a huge disparity between tempdb data file usage and log file usage. I explained the answer and thought I'd share it with you all too. The […]


This is a question that came up today on Twitter, and is actually something I’ve been meaning to blog about. One of the biggest space hogs in tempdb can be DBCC CHECKDB. It generates all kinds of information about what it’s seeing in the database (called facts) and stores them in a giant worktable. A […]

Tempdb configuration survey results and advice

A few weeks ago I kicked off a survey about tempdb configuration – see here for the survey. I received results for more than 600 systems! Here they are:     These are very interesting results, for several reasons: It shows the relative distribution of core-count for SQL Servers, with a pronounced shift to 8+ […]

Survey: how is your tempdb configured?

In this week's survey, I want to know how you've got tempdb configured compared to the number of processor cores SQL Server thinks it has. I'll correlate, analyze, and present the results like the log file survey I did last year where I got results for 17000 databases. The code I'd like you to run […]

Adventures in query tuning: unexpected key lookups

I'm starting a new blog category to talk about some of weird and confusing stuff I see while query tuning. First up is the case of the unexpected Key Lookup (Clustered) in a query that looks like it should be covered. This is a follow on from the post Missing index DMVs bug that could […]

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This is a myth I hear over and over and […]

What does checkpoint do for tempdb?

Last week I posted in-depth about how checkpoints work and what exactly goes on (see How do checkpoints work and what gets logged). About a year ago I posted about why the buffer pool on a busy system may seem to have an inordinate amount of dirty tempdb pages in it, and now I want […]

Misconceptions around TF 1118

[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]

TechNet Magazine: April 2009 SQL Q&A column

The April edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]

Comprehensive tempdb blog post series

Over the last few weeks Sunil Agarwal (from the SQL Storage Engine team) has posted a great series of blog articles about tempdb and the version store, over on my old stomping ground – the Storage Engine blog. The articles are well worth reading – the links are: Managing tempdb part 1 and part 2 Version […]

Why does the buffer pool contain so many dirty tempdb pages?

(Quickie post #2 while it's Kimberly's turn to lecture this morning…) Greg asked a question regarding the script I posted to examine buffer pool contents (paraphrased) – why does the buffer pool seem to contain such a high proportion of dirty tempdb pages on busy production systems? The answer is to do with the recoverability of […]