The tempdb Database

In some ways, your tempdb database is just like any other database, but it has some unique behaviors. They are not all relevant to the topic of this chapter, so I will provide some references to other chapters where you can find additional information.

As mentioned earlier, the biggest difference between tempdb and all the other databases in your SQL Server instance is that tempdb is re-creatednot recoveredevery time SQL Server is restarted. You can think of tempdb as a workspace for temporary user objects and internal objects explicitly created by SQL Server itself.

Every time tempdb is re-created, it inherits most database options from the model database. However, the recovery mode is not copied because tempdb always uses simple recovery, which will be discussed in detail in Chapter 5. Certain database options cannot be set for tempdb, such as OFFLINE, READONLY, and CHECKSUM. You also cannot drop the tempdb database.

In SIMPLE mode, the tempdb database's log is constantly being truncated, and it can never be backed up. No recovery information is needed because every time SQL Server is started, tempdb is completely re-created; any previous user-created temporary objects (that is, all your tables and data) will be gone.

Logging for tempdb is also different than for other databases. (Normal logging will be discussed in Chapter 5.) Many people assume that there is no logging in tempdb, but this is not true. Operations within tempdb are logged so that transactions on temporary objects can be rolled back, but the records in the log contain only enough information to roll back a transaction, not to recover (or redo) it.

As I mentioned earlier, recovery is run on a database as one of the first steps in creating a snapshot. We can't recover tempdb, so we cannot create a snapshot of it, and this means we can't run DBCC CHECKDB (or, in fact, most of the DBCC validation commands) in online mode. Another difference with running DBCC in tempdb is that SQL Server will skip all allocation and catalog checks. Running DBCC CHECKDB (or CHECKTABLE) in tempdb acquires a Shared Table lock on each table as it is checked. (Locking will be discussed in Chapter 8.)

Objects in tempdb

Three types of objects are stored in tempdb: user objects, internal objects, and the version store, which is new in SQL Server 2005.

User Objects

All users have the privileges to create and use private and global temporary tables that reside in tempdb. (Private and global table names have the # or ## prefix, respectively, which are discussed in Inside SQL Server 2005: TSQL Programming.) However, by default, users don't have the privileges to USE tempdb and then create a table there (unless the table name is prefaced with # or ##). But you can easily add such privileges to model, from which tempdb is copied every time SQL Server is restarted, or you can grant the privileges in an autostart procedure that runs each time SQL Server is restarted. If you choose to add those privileges to the model database, you must remember to revoke them on any other new databases that you subsequently create if you don't want them to appear there as well.

Other user objects that need space in tempdb include table variables and table-valued functions. The user objects that are created in tempdb are in many ways treated just like user objects in any other database. Space must be allocated for them when they are populated, and the metadata needs to be managed. You can see user objects by examining the system catalog views, such as sys.objects, and information in the sys.partitions and sys.allocation_units views will allow you to see how much space is taken up by user objects. I'll discuss these views in Chapter 6.

Internal Objects

Internal objects in tempdb are not visible using the normal tools, but they still take up space from the database. They are not listed in the catalog views because their metadata is stored only in memory. The three basic types of internal objects are work tables, work files, and sort units.

Work tables are created by SQL Server during the following operations:

Spooling, to hold intermediate results during a large query
Running DBCC CHECKDB or DBCC CHECKTABLE
Working with XML or varchar(MAX) variables
Processing SQL Service Broker objects
Working with static or keyset cursors

Work files are used when SQL Server is processing a query that uses a hash operator, either for joining or aggregating data.

Sort units are created when a sort operation takes place, and this occurs in many situations in addition to a query containing an ORDER BY clause. SQL Server uses sorting to build an index, and it might use sorting to process queries involving grouping. Certain types of joins might require that SQL Server first sort the data before performing the join. Sort units are created in tempdb to hold the data as it is being sorted. SQL Server can also create sort units in user databases in addition to tempdb, in particular when creating indexes. As you'll see in Chapter 7, when you create an index, you have the option to do the sort in the current user database or in tempdb.

Version Store

The version store supports a new technology in SQL Server 2005 for row-level versioning of data. Older versions of updated rows are kept in tempdb in the following situations:

When a trigger is fired
When a DML command is executed in a database that allows snapshot transactions
When multiple active result sets (MARS) is invoked from a client application
During online index builds or rebuilds when there is concurrent DML on the index

Versioning, which is a new concurrency control feature in SQL Server 2005, and snapshot transactions will be discussed in detail in Chapter 8.

Optimizations in tempdb

Because tempdb is so much more heavily used in SQL Server 2005 than in previous versions, you have to take much more care in managing it. The next section will present some best practices and monitoring suggestions. In this section, I'll tell you about some of the internal optimizations in SQL Server that allow tempdb to manage objects much more efficiently.

Logging Optimizations

As you know, every operation that affects your user database in any way is logged. In tempdb, however, this is not entirely true. For example, with logging update operations, only the original data (the before image) is logged, not the new values (the after image). In addition, the commit operations and committed log records are not flushed to disk synchronously in tempdb, as they are in other databases.

Allocation and Caching Optimizations

Many of the allocation optimizations are used in all databases, not just tempdb. However, tempdb is most likely the database in which the greatest number of new objects are created and dropped during production operations, so the impact on tempdb is greater than on user databases. In SQL Server 2005, allocation pages are accessed much more efficiently to determine where free extents are available; you should see far less contention on the allocation pages than in previous versions. SQL Server 2005 also has a more efficient search algorithm for finding an available single page from mixed extents. When a database has multiple files, SQL Server 2005 has a very efficient proportional fill algorithm that allocates space to multiple data files, proportional to the amount of free space available in each file.

Another optimization specific to tempdb prevents you from having to allocate any new space for some objects. If a work table is dropped, one Index Allocation Map (IAM) page and one extent are saved (for a total of nine pages), so there is no need to deallocate and then reallocate the space if the same work table needs to be created again. This dropped work table cache is not very big and has room for only 64 objects. If a work table is truncated internally and the query plan that uses that worktable is still in the plan cache, again the first IAM page and the first extent are saved. For these truncated tables, there is no specific limitation on the number of objects that can be cached; it depends only on the available memory space.

User objects in tempdb can also have some of their space cached if they are dropped. For a small table of less than 8 MB, dropping a user object in tempdb causes one IAM page and one extent to be saved. However, if the table has had any additional DDL performed, such as creating indexes or constraints, or if the table was created using dynamic SQL, no caching is done.

For a large table, the entire drop is done as a deferred operation. Deferred drop operations are in fact used in every database as a way to improve overall throughput because a thread does not need to wait for the drop to complete before proceeding with its next task. Like the other allocation optimizations that are available in all databases, the deferred drop probably provides the most benefit in tempdb, which is where tables are most likely to be dropped during production operations. A background thread eventually cleans up the space allocated for dropped tables, but until then, the allocated space remains. You can detect this space by looking at the sys.allocation_units system view for rows with a type value of 0, which indicates a dropped object; you will also see that the column called container_id is 0, which indicates that the allocated space does not really belong to any object. We'll look at sys.allocation_units and the other system views that keep track of space usage in Chapter 6.

Best Practices

By default, your tempdb database is created on only one data file. You will probably find that multiple files give you better I/O performance and less contention on the global allocation structures (the GAM and SGAM pages). An initial recommendation is that you have one file per CPU, but your own testing based on your data and usage patterns might indicate more or less than that. For the greatest efficiency with the proportional fill algorithm, the files should be the same size. The downside of multiple files is that every object will have multiple IAM pages and there will be more switching costs as objects are accessed. It will also take more effort just to manage the files. No matter how many files you have, they should be on the fastest disks you can afford. One log file should be sufficient, and that should also be on a fast disk.

To determine the optimum size of your tempdb, you must test your own applications with your data volumes, but knowing when and how tempdb is used can help you make preliminary estimates. Keep in mind that there is only one tempdb for each SQL Server instance, so one badly behaving application can affect all other users in all other applications. In Chapter 7, we'll look at estimating the size of tables and indexes, and we'll talk more about online index building and the tempdb space required for that operation. Finally, in Chapter 8, you'll see how to determine the size of the version store. All these factors affect the space needed for your tempdb.

Database options for tempdb should rarely be changed, and some options are not applicable to tempdb. In particular, the autoshrink option is ignored in tempdb. In any case, shrinking tempdb is not recommended, unless your workload patterns have changed significantly. If you do need to shrink your tempdb, you're probably better off shrinking each file individually. Keep in mind that the files might not be able to shrink if any internal objects or version store pages need to be moved. The best way to shrink tempdb is to ALTER the database, change the files' sizes, and then stop and restart SQL Server so tempdb is rebuilt to the desired size. You should allow your tempdb files to autogrow only as a last resort and only to prevent errors due to running out of room. You should not rely on autogrow to manage the size of your tempdb files. Autogrow causes a delay in processing when you can probably least afford it, although the impact is somewhat less if you use instant file initialization. You should determine the size of tempdb through testing and planning so that tempdb can start with as much space as it needs and won't have to grow while your applications are running.

Here are some tips for making optimum use of your tempdb. Later chapters will elaborate on why these suggestions are considered best practices:

Take advantage of tempdb object caching.
Keep your transactions short, especially those that use snapshot isolation, MARS, or triggers.
If you expect a lot of allocation page contention, force a query plan that uses less of tempdb.
Avoid page allocation and deallocation by keeping columns that are to be updated at a fixed size rather than a variable size (which can implement the update as a delete followed by an update).
Do not mix long and short transactions from different databases (in the same instance) if versioning is being used.

tempdb Space Monitoring

Quite a few tools, stored procedures, and system views report on object space usage, as discussed in Chapter 6 and Chapter 7. However, one set of system views reports information only for tempdb. The simplest view is sys.dm_db_file_space_usage, which returns one row for each file in tempdb. It returns the following columns:

database_id (even though the database ID 2 is the only one used)
file_id
unallocated_extent_page_count
version_store_reserved_page_count
user_object_reserved_page_count
internal_object_reserved_page_count
mixed_extent_page_count

These columns can show you how the space in tempdb is being used for the three types of storage: user objects, internals objects, and version store.

Two other system views are similar to each other:

sys.dm_db_task_space_usage This view returns one row for each active task and shows the space allocated and deallocated by the task for user objects and internal objects. If no tasks are being run by a session, this view still gives you one row for the session, with all the space values showing 0. No version store information is reported because that space is not associated one any particular task or session. Every running task starts with zeros for all the space allocation and deallocation values.
sys.dm_db_session_space_usage This view returns one row for each session, with the cumulative values for space allocated and deallocated by the session for user objects and internal objects, for all tasks that have been completed. In general, the space allocated values should be the same as the space deallocated values, but if there are deferred drop operations, allocated values will be greater than the deallocated values. Keep in mind that this information is not available to all users; a special permission called VIEW SERVER STATE is needed to select from this view.