Taming the Tempdb Tempest - WI SQL Server Virtual User Group, 22 Apr 2011
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 https://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.