Skip to content

Tempdb Configuration Best Practices in SQL Server

July 15, 2011

Transactions which happen on user tables created on tempdb do not require log so they are faster than those on other user tables.

Problem
In SQL Server 2005, TempDB has taken on some additional responsibilities.  As such, some of the best practice have changed and so has the necessity to follow these best practices on a more wide scale basis.  In many cases TempDB has been left to default configurations in many of our SQL Server 2000 installations.  Unfortunately, these configurations are not necessarily ideal in many environments.  With some of the shifts in responsibilities in SQL Server 2005 from the user defined databases to TempDB, what steps should be taken to ensure the SQL Server TempDB database is properly configured?

Solution

In an earlier tip, we discussed sizing (Properly Sizing the TempDB Database) the TempDB database properly.  The intention of that tip was to determine the general growth and usage of the database in order to determine the overall storage needs.  In this tip we want to take a broader look at how TempDB can be optimized to improve the overall SQL Server performance.

What is TempDB responsible for in SQL Server 2005?

  • Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
  • Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
  • Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
  • DBCC CHECKDB work tables.
  • Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

  • Do not change collation from the SQL Server instance collation.
  • Do not change the database owner from sa.
  • Do not drop the TempDB database.
  • Do not drop the guest user from the database.
  • Do not change the recovery model from SIMPLE.
  • Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
  • If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
  • Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Where can I find additional information related to TempDB best practices?

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

If number of CPU on a server < 8, tempdb data files should be equal to the number of CPU. Otherwise the number of data files should be 8 and add each 4 more until the memory pressure is released.

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 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.

Referrence from Denny Cherry’s blog

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

Storage top 10 Best Practices from BOL

http://www.db-staff.com/index.php/microsoft-sql-server/65-tempdb

http://technet.microsoft.com/en-us/library/cc966545.aspx

Advertisements

From → DBA

Leave a Comment

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: