Role and optimization of SQL Server tempdb

The tempdb system database is a global resource that is available to all users who connect 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 created explicitly by the user. These objects can be in the scope of the user session or in the scope of the use case where the object was created. Routines can be stored procedures, triggers, or user-defined functions. The user object 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 variable

• Table returned in table-valued function

Internal objects

Internal objects are created as needed by the SQL Server database engine and are used to process SQL Server statements. Internal objects can be created and deleted in the scope of the statement. An internal object can be one of the following:

• Worksheets that are stored with cursor or spool operations and temporary large object (LOB).

• A working file for a hash join or hash aggregation operation.

• Intermediate sort results for operations such as creating or rebuilding indexes, if specified sort_in_tempdb, or intermediate sorting results for some GROUP by, ORDER by, or UNION queries.

Each internal object uses at least nine pages; an IAM page, a eight-page area. For more information about pages and extents, see pages and extents.

The following table lists the features of user objects, internal objects, or row versions of tempdb that are created in SQL Server. Whenever possible, a method of estimating disk space usage is provided.

Function	Tempdb uses	Other information
Bulk load operation with trigger enabled	You can use bulk import optimizations 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" in the following section of this table.	Optimize Bulk-Import performance
Common-Table expression queries	A common table expression can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. When a query plan for a common table expression query uses the Spool operator to hold intermediate query results, the database engine creates a worksheet in tempdb to support this operation.	Using common table expressions With common_table_expression (Transact-SQL)
Cursor	Keyset-driven cursors and static cursors use the worksheet that is generated in tempdb . Keyset-driven cursors use worksheets to store key sets that identify the rows in the cursor. Static cursors use worksheets to store the full result set of a cursor. If you select a different query plan, the disk space usage of the cursor may also be different. If the query plan is the same as the query plan in earlier versions of SQL Server, disk space usage is approximately the same.	About selecting a cursor type
Database Mail	See "Service Broker" in the following section of this table.	Database Mail
DBCC CHECKDB	DBCC CHECKDB uses the tempdb worksheet to hold intermediate results or to sort operations. To determine the tempdb disk space requirements for the operation, run DBCC CHECKDB with Estimateonly.	DBCC CHECKDB (Transact-SQL) Optimize DBCC CHECKDB Performance
Event notification	See "Service Broker" in the following section of this table.	Understanding Event Notifications
Index	When you create or rebuild an index (offline or online) and set the SORT_IN_TEMPDB option to on, you can direct the database engine to use TEMPDB to store the intermediate sort results used to build the index. If SORT_IN_TEMPDB is specified and needs to be sorted, TEMPDB must have disk space sufficient to contain the maximum index, and also must have disk space equal to the value of the index create memory option. For more information, see Index disk space sample. 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 enough space in TEMPDB to accommodate the middle sort segment of the largest partition. If the index is misaligned, there must be enough space in tempdb to hold the intermediate sort segments for all partitions. For more information, see Special guidelines for partitioned indexes. Online index operations use row versioning to make index operations unaffected by modifications made by other firms. Row versioning does not require a shared lock to be requested on a row that has been read. During an online index operation, concurrent user update and delete operations require a certain amount of space to be used for version records in tempdb . If an online index operation uses sort_in_tempdb and needs to be sorted, TEMPDB must have additional disk space in addition to the disk space used for the intermediate sort results previously described. An online index operation that creates, deletes, or rebuilds a clustered index also requires additional disk space to generate and maintain a temporary mapping index. The CREATE and UPDATE STATISTICS operations can use tempdb to sort the row samples to generate 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 sample How online index operations work
Large object (LOB) data type variables and parameters	Large object data types include   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 LOB data types use main memory as a store when the value is small. However, larger values are stored in   tempdb  . When LOB variables and parameters are stored in   tempdb  , they can be treated as internal objects. You can query  sys.dm_db_session_space_usage  dynamic management view to report pages that are assigned to internal objects for a given session. Some internal string functions (for example, SUBSTRING or REPLICATE) that perform operations on LOB values may need to be &NBSP: Intermediate temporary storage in tempdb  . Similarly, if row versioning-based transaction isolation levels are enabled for a database and modifications are made to large objects, the changed fragments of the LOB are copied to   tempdb   in the version store.	Using large value data types
Multiple active result sets (MARS)	Multiple active result sets can appear under a single connection, which is often referred to as MARS. If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) in the presence of an active result set, the rows affected by the modified statement are stored in the version store of tempdb . See "Row versioning" in the following section of this table.	Using multiple active result sets (MARS)
Query notifications	See "Service Broker" in the following section of this table.	Using Query Notifications
Inquire	Queries that contain SELECT, INSERT, UPDATE, and DELETE statements can use internal objects to store intermediate results for hash joins, hash aggregations, or sorting. When the query execution plan is cached, the worksheets that are required for the plan are also cached. When the worksheet is cached, the table is truncated and nine pages are left in the cache for reuse. This can improve performance the next time the query is executed. If system memory is low, the database engine can delete the execution plan and the associated worksheets.	Cache and reuse of execution plans
Row version Control	Row versioning is a general framework for supporting the following features: Trigger Multiple active result sets (MARS) Specify an index operation for the ONLINE option Row versioning-based transaction isolation level: The new implementation of the read-committed isolation level, which uses row versioning to provide statement-level read consistency. The snapshot isolation level used to provide transactional-level read consistency. The row version is persisted in the tempdb version store when the active transaction must access it. The contents of the current version store are returned in Sys.dm_tran_version_store. Because the version store page is a global resource, it is tracked at the file level. 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 a specific version. You can find the longest running transaction associated with version store cleanup by looking at the elapsed_time_seconds column in sys.dm_tran_active_snapshot_database_transactions. You can use the free Space in Tempdb (KB) and Version Store Size (KB) counters in the transactions object to monitor tempdb The size and growth rate of the row version store. For more information, see SQL Server transactions objects. To estimate the amount of space required for version control in tempdb , be sure to first consider that the active transaction must keep all of its changes in the version store. This means that the snapshot transaction that is started later can access the old version. In addition, if there is an active snapshot transaction, all version store data generated by the transaction that is active when the snapshot is started must also be persisted. Here is the 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 The use of row version control resources
Service Broker	Service Broker helps developers build asynchronous, loosely coupled applications where components that are independent of each other 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 to preserve existing dialog box contexts that cannot be persisted in memory. The size of each dialog box is approximately 1 KB. In addition, service Broker will implicitly use tempdb to cache objects in the context of the query execution (for example, worksheets for timer events and background delivery sessions). Database Mail, event notifications, and query notifications are implicitly used by service Broker.	Overview (Service Broker)
Stored Procedures	Stored procedures can create user objects, such as global temporary tables or local temporary tables and their indexes, variables, or parameters. Temporary objects in stored procedures can be cached to optimize the operation of deleting and creating those objects. This behavior increases the tempdb disk space requirement. Each temporary object can store up to nine pages for reuse. See "Temporary tables and table variables" later in this table .	Create a stored procedure (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 variable Table returned in table-valued function	Temporary tables and table variables are stored in tempdb . Temporary table objects have the same disk space requirements as in earlier versions of SQL Server. The method of estimating the size of a temporary table is the same as estimating the size of a standard table. For more information, see Estimating the size of a table. The table variable behaves like a local variable. Table variables are of type table and are primarily used to temporarily store the rowset returned as a result set of a table-valued function. The disk space required to save the table variable depends on the declared variable and the size of the value stored in the variable. Local temporary tables and variables are cached when the following conditions are met: No naming constraints were created. After the staging table is created, the data definition language (DDL) statement that affects the table (for example, the Create INDEX or create STATISTICS) statement is not running. No temporary objects were created using dynamic SQL (for example, sp_executesql N ' CREATE TABLE #t (a int) '). Creates a temporary object inside another object (for example, a stored procedure, trigger, user-defined function), or a temporary object is a return table for a user-defined table-valued function. When a temporary table or table variable is cached, it is not deleted when the temporary object completes its mission. Instead, the temporary object is truncated. Temporary objects can store up to nine pages to be reused the next time the call is executed. By caching, the actions of deleting and creating objects can be performed very quickly, and the page allocation contention can be reduced. For best performance, you should use the following formula to calculate the disk space required to cache local temporary tables or table variables in tempdb : 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)
Trigger	The inserted and deleted tables used in the After trigger are created in tempdb . That is, the rows that are updated or deleted by the trigger should be versioned. This includes all rows modified by the statement that triggered the trigger. The rows inserted by the trigger are not version controlled. INSTEAD of triggers use tempdb in a manner similar to how queries are used. INSTEAD of triggers use the same amount of disk space as in earlier versions of SQL Server. See "Query" in the previous section of this table. When you bulk load data with triggers enabled, a copy of each deleted row or update row is added to the version store.	CREATE TRIGGER (Transact-SQL) Optimize Bulk-Import performance The use of row version control resources
User-defined functions	user-defined functions to create temporary user objects, such as global temporary tables or local temporary tables and their indexes, variables, or parameters. For example, the table-valued function's return table is stored in   tempdb  . Allows the data types used for parameters and return values in scalar functions and table-valued functions to include most LOB data types. For example, the type of the return value can be  xml  or  varchar (max). See "Large object (LOB) data type variables and parameters" in the previous section of this table. Can cache temporary objects in user-defined table-valued functions to optimize the operation of deleting and creating those objects. See "Temporary Tables and  table  variables" earlier in this table.	CREATE FUNCTION (Transact-SQL)
Xml	variables and parameters of type XML can be up to 2 GB in size. As long as the values are small, they use the main memory as the storage area. However, larger values are stored in tempdb . See "Large object (LOB) data type variables and parameters" in the previous section of this table. The sp_xml_preparedocument system stored procedure creates a worksheet in tempdb . The MSXML parser uses this worksheet to store the parsed XML document. When you execute a stored procedure, the disk space requirements fortempdb are almost proportional to the size of the specified XML document.	Implementing XML in SQL Server sp_xml_preparedocument (Transact-SQL) Querying XML using OPENXML

upgrade capacity planning for SQL Server

Determining the appropriate size of tempdb in a production environment depends on a number of factors. As described earlier in this topic, these factors include existing workloads and the SQL Server features that are used. We recommend that you analyze an existing workload by performing the following tasks in a SQL Server test environment:

1. Set up automatic growth for tempdb .

2. Perform a separate query or workload trace file to monitor tempdb space usage.

3. Perform index maintenance operations (for example, rebuild indexes) to monitor tempdb space.

4. Use the space in the previous step to predict the total workload usage, adjust this value for scheduled concurrency activities, and then set the size of tempdb accordingly.

Optimizing tempdb Performance

The size and physical location of the tempdb database can affect the performance of the system. For example, if the size defined for tempdb is too small, each time you restart the instance of SQL Server, you may be consuming part of the system processing load so that tempdb automatically grows to the size that is required to support the workload. You can avoid this overhead by increasing the size of the tempdb data and log files. Information about determining the amount of appropriate disk space required for tempdb

• The

• Sets the file increment to a reasonable size to avoid a small increment of the tempdb database file. If the increment of the file is too small compared to the amount of data written to tempdb, tempdb may need to be continuously expanded. This will affect performance. We recommend that you follow these general guidelines when you set filegrowth increments for the tempdb file.

   

  tempdb file size	F Ilegrowth increment
  0 to [MB]	Ten MB
  100 to + MB	MB
  More than MB or more	10%*

  * You may have to adjust this percentage based on the speed of the I/O subsystem where the tempdb file resides. To avoid potential latch timeouts, we recommend limiting the autogrow operation to approximately two minutes. For example, if the I/O subsystem Initializes a file at a speed of 6 MB per second, the filegrowth increment should be set to the maximum value, regardless of the size of the tempdb file. If possible, use instance database file initialization to improve the performance of autogrow operations.

• Pre-allocate space for all tempdb files by setting the file size to a value that is sufficient to accommodate a typical workload in your environment. This avoids the performance impact of tempdb as it expands too frequently. The tempdb database should be set to autogrow, but this setting will be used to increase disk space in the event of an unexpected situation.

• Create as many files as necessary to maximize the disk width. Using multiple files can reduce tempdb storage contention and gain greater scalability. However, do not create too many files, because this operation can degrade performance and increase administrative overhead. As a general rule, create a data file for each CPU in the server (to interpret any affinity mask settings), and then adjust the number of files up and down as needed. Note that a dual-core CPU will be treated as two CPUs.

• Make the size of each data file the same so that you can optimize the performance of the scale fill.

• Place the tempdb database in the fast I/O subsystem. If you have many direct-attached disks, use disk stripe.

• Place the tempdb database on a disk other than the disk used by the user database.

Role and optimization of SQL Server tempdb