Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

Tempdb quick notes from Professional SQL Server 2008 internals and troubleshooting

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





Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.