SQL Centre of Excellence

Quick Notes on TempDB Sizing

by Bob Duffy 26. March 2011 05:24

Here’s some common questions I am asked on TempDB

Question 1) How Big should my TempDB Be ?

On an existing system this is quite a simple question to answer. by default the TempDB starts at 8MB and grows by 10%, so we just look at the size it has grown to and now we know how big it needs to be Winking smile

On a “suck it and see” approach the only consideration is to ensure that file growth is not left at the default values so we can limit “growth” events. Consider growing in 50MB, or 100MB+ increments, depending on the size of the databases.

Once we know how big it can grow to, it is really important to go back to the database size settings and correctly size the TempDb database. Bear in mind, when the instance is restarted the TempDB will be recreated back to the original size specified. This can lead to “Monday Blues” following a reboot on a Sunday. Warning signs for consultants are customer statements like “the system is slow every Monday morning”

What if you don’t have an existing workload, e.g. you are doing capacity planning for a new solution that has not been tested yet. Well, a few underpinning questions to ask:

1) How is that largest table or index or partition that will be rebuilt. If this is sorted in TempDB, then this is a minimum size plus a bit more.

2) Do we make use of any new features that use a lot of TempDB such as snapshot isolation or lots of large sorts http://msdn.microsoft.com/en-us/library/ms345368.aspx

3) if we have the database with sample data we can run DBCC CHECKDB WITH ESTIMATEONLY to estimate the amount of size needed for consistency checking

Question 2) How many files do I need for TempDB

This is always a contentious question, even amongst the SQL community. Microsoft do recommend one file per core http://msdn.microsoft.com/en-us/library/ms175527.aspx 

On greater than eight cores, I don’t usually go that far on recommendations  as this can be harder to manage and make sure that growth in all files is even. On most systems I see 2-4 files is more than enough to avoid concurrency issues.

Paul Randal also recommends avoiding that 1-1 ratio for most systems: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx 

However, there is the expression “No one ever got fired for having too many files for TempDB”. Too many files is likely to hurt less than not enough, but the point is it can hurt…

Question 2) Why is my TempDB so Huge!

Well there are three likely suspects which we need to eliminate from enquiries:

a) User objects: developers specifically creating temporary objects

b) Internal objects: hashing and sorting for monster queries or queries with bad plans and lack of indexes.

c) version store: for snapshot isolation and triggers maintaining multiple versions of rows in TempDB

The DMV sys.dm_db_file_space_usage will show which one is the culprit. here's a sample query that shows total 8k pages and how they are broken down

SELECT
user_object_perc = CONVERT(DECIMAL(6,3), u*100.0/(u+i+v+f)),
internal_object_perc = CONVERT(DECIMAL(6,3), i*100.0/(u+i+v+f)),
version_store_perc = CONVERT(DECIMAL(6,3), v*100.0/(u+i+v+f)),
free_space_perc = CONVERT(DECIMAL(6,3), f*100.0/(u+i+v+f)),
[total] = (u+i+v+f)
FROM (
SELECT
u = SUM(user_object_reserved_page_count)*8,
i = SUM(internal_object_reserved_page_count)*8,
v = SUM(version_store_reserved_page_count)*8,
f = SUM(unallocated_extent_page_count)*8
FROM
sys.dm_db_file_space_usage
) x;

blog comments powered by Disqus

Page List

Page List