SQL Server tempdb best practices increase performance
Denny Cherry, Contributor
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, 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.
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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
This was first published in March 2008
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.
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.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation