Capacity Planning for tempdb
This topic describes the changes that have been made in SQL Server 2005 to the tempdb system database, and provides guidelines for determining the appropriate amount of disk space that tempdb requires. This topic also includes recommendations about how to configure tempdb for optimal performance in a production environment and information about how to monitor tempdb space usage.
In SQL Server 2005, tempdb requires more disk space than in earlier versions of SQL Server. This is because of the following changes:
- Some new features in SQL Server 2005 use tempdb.
- Some features that use tempdb in earlier versions of SQL Server may require more tempdb disk space in SQL Server 2005.
- Some features that do not use tempdb in earlier versions of SQL Server use tempdb in SQL Server 2005.
Because of these changes, it is important that you provide sufficient tempdb disk space when you upgrade to SQL Server 2005 to allow for your current production workload plus the additional space requirements of SQL Server features that use tempdb.
How tempdb Is Used
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.
User Objects
User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:
- User-defined tables and indexes
- System tables and indexes
- Global temporary tables and indexes
- Local temporary tables and indexes
- Table variables
- Tables returned in table-valued functions
Internal Objects
Internal objects are created as necessary by the SQL Server Database Engine to process SQL Server statements. Internal objects are created and dropped within the scope of a statement. Internal objects can be one of the following:
- Work tables for cursor or spool operations and temporary large object (LOB) storage.
- Work files for hash join or hash aggregate operations.
- Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
Each internal object uses a minimum of nine pages; one IAM page and one eight-page extent. For more information about pages and extents, see Pages and Extents.
Version Stores
A version store is a collection of data pages that hold the data rows that are required to support the features that use row versioning. In SQL Server 2005, there are two version stores: a common version store and an online-index-build version store. The version stores contain the following:
- Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
- Row versions that are generated by data modification transactions for features such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
The following table lists the features in SQL Server that create user objects, internal objects, or row versions in tempdb. Whenever possible, the methods for estimating disk space use are provided.
| Feature | tempdb use | Additional information |
|---|---|---|
Bulkload operations with triggers enabled |
In SQL Server 2005, bulk-import optimizations are available when triggers are enabled. SQL Server 2005 uses row versioning for triggers that update or delete transactions. A copy of each deleted or updated row is added to the version store. See "Triggers" that follows later in this table. |
|
Common table expression queries |
A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation. |
|
Cursors |
Keyset-driven and static cursors use work tables that are built in tempdb. Keyset-driven cursors use the work tables to store the set of keys that identify the rows in the cursor. Static cursors use a work table to store the complete result set of the cursor. The disk space usage for cursors may vary depending on the query plan that is chosen. If the query plan is the same as earlier versions of SQL Server, the disk space usage is approximately the same. |
|
Database Mail |
See "Service Broker" that follows later in this table. |
|
DBCC CHECKDB |
DBCC CHECKDB uses tempdb work tables to hold intermediate results and for sort operations. The disk space usage for DBCC CHECKDB is increased due to the following changes in the DBCC CHECK operation:
To determine tempdb disk space requirements for the operation, run DBCC CHECKDB WITH ESTIMATE_ONLY. |
|
Event notifications |
See "Service Broker" that follows later in this table. |
|
Indexes |
When you create or rebuild an index (offline or online) and set the SORT_IN_TEMPDB option to ON, you direct the Database Engine to use tempdb to store the intermediate sort results that are used to build the index. When SORT_IN_TEMPDB is specified and sorting is required, tempdb must have sufficient disk space to hold the largest index plus disk space that is equal to the value of the index create memory option. For more information, see Index Disk Space Example. The disk space usage for offline index operations that do not use SQL Server 2005 features is the same as earlier versions of SQL Server. Changes to tempdb Usage in SQL Server 2005 In SQL Server 2005, tables and indexes can be partitioned. For partitioned indexes, if the SORT_IN_TEMPDB index option is specified and the index is aligned with the base table, there must be sufficient space in tempdb to hold the intermediate sort runs of the largest partition. If the index is not aligned, there must be sufficient space in tempdb to hold the intermediate sort runs of all partitions. For more information, see Special Guidelines for Partitioned Indexes. Online index operations use row versioning to isolate the index operation from the effects of modifications that are made by other transactions. Row versioning removes the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb. When online index operations use SORT_IN_TEMPDB and sorting is required, tempdb must also have the additional disk space previously described for intermediate sort results. Online index operations that create, drop, or rebuild a clustered index also require additional disk space to build and maintain a temporary mapping index. For more information, see Disk Space Requirements for Index DDL Operations. |
Special Guidelines for Partitioned Indexes |
Large object (LOB) data type variables and parameters |
The large object data types are varchar(max), nvarchar(max), varbinary(max)text, ntext, image, and xml. These types can be up to 2 GB in size and can be used as variables or parameters in stored procedures, user-defined functions, batches, or queries. Parameters and variables that are defined as a LOB data type use main memory as storage if the values are small. However, large values are stored in tempdb. When LOB variables and parameters are stored in tempdb, they are treated as internal objects. You can query the sys.dm_db_session_space_usage dynamic management view to report the pages allocated to internal objects for a given session. Some intrinsic string functions, such as SUBSTRING or REPLICATE, may require intermediate temporary storage in tempdb when they are operating on LOB values. Similarly, when a row versioning-based transaction isolation level is enabled on the database and modifications of large objects are made, the changed fragment of the LOB is copied to the version store in tempdb. |
|
Multiple Active Result Sets (MARS) |
In SQL Server 2005, multiple active result sets can occur under a single connection; this is commonly referred as MARS. If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) when there is an active result set, the rows that are affected by the modification statement are stored in the version store in tempdb. See "Row versioning" that follows later in this table. |
|
Query notifications |
See "Service Broker" that follows later in this table. |
|
Queries |
Queries that contain SELECT, INSERT, UPDATE, and DELETE statements can use internal objects to store intermediate results for hash joins, hash aggregates, or sorting. In SQL Server 2005, when a query execution plan is cached, the work tables required by the plan are cached. When a work table is cached, the table is truncated and nine pages remain in the cache for reuse. This improves the performance of the next execution of the query. If the system is low on memory, the Database Engine can remove the execution plan and drop the associated work tables. |
|
Row versioning |
Row versioning is a general framework in SQL Server 2005 that is used to support the following features:
Row versions are held in the tempdb version store for as long as an active transaction must access it. The content of the current version store is returned in sys.dm_tran_version_store. Version-store pages are tracked at the file level because they are global resources. You can use the version_store_reserved_page_count column in sys.dm_db_file_space_usage to view the current size of the version store. Version-store cleanup must consider the longest running transaction that requires access to the particular version. The longest running transaction related to version store clean-up can be discovered by viewing the elapsed_time_seconds column in sys.dm_tran_active_snapshot_database_transactions. The counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object can be used to monitor the size and rate of growth of the row-version store in tempdb. For more information, see SQL Server, Transactions Object. To estimate how much space is required in tempdb for row versioning, you have to first consider that an active transaction must keep all its changes in the version store. This means that a snapshot transaction that starts later can access the old versions. Also, if there is an active snapshot transaction, all the version store data that is generated by transactions that are active when the snapshot starts must also be kept. Here is a basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
|
Service Broker |
Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task. These application components exchange messages that contain the information that is required to complete the task. Service Broker explicitly uses tempdb for preserving existing dialog context that cannot stay in memory. The size is approximately 1 KB per dialog. Also, Service Broker implicitly uses tempdb by the caching of objects in the context of query execution, such as work tables that are used for timer events and background delivered conversations. Database Mail, Event Notifications, and Query Notifications implicitly use Service Broker. |
|
Stored procedures |
Stored procedures can create user objects such as global or local temporary tables and their indexes, variables, or parameters. In SQL Server 2005, temporary objects in stored procedures can be cached to optimize the operations that drop and create these objects. This behavior can increase tempdb disk space requirements. Up to nine pages per temporary object are stored for reuse. See "Temporary tables and table variables" that follows later in this table. |
|
Temporary tables and table variables
|
Temporary tables and table variables are stored in tempdb. The disk space requirements for temporary table objects are the same as earlier versions of SQL Server. The method for estimating the size of a temporary table size is the same as estimating the size of a standard table. For more information, see Estimating the Size of a Table. A table variable behaves like a local variable. A table variable is of type table and is primarily used for the temporary storage of a set of rows that are returned as the result set of a table-valued function. The disk space that is required to hold a table variable depends on the size of the declared variable and the value stored in the variable. In SQL Server 2005, local temporary tables and variables are cached when the following conditions are satisfied:
When a temporary table or table variable is cached, the temporary object is not deleted when its purpose ends. Instead, the temporary object is truncated. Up to nine pages are stored and reused the next time that the calling object is executed. Caching allows operations that drop and create the objects to execute very quickly and reduces page allocation contention. For optimal performance, you should calculate the disk space that is required for cached local temporary tables or table variables in tempdb by using the following formula: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
|
Triggers |
In earlier versions of SQL Server, trigger logic is based on log records, and tempdb is not used. In SQL Server 2005, the inserted and deleted tables that are used in AFTER triggers are created in tempdb. That is, the rows that are updated or deleted by the trigger are versioned. This includes all of the rows that are modified by the statement that fired the trigger. Rows that are inserted by the trigger are not versioned. INSTEAD OF triggers use tempdb in way similar to queries. The disk space usage for INSTEAD OF triggers is the same as earlier versions of SQL Server. See "Queries" previously in this table. When you bulk load data with triggers enabled, a copy of each deleted or updated row is added to the version store. |
|
User-defined functions |
User-defined functions can create temporary user objects, such as global or local tables and their indexes, variables, or parameters. For example, the return table of a table-valued function is stored in tempdb. In SQL Server 2005, the data types that are allowed for parameters and return values in scalar functions and table-valued functions include most LOB data types. For example, a return value can be of type xml or varchar(max). See "Large object (LOB) data type variables and parameters" previously in this table. In SQL Server 2005, temporary objects in table-valued user-defined functions can be cached to optimize the operations that drop and create these objects. See "Temporary tables and table variables" previously in this table. |
|
XML |
Variables and parameters of type xml can be up to 2 GB. They use main memory as storage as long as the values are small. However, large values are stored in tempdb. See "Large object (LOB) data type variables and parameters" previously in this table. The sp_xml_preparedocument system stored procedure creates a work table in tempdb. The MSXML parser uses the work table to store the parsed XML document. The disk space requirements for tempdb is nearly proportional to the size of the specified XML document when the stored procedure is execute. |
Capacity Planning for Upgrades to SQL Server 2005
Determining the appropriate size for tempdb in a production environment depends on many factors. As described previously in this topic, these factors include the existing workload and the SQL Server features that are used. We recommend that you analyze the existing workload by performing the following tasks in a SQL Server 2005 test environment:
- Set autogrow on for tempdb.
- Execute individual queries or workload trace files and monitor tempdb space use.
- Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
- Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.
For more information about monitoring tempdb space, see Troubleshooting Insufficient Disk Space in tempdb. For more information about estimating tempdb usage during index operations, see Index Disk Space Example.
Configuring tempdb for Production Environments
To achieve optimal tempdb performance, follow the guidelines and recommendations provided in Optimizing tempdb Performance.
How to Monitor tempdb Use
Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. In SQL Server 2005, you can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.
See Also
Tasks
Troubleshooting Insufficient Disk Space in tempdb
Concepts
tempdb Database
Optimizing tempdb Performance
Other Resources
Optimizing Databases
Working with tempdb in SQL Server 2005
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|