Reducing allocation contention in SQL Server tempdb database

In SQL Server, tempdb database can become the bottleneck when there are allocation contention problems. To resolve the contention, Microsoft suggests one file per each CPU core. It seems this is a generally accepted rule. But, Paul Randal suggests it may not always work. If the database spills out large volume of data, multiple tempdb files may reduce the performance due to round-robin allocation, or random IO patterns.

The following is a quote from Microsoft about tempdb files. It follows by related hyperlinks of the topic.

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Recommendations to reduce allocation contention in SQL Server tempdb database
http://support.microsoft.com/kb/2154845

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

Misconceptions around TF 1118
http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

Working with tempdb in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc966545.aspx

SQL Server: Temporary Objects
http://pluralsight.com/training/courses/TableOfContents?courseName=sqlserver-temporary-objects&highlight=joe-sack_sqlserver-temporary-objects-m4-considerations*3#sqlserver-temporary-objects-m4-considerations

Advertisements

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s