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

I am a Generative Coach, a Race+Gender Equity Adaptive Leader, an ex-techie, & the founder of Befriending Dragons. I lead & coach with empathy & an intersectional social equity perspective so you can unleash the change & impact you want to have in the world. I am a chaos dragon, injecting just enough disruption around me to open new perspectives and new harmonies. I seek wise justice.

Leave a Reply

%d bloggers like this: