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 [...]
Paul S. Randal
In Recovery...
Understanding data vs log usage for spills in tempdb
Posted on: May 11, 2012 2:08 am
How does DBCC CHECKDB WITH ESTIMATEONLY work?
Posted on: September 23, 2011 10:01 am
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
Posted on: March 9, 2011 10:42 am
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?
Posted on: February 22, 2011 3:03 pm
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
Posted on: May 1, 2010 6:26 pm
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
Posted on: April 12, 2010 8:28 am
(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?
Posted on: October 7, 2009 11:49 am
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
Posted on: April 23, 2009 9:19 pm
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
Posted on: March 11, 2009 12:57 pm
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
Posted on: January 16, 2009 9:24 am
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?
Posted on: September 2, 2008 9:48 am
(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 [...]
