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.
