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?

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 some code to calculate average I/O latencies and send me the results. I […]

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. If you know someone who would benefit from […]

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 […]

How does DBCC CHECKDB WITH ESTIMATEONLY work?

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

There’s been a recent flurry of confusion and misconceptions about trace flag 1118 in SQL Server 2008. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load […]

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 […]