Befriending Dragons

Intersectional Coaching Journeys: Reframe, Unblock, Move Forward

Taming the Tempdb Tempest – WI SQL Server Virtual User Group, 22 Apr 2011

Leave a comment

Thanks to the Wisconsin Virtual SQL Server User Group for letting me talk about tempdb today! The slides and demo queries are attached. Once the recording is available I will update this blog with a link to it.

Taming the Tempdb Tempest


·         Multiple data files of the same size, one log file

·         Enough data files to avoid contention, not so many to cause problems.

·         Presize for peak periods of next X months, re-evaluate

·         Set autogrow to be rare but “big enough”

·         Instant File Initialization on (small security risk)

·         Fast IO subsystem

·         Change size/settings if you add new features that use tempdb

·         Monitor for approaching full, change in activity/size

·         Performance tune user databases and applications

·         Limit use of versioning or temp objects


The demo queries are:

·         sys.dm_db_file_space_usage.sql: How space is used inside tempdb

·         sys.dm_db_file_space_usage_companion1.sql: Show how different activities cause space to be used in tempdb

·         Autogrow.sql: Find autogrow settings for all dbs on an instance

·         TempdbContention.sql: Find contention on tempdb metadata


Also see my previous blog post with the same basic data in a different format: Compilation of SQL Server TempDB IO Best Practices

I also delivered the talk to the Boise SQL Server User Group on 13 Sep 2011 and the updated queries and slide deck are attached below.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s