Capacity Planning for tempdb

SQL Server 2008 R2

Other Versions

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

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 Understanding 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. 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	Bulk-import optimizations are available when triggers are enabled. SQL Server 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.	Optimizing Bulk Import Performance
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.	Using Common Table Expressions WITH common_table_expression (Transact-SQL)
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.	About Choosing a Cursor Type
Database Mail	See "Service Broker" that follows later in this table.	Database Mail
DBCC CHECKDB	DBCC CHECKDB uses tempdb work tables to hold intermediate results and for sort operations. To determine tempdb disk space requirements for the operation, run DBCC CHECKDB WITH ESTIMATEONLY.	DBCC CHECKDB (Transact-SQL) Optimizing DBCC CHECKDB Performance
Event notifications	See "Service Broker" that follows later in this table.	Understanding Event Notifications
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. 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. CREATE and UPDATE STATISTICS operations can use tempdb to sort the sample of rows for building statistics. For more information, see Disk Space Requirements for Index DDL Operations.	tempdb and Index Creation Special Guidelines for Partitioned Indexes Disk Space Requirements for Index DDL Operations Index Disk Space Example How Online Index Operations Work
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.	Using Large-Value Data Types
Multiple Active Result Sets (MARS)	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.	Using Multiple Active Result Sets (MARS)
Query notifications	See "Service Broker" that follows later in this table.	Using Query Notifications
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. 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.	Execution Plan Caching and Reuse
Row versioning	Row versioning is a general framework that is used to support the following features: Triggers Multiple Active Result Sets (MARS) Index operations that specify the ONLINE option Row versioning-based transaction isolation levels: A new implementation of read-committed isolation level that uses row versioning to provide statement-level read consistency. A snapshot isolation level to provide transaction-level read consistency. 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]	Understanding Row Versioning-Based Isolation Levels Row Versioning Resource Usage
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.	Overview (Service Broker)
Stored procedures	Stored procedures can create user objects such as global or local temporary tables and their indexes, variables, or parameters. 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.	Creating Stored Procedures (Database Engine)
Temporary tables and table variables 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	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. Local temporary tables and variables are cached when the following conditions are satisfied: Named constraints are not created. Data Definition Language (DDL) statements that affect the table are not run after the temporary table has been created, such as the CREATE INDEX or CREATE STATISTICS statements. The temporary object is not created by using dynamic SQL, such as: sp_executesql N'create table #t(a int)'. The temporary object is created inside another object, such as a stored procedure, trigger, user-defined function; or is the return table of a user-defined, table-valued function. 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	CREATE TABLE (Transact-SQL) Using Variables and Parameters (Database Engine) DECLARE @local_variable (Transact-SQL)
Triggers	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.	CREATE TRIGGER (Transact-SQL) Optimizing Bulk Import Performance Row Versioning Resource Usage
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. 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. 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.	CREATE FUNCTION (Transact-SQL)
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.	Implementing XML in SQL Server sp_xml_preparedocument (Transact-SQL) Querying XML Using OPENXML

Capacity Planning for Upgrades to SQL Server

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 test environment:

1. Set autogrow on for tempdb.
2. Execute individual queries or workload trace files and monitor tempdb space use.
3. Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
4. 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. 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.