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

Summary:

·         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 http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

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.