I spent sometime to understand more about tempdb today. posting some key points for our quick reference.
some key books/links on this topic are given in reference section for further read.
Some properties of Tempdb Properties
1. Tempdb is recreated everytime SQL Server starts.
2. Simple recovery mode. No Full/Log backup possible.
3. Tempdb has one filegroup named PRIMARY. We can't add more filegroups.
Usage:
1. User Objects:
Local and Global temporary tables, table variables
2. Internal Objects:
Spools, Sorts, Hash Joins, Cursors, DBCC Checkdb etc
3. Version Store
Online indexes, Triggers, Snapshot Isolation etc
Troubleshootin common issues:
1. Multiple tempdb data files having same size. SQL Server 2008 has improved algorithm to manage SGAM contention.
2. Increase temp object reuse
3. Trace flag 1118 - only uniform extents.
Monitoring Tempdb performance:
IO performance:
Perfmon: Avg. Disk sec Read/Write/Transfer
DMV:
1. sys.dm_io_virtual_file_stats,
2. sys.dm_db_file_space_usage
3. sys.dm_db_task_space_usage
4. sys.dm_db_session_space_usage
Too big tempdb log size:
Can't do much in Simple recovery mode. Try CHECKPOINT.
Configuration best practices:
1. Tempdb File placement (Seperate data, logs and tempdb)
2. Recomendation is RAID10 disk array
3. Set Size and autogrowth based on workload.
4. use Instant file initialization. tiny security risk involved.
Source: Wrox - Professional SQL Server 2008 internals and troubleshooting (Ch 7: Knowing Tempdb)
Further Reference:
How to optimize tempdb performance from SQL Server MVP Deep Dives
http://msdn.microsoft.com/en-us/library/ms190768.aspx
http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/
http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx