Tip

SQL Server tempdb best practices increase performance

One way to greatly improve your SQL Server's performance is to properly optimize the tempdb database. To punch up its efficiency, try looking at the physical disk configuration, file configuration,

    Requires Free Membership to View

as well as some settings within the database.

Physical files countdown

With standard user databases, it's recommended that you set the number of physical files for each database at .25 to one physical file per CPU core. With the tempdb database, you should have one physical file per CPU core in the server. So, if you have a dual-chip, dual-core server, you should have four physical database files for the tempdb database.

More ways to increase SQL Server performance

Learn to optimize performance with this guide about SQL Server hardware

Read about maximizing SQL Server database performance by streamlining data

Find out three easy ways to improve SQL Server performance

By having the larger number of files, you can increase the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O that SQL Server can push down to the disk level, the faster the database will run. With standard databases, SQL Server can cache a large amount of the data that it needs into memory. Because of the high-write nature of the tempdb, the data needs to be written to the disk before it can be cached back up into memory.

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. If the database files end up being different sizes, it will attempt to fill the files with the most free space first in order to balance the amount of free space within all of the files.

Storage array configuration

Most databases are high read and low write and, usually, database files are placed on RAID 5 arrays, which give good disk performance at a reasonable cost. However, because the tempdb is a very high-write database, a RAID 5 array isn't the proper place for it. You should put the tempdb on either a RAID 1 or RAID 10 array as they're optimized for high-write applications. If you can afford additional RAID 1 or RAID 10 arrays for each physical database file for the tempdb, you'll get increased performance.

For best performance, put the tempdb on its own RAID array or arrays that are not shared with any other parts of the database or system. This includes having other LUNs on the same SAN drives as other LUNs, and it's especially important because if you put other LUNs on the same spindles as the tempdb LUN, it places additional load on those disks. That makes it extremely difficult to troubleshoot from the server side because the server doesn't know about the extra I/O on the disks.

Modifying database settings

You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, you stop SQL Server from having to check to see if it needs to update the statistics. However, you'll need to manually update statistics if you occasionally use large temporary objects.

You can also set the auto create statistics option to false. That, too, will increase tempdb performance. By disabling the setting, you stop SQL Server from having to produce the statistics when the object is first created.

Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance. You should test the settings in a non-production environment before they are deployed to the production environment. After deployment, monitor these changes closely to ensure the system is running at peak performance.

Indexing temporary database objects

If you have large temp tables -- thousands of rows or more – you'll find that indexing your temporary tables greatly increases your tempdb performance, just as indexing permanent tables will increase the performance of queries on these tables. To index temp tables and global temp tables, use the normal CREATE INDEX command after the table has been created.

 

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This creates a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.

With careful planning and testing, you can get some excellent system performance improvements by making some fairly easy changes to the tempdb. Take care, however. Even simple changes to the tempdb can have an adverse impact on system performance.

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

 Check out his blog: SQL Server with Mr. Denny.

This was first published in March 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.