Dec
24
2012

Tips to optimize tempdb performance

In order to optimize tempdb performance pay attention to physical disk configuration, file configuration, as well as some settings within the database.

Physical disk configuration

tempdb should reside on its own dedicated physical disks. This allows it to split I/O transactions from the remainder of volumes on the SQL Server.

To move tempdb to a new disk drive, use ALTER DATABASE. It is the key T-SQL command to perform this operation. Microsoft offers a good example in SQL Server 2005 Books Online. The article name is ALTER DATABASE (Transact-SQL) and the specific section is ‘G. Moving tempdb to a new location.’

The tempdb is a very high-write database. So, a RAID 5 array isn’t the proper place for it. You should put the tempdb on either a RAID 0 or RAID 10 array as they’re optimized for high-write applications. If you can afford additional RAID 0 or RAID 10 arrays for each physical database file for the tempdb, you’ll get increased performance.

Database files

You should have one physical file per CPU core in the server or ½ or ¼ of no of CPU. So, if you have a dual-chip, quad-core server, you should have 8 (best) or 4(mid) or 2(min) physical database files for the tempdb database. When adding more database files, it’s important to configure the files at the same initial size and with the same growth settings. That way, SQL Server will write the data across the files as evenly as possible.

Database file size

The size of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.

Determining the appropriate size for tempdb in a production environment depends on many factors including the existing workload and the SQL Server features that are used. Microsoft recommends that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  1. Set autogrow on for tempdb (in a test environment!).
  2. Execute individual queries or workload trace files and monitor tempdb space use.
  3. Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
  4. Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

Minimum size recommendations for tempdb are as follows:

Envir. Size DB     Size (MB)     Log Size (MB)

Small      1024      256

Medium      5120      1024

Large      10024      2048

Database settings

You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. You can also set the auto create statistics option to false.

Disclaimer: Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance.

To achieve optimal tempdb performance, follow the guidelines and recommendations provided in Optimizing tempdb Performance.

How to monitor tempdb usage?

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations.

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views.

These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb.

Links:

This article is taken from post on forum:

http://stackoverflow.com/questions/360088/sql-server-tempdb-optimization-tips-for-a-new-server

 

Recommended Read:

Top 10 Storage Best Practices: http://technet.microsoft.com/en-us/library/cc966534.aspx

 

Other Useful Resources:

http://ask.sqlservercentral.com/questions/24029/best-raid-for-tempdb.html

http://searchsqlserver.techtarget.com/tip/SQL-Server-tempdb-best-practices-increase-performance

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 264 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

January 2021
M T W T F S S
« Nov    
 123
45678910
11121314151617
18192021222324
25262728293031

View Post by Categories

%d bloggers like this: