Chapter 43. How to optimize tempdb performance
Although most DBAs know about the tempdb database, many seem to think of it as a black box that takes care of itself with no involvement required from the DBA. Although this may be true on smaller, less active SQL Server instances, tempdb can significantly affect SQL Server’s performance. DBAs can act to ensure that tempdb performance is optimized and to optimize the overall performance of SQL Server.
What is tempdb used for?
The tempdb database is one of SQL Server’s included system databases and is used as a shared temporary workspace for many different kinds of activities, such as the following:
- Storing user objects, such as temporary local and global tables and indexes, temporary stored procedures, table variables, and the cursor.
- Storing temporary work tables used for hash joins, aggregations, cursors, and spool operations, and temporarily storing large objects; storing intermediate sort results from many different internal operations, such as creating or rebuilding indexes, in addition to some GROUP BY, ORDER BY, and UNION queries.
- Storing objects used when using AFTER triggers and INSTEAD OF triggers.
- Storing large XML objects.
- Storing the SQL Server version store (SQL Server 2005/2008), which includes the common version store and the online-index-build version store (Enterprise Edition).
- Storing intermediate results and sorting activity used during DBCC CHECKDB operations.
- Storing temporary objects for Service Broker.
If your SQL Server instance is not busy, and it doesn’t employ many of the activities described previously, then tempdb performance may not be a problem for you. On the other hand, if any of your SQL Server instances are busy, and are heavily involved in many, if not most, of the previously described activities, then you may find that tempdb can become a significant bottleneck for your entire SQL Server instance.
Tempdb internals
Unlike other SQL Server databases, the tempdb database is dropped and re-created every time the SQL Server service is stopped and restarted. Here’s what happens. When the SQL Server service is started, by default, SQL Server makes a copy of the model database to create a new 8 MB tempdb database, inheriting customizations made to the model database. In addition, a transaction log file of 1 MB is created. For both the MDF and the LDF files, autogrowth is set to grow by 10 percent with unrestricted growth. Each SQL Server instance may have only one tempdb database.
In addition, tempdb exhibits many behaviors that don’t occur with other SQL Server databases. For example, tempdb is configured to run using the simple recovery model, and this setting cannot be changed. In addition, many database options, such as Online, Read Write, Auto Close, Auto Shrink, and others are preset and cannot be modified. The tempdb database has many other restrictions including the following: it can’t be dropped; it can’t be captured in a database snapshot; it can’t participate in mirroring; and it can’t allow DBCC CHECKDB to be run on it. And as you would expect, neither the tempdb database nor its transaction log file can be backed up. This makes sense, as tempdb is designed for temporary objects only, and is re-created each time SQL Server restarts.
After tempdb has been created, DBAs can create objects in it just as in other databases. As user-created or internal objects are added to tempdb, it will automatically grow as necessary to whatever size is required to hold the objects. On servers with heavy tempdb activity, tempdb can grow considerably.
As activity transpires in tempdb, transaction logging occurs, but somewhat differently than with other SQL Server databases. Operations performed within tempdb are minimally logged, which means that only enough information is logged so that temporary objects can be rolled back, if necessary. Minimal logging helps to reduce the overhead put on the SQL Server instance. Because the database is set to the simple recovery mode, the transaction log is truncated constantly.
How DBAs can help optimize tempdb
As I mentioned previously, if your SQL Server instance doesn’t use tempdb much, then tempdb performance may not be an issue for you. On the other hand, you should keep one important thing in mind about tempdb: there is only one tempdb database per SQL Server instance, and it can become a major bottleneck that can affect the entire performance of your SQL Server. Keep in mind that even if most of your applications and databases behave well, a single misbehaving application and database can affect the performance of all the other databases running on the same server.
When building a new SQL Server instance, it is often difficult to determine how busy the tempdb database will be in production. Because of this, you may want to consider implementing many of the following suggestions when you build a new instance, as in many cases it is much easier to implement these recommendations when the server is first built, rather than trying to implement them after a problem has developed. Consider this as an ounce of prevention to avoid potential and unforeseen problems in the future.
In the following section, we take a look at many different practices you can employ to help optimize the performance of tempdb. Because each SQL Server instance is different, I am not suggesting that you employ every one of these recommendations on each of your SQL Servers. Instead, you must evaluate the kinds of problems you are having (or may have, if you are building a new SQL Server) and consider the available options (for example, you may not have the option to reconfigure your storage array or purchase new hardware). You implement only those recommendations that best meet your needs.
Minimizing the use of tempdb
As we discussed earlier, a lot of activity can occur in tempdb. In some cases, you can take steps to reduce SQL Server use of tempdb, helping to boost overall SQL Server performance. Although this is not a comprehensive list, here are some actions you may want to avoid:
- Using user-created temp tables. Often, I have seen T-SQL code that creates temp tables unnecessarily when the code could have been written to perform the same task without using temp tables. If you have to use a temp table, and the table has several thousand rows and is accessed frequently, consider adding an index to the table to boost performance of queries that are run against it. You may have to experiment with indexing to see if it helps or hurts overall performance.
- Scheduling jobs, such as DBCC CHECKDB, that use tempdb heavily, at times of the day when the SQL Server instance is busy.
- Using static- and keyset-driven cursors. In many cases, cursors can be avoided by rewriting the code.
- Using recursive common table expression queries. If the execution plan for such a query shows a spool operator, then you know that tempdb is being used to execute it.
- Using the SORT_IN_TEMPDB option when creating or rebuilding an index. Or if you decide to use this option, schedule the job to run during a less busy time of the day.
- Using online index rebuilding (Enterprise Edition), which uses row versioning and, in turn, uses tempdb.
- Using large object data types.
- Using table variables.
- Returning and sorting excessive amounts of data. Smaller sorts can be done in memory, but larger sorts spill over into tempdb.
- Returning and aggregating excessive amounts of data.
- Using joins that indicate a hash-type join in the query’s execution plan.
- Using AFTER and INSTEAD OF triggers.
- Using row-versioning-based transaction isolation levels.
You may know that you can’t avoid using the features in the previous list. If that’s the case, then use them. Be aware that each of the choices in the list directly impact tempdb’s performance. If you are having tempdb bottleneck issues and can’t reduce the activity occurring in tempdb, you will have to resort to using some of the other tempdb optimization suggestions in this chapter instead.
Preallocating tempdb space and avoiding use of autogrowth
Every time SQL Server restarts using its default settings, a new 8 MB copy of tempdb is created (regardless of the model database’s size), and autogrowth is set to 10 percent with unrestricted growth. Other than for the smallest SQL Server instances, 8 MB is rarely large enough, and because of this, tempdb must grow on demand to whatever size is necessary for your SQL Server instance to continue functioning. As you might expect, allowing autogrowth to size tempdb can often be problematic.
For example, let’s say that the average size for tempdb for a particular SQL Server instance is 1 GB. In this case, when SQL Server is restarted, the initial size of tempdb is 8 MB. Soon thereafter, autogrowth kicks in and grows tempdb 10 percent, over and over again, until it reaches its optimal size of about 1 GB. This can result in autogrowth kicking in at busy times of the day, using up valuable server resources; and delaying transactions from completing because autogrowth has to complete before the transaction can complete. In addition, the use of autogrowth contributes to physical file fragmentation on disk, which can put extra stress on your disk I/O subsystem.
Instead of using autogrowth to manage tempdb sizing, use ALTER DATABASE to change the tempdb’s MDF and LDF files to their optimal size. This way, when the SQL Server service is restarted, tempdb will be sized correctly right away. Additional performance gains can be achieved if you are using instant file initialization for your SQL Server instance, as this feature can dramatically decrease the time it takes for tempdb to be created when SQL Server is restarted, or when autogrowth events occur.
The difficult part is determining the optimal size of tempdb for a particular SQL Server instance. Although Books Online offers a way to estimate the normal size of tempdb, in practice, this is difficult to do. Personally, I start with an initial guess for the size (based on similar servers I am managing) and then watch the tempdb size over time. At some point, you should notice that tempdb is not growing, or that the amount of space you have allocated is not being fully used. Based on this information, I round this number up (the maximum amount of tempdb space actually used) to the nearest GB and use that figure as the optimal size for my tempdb database. This applies to both the MDF and LDF files. I still leave autogrowth on, as something unexpected may come up, and I want tempdb to grow if it needs to.
After a particular SQL Server instance’s tempdb has been set, every time SQL Server is restarted, an optimum-sized tempdb will be created in one fell swoop, and you won’t have to worry about the potential downsides of using autogrowth to size tempdb.
Don’t shrink tempdb if you don’t need to
After you have established the optimal size of tempdb, you may notice that the actual space used within tempdb varies considerably. Sometimes it may be less than half used, and other times it may be virtually full. For example, your tempdb database may be set to 1 GB (its optimal size), but sometimes only 250 MB of the allocated space is used, whereas at other times 950 MB of the allocated space is used. This is normal behavior, as the amount of space used in tempdb varies depending on what is happening on the SQL Server instance.
Don’t let this varying amount of space trick you into considering shrinking tempdb when space usage drops. If you do, you will be using SQL Server resources unnecessarily and contribute to worse overall performance. For example, let’s say that on a Friday you notice that your 1 GB of tempdb database is only using 250 MB, so you decide to shrink the tempdb database from 1 GB to 250 MB to recover disk space. But on Saturday, a series of weekly jobs runs that causes the now-250-MB-tempdb database to grow back to 1 GB using the autogrowth feature. In this case, all the resources required to shrink the tempdb database, and the resources to autogrow the database, will have been wasted.
In some cases, a wild-running query will cause tempdb to grow much larger than its normal size. If you are short on disk space and want to reclaim this empty space, and you are fairly confident that the wild-running query won’t repeat itself, you have two choices:
- Shrink the database and log files manually using DBCC SHRINKFILE. This option may not work as expected, as internal objects or the version store aren’t moved during this process, often resulting in less than expected shrinkage.
- Restart the SQL Server instance. This will re-create tempdb at the size it was as specified by the ALTER DATABASE statement. This option is more effective, but you must have downtime available for it to work.
Think of a tempdb’s optimum size as its maximum size. If you keep this in mind, then you won’t be tempted to shrink it when it is less than full.
Dividing tempdb among multiple physical files
Although there can only be one tempdb database per SQL Server instance, the tempdb database can be split into multiple physical files. If your tempdb is not active, then splitting it into multiple files may not buy you much additional performance. But if your tempdb is active, splitting it into multiple files can potentially boost your server’s overall performance.
Unfortunately, selecting the ideal number of physical files needed to optimize tempdb’s performance is not an exact science. As a starting point, you should consider creating as many physical files as there are CPU cores available to your SQL Server instance. For example, if your server has 8 CPU cores available to it, then divide the tempdb database into 8 physical files.
Note
If you decide to use multiple files for tempdb, it is important that each of them be exactly the same size (for example, if your tempdb’s normal size is 1 GB, then you should have 8 physical files of 125 MB each). This is because SQL Server uses a proportional fill strategy to fill the physical files. In addition, the autogrowth settings should be identical for each physical file in order to ensure that each physical file grows identically.
Multiple files can boost disk I/O performance and reduce contention by spreading I/O activity over multiple files. This is beneficial even if the multiple files reside on a single disk volume, although locating each physical tempdb file on its own disk volume would generally provide an even greater benefit (at a much greater cost).
On the other hand, using more physical disk files can increase switching costs and file management overhead because each object created in tempdb will have to have IAM pages created in each of the physical files. This and other unknowns about your SQL Server instance complicate providing an exact recommendation for the optimum number of physical files to use for your tempdb. I recommend that you perform tests in your own environment to determine the number of physical files that optimize the tempdb for your particular needs. After testing, you may find that more or fewer tempdb physical files are needed for optimum performance.
Although the tempdb MDF file should generally be split into multiple physical files, this is not the case with the tempdb LDF file. Because tempdb uses the simple recovery model, and because it uses it own optimized logging method, dividing the LDF file into multiple physical files rarely provides any benefit.
Moving tempdb to a disk separate from your other databases
By default, when installing SQL Server, tempdb is stored in the same location as the rest of your databases. Although this may work for smaller, less busy SQL Servers, it can cause a lot of I/O contention problems on busy SQL Servers. Ideally, tempdb should be located on its own disk volume(s), separate from other disk activity.
Locating tempdb on a fast I/O subsystem
No matter where tempdb is located, it should be located on the fastest I/O subsystem available to your SQL Server instance. Your hardware will limit what you can do, but you might want to consider the following options, assuming your hardware (and budget) permits:
- Avoid putting tempdb on a RAID 5 I/O subsystem, because tempdb is subject to heavy writes, and RAID 5 often offers poor write performance.
- Instead, locate tempdb on a RAID 1 or RAID 10 I/O subsystem, which offer better write performance.
- If your SQL Server instance is storing data on a storage area network (SAN), consult with your SAN engineer to determine the best location for optimal performance. As a general rule of thumb on SANs, tempdb should be located on its own logical unit number (LUN) with its own dedicated drives.
Adding RAM to your SQL server instance
Depending on the operation, SQL Server often tries to perform the action in the buffer cache. If the space is insufficient, then the operation may be forced to use tempdb. For example, whenever a sort is performed, SQL Server tries to perform it in the buffer cache because that is the fastest way to perform a sort. But if the sort data is large, or if the buffer cache does not have enough space, then the sorting is done in tempdb. This not only slows down the sort; it also places additional overhead on tempdb.
One way to avoid this and similar problems is to add additional RAM to your SQL Server instance, so that these activities can be completed in the buffer cache without using tempdb.
Using SQL Server 2008 transparent data encryption
In SQL Server 2008, if you turn on transparent data encryption (TDE) for any one database on a SQL Server instance, then tempdb automatically becomes encrypted, which can negatively affect the performance of all the databases on the server, whether they are encrypted or not. Because TDE encrypts and decrypts data pages as they are being moved between the buffer pool and disk, CPU utilization increases as encryption and decryption occur. On a busy server with an active tempdb, this can significantly boost CPU utilization, potentially hurting the overall performance of your SQL Server. If your tempdb database is busy, avoid using TDE. If you must use TDE for security purposes, consider limiting the load on the instance, or consider using beefed-up hardware—CPU, memory, and faster disk access—to help overcome the encryption and decryption performance penalty.
Leaving auto create statistics and auto update statistics on
By default, the auto create statistics and auto update statistics database options are turned on for tempdb. In most cases, don’t turn these options off, as SQL Server will automatically create and update statistics as needed in temporary tables and indexes, helping to boost performance of many operations performed on them.
Verifying CHECKSUM for SQL Server 2008
Although this is not a performance optimization suggestion, it is an important consideration for tempdb integrity. Starting with SQL Server 2008, the CHECKSUM database option is turned on by default for tempdb during a new install. This feature helps to ensure the integrity of the tempdb database. On the other hand, if you perform an in-place upgrade from an older SQL Server instance to SQL Server 2008, the CHECKSUM database option will not be automatically enabled for you on tempdb. To be on the safe side, you should manually enable the CHECKSUM database option on tempdb for added data protection.
Summary
As a DBA, it is a good idea to learn about the tempdb database and how it affects your SQL Server instances’ performance. You may quickly learn that you have a tempdb bottleneck and not even know it. If tempdb has become a bottleneck for your SQL Server, consider all your options and implement one at a time, beginning with the easiest ones, and if they don’t resolve the bottleneck, consider the more difficult ones until you have resolved the problem. And, before you implement any of the suggestions in this chapter, it is a good idea to test them on a test platform.
About the author
Brad M. McGehee is an MCSE+I, MCSD, and MCT (former) with a bachelor’s degree in Economics and a master’s in Business Administration. Involved in the industry since 1982, Brad is currently the Director of DBA Education for Red Gate Software, and is an accomplished Microsoft SQL Server MVP with over 14 years of experience with SQL Server and over 6 years of training experience. Brad is a frequent speaker at SQL PASS, European PASS, SQL Connections, SQLTeach, SQLBits, SQL Saturdays, TechFests, Code Camps, SQL Server user groups, and other industry seminars. He blogs at www.bradmcgehee.com.