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 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.

TempdbBoiseSQLSep2011.zip

Published by Cindy Gross | She-Her | Befriending Dragons

Coach

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: