tempdb – Space Usage, Capacity planning, Performance Tuning

For every SQL Dba managing TempDB is a very important task in his Day to Day tasks , it is always better to understand the tempdb in a more practical way,

Because Tempdb is an important System database if it is not taken care properly it will definitely disrupts the services and applications accessing  SQL server

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

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 ExpressionsWITH 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 tempdbwork 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 CreationSpecial Guidelines for Partitioned IndexesDisk 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_usagedynamic 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 LevelsRow 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 tempdbfor preserving existing dialog context that cannot stay in memory. The size is approximately 1 KB per dialog.Also, Service Broker implicitly uses tempdbby 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 tempdbin 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 PerformanceRow 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 Serversp_xml_preparedocument (Transact-SQL)Querying XML Using OPENXML

Capacity planning for temp db

Determining the appropriate size for tempdbin 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.

Monitoring and troubleshooting Tempdb disk space problems

SELECT

[NAME]                  AS [LOGICAL FILE NAME],

CASE TYPE_DESC

WHEN ‘ROWS’ THEN ‘DATAFILE’

WHEN ‘LOG’  THEN ‘LOGFILE’

END                     AS [FILE TYPE],

PHYSICAL_NAME           AS [FILE PATH],

[SIZE]                  AS [FILE SIZE IN KB],
[SIZE]/1024.00                  AS [FILE SIZE IN MB],
[SIZE]/(1024.00*1024)                  AS [FILE SIZE IN GB],

CASE GROWTH

WHEN 0 THEN ‘DISABLED’

ELSE ‘ENABLED’
END                     AS [AUTO GROWTH]

,GROWTH

,CASE      IS_PERCENT_GROWTH

WHEN 0 THEN ‘GROWTH IN KB’
ELSE ‘GROWTH IN PERCENTAGE’

END AS  ‘TYPE OF GROWTH’

FROM TEMPDB.SYS.DATABASE_FILES

Query

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores 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 a large amount of tempdb disk space.

free space

Code:
–MONITERING TEMPDB SIZE
SELECT * FROM SYS.DM_DB_FILE_SPACE_USAGE

SELECT SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS ‘FREE PAGES’
,SUM(VERSION_STORE_RESERVED_PAGE_COUNT)AS ‘ALLOCATED PAGES TO VERSION STORE’
,SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS ‘ALLOCATED PAGES TO USER OBJECTS’
,SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS ‘ALLOCATED PAGES TO INTERANL OBJECTS’

FROM  SYS.DM_DB_FILE_SPACE_USAGE

— ONE PAGE = 8KB
–ONE MB = 1024 KB

SELECT (SUM(UNALLOCATED_EXTENT_PAGE_COUNT) * 8)/1024.00 AS ‘FREE SPACE IN MB’
,(SUM(VERSION_STORE_RESERVED_PAGE_COUNT) * 8 )/1024.00 AS ‘SPACE ALLOCATED TO VERSION STORE IN MB’
,(SUM(USER_OBJECT_RESERVED_PAGE_COUNT) * 8)/1024.00 AS ‘SPACE ALLOCATED TO USER OBJECTS IN MB ‘
,(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) * 8 )/1024.00 AS ‘SPACE ALLOCATED TO INTERANL OBJECTS IN MB’

FROM  SYS.DM_DB_FILE_SPACE_USAGE

Error Is raised when
1101 or 1105 Any session must allocate space in tempdb.
3959 The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967 The version store is forced to shrink because tempdb is full.
3958 or 3966 A transaction cannot find the required version record in tempdb.

How to find out  query which utilized or  utilizing the maximum tempdb space

we will use the two other dynamic management views to answer this question

1)  sys.dm_db_session_space_usage

2) sys.dm_db_task_space_usage

Note: these views are only applicable for Tempdb

Sys.dm_db_task_space_usage  :  This views has entry for each task currently running in each session

Sys.dm_db_session_space_usage  :  This views has entry for each session currently running or completed task

code:

–SQLJUNKIESHARE
SELECT DM.SESSION_ID,SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) AS ‘ALLOCATED PAGES’,SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT) AS ‘DEALLOCATED PAGES’

, (SUM(USER_OBJECTS_ALLOC_PAGE_COUNT))/(DATEDIFF(SS,DME.START_TIME,GETDATE()))’PAGES PER SEC’, SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) /128 AS ‘SPACE IN MB’

FROM SYS.DM_DB_TASK_SPACE_USAGE  DM INNER JOIN SYS.DM_EXEC_REQUESTS DME ON DME.SESSION_ID = DM.SESSION_ID

WHERE DATEDIFF(SS,DME.START_TIME,GETDATE()) != 0

GROUP BY DM.SESSION_ID,DATEDIFF(SS,DME.START_TIME,GETDATE())

ORDER BY SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) DESC

code

SELECT SESSION_ID,SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) AS ‘ALLOCATED PAGES’,SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT) AS ‘DEALLOCATED PAGES’

FROM SYS.DM_DB_SESSION_SPACE_USAGE

GROUP BY SESSION_ID

ORDER BY SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) DESC

in order  to find the query its self just modify the query SELECT DM.SESSION_ID,SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) AS ‘ALLOCATED PAGES’,SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT) AS ‘DEALLOCATED PAGES’

, (SUM(USER_OBJECTS_ALLOC_PAGE_COUNT))/(DATEDIFF(SS,DME.START_TIME,GETDATE()))’PAGES PER SEC’, SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) /128 AS ‘SPACE IN MB’

,text

FROM SYS.DM_DB_TASK_SPACE_USAGE  DM INNER JOIN SYS.DM_EXEC_REQUESTS DME ON DME.SESSION_ID = DM.SESSION_ID

cross apply sys.dm_exec_sql_text(DME.sql_handle)

WHERE DATEDIFF(SS,DME.START_TIME,GETDATE()) != 0

GROUP BY DM.SESSION_ID,DATEDIFF(SS,DME.START_TIME,GETDATE()),text

ORDER BY SUM(USER_OBJECTS_ALLOC_PAGE_COUNT) DESC

automate the above steps by creating a job and polling those views for every three minutes or more as required and we can easily tune the tempdb

tempdb Size and Placement Recommendations

To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

  • Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.For more information, see ALTER DATABASE (Transact-SQL) or How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).
  • Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.
    Note Note
    If the production environment cannot tolerate the potential for application time-outs that may occur during autogrow operations, preallocate space to allow for the expected workload.
  • Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance. We recommend the following general guidelines for setting the FILEGROWTH increment for tempdb files.
    tempdb file size FILEGROWTH increment
    0 to 100 MB 10 MB
    100 to 200 MB 20 MB
    200 MB or more 10%*

    * You may have to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. To avoid potential latch time-outs, we recommend limiting the autogrow operation to approximately two minutes. For example, if the I/O subsystem can initialize a file at 50 MB per second, the FILEGROWTH increment should be set to a maximum of 6 GB, regardless of the tempdb file size. If possible, use instant database file initialization to improve the performance of autogrow operations.

  • Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
  • Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
  • Make each data file the same size; this allows for optimal proportional-fill performance.
  • Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
  • Put the tempdb database on disks that differ from those that are used by user databases.

Free space left in tempdb (in KB)

SELECT

                    DB_NAME(database_id)    AS          [DATABASE Name],

                    [Name]                  AS     [Logical Name],

                    [filename]              AS    [FILE Name],

                    [size_on_disk_bytes] / 1024   AS    [Total SIZE (in KB)],

                    [num_of_bytes_written] / 1024 AS    [SPACE Used (in KB)],

                    ([size_on_disk_bytes] – [num_of_bytes_written]) / 1024 AS [FREE SPACE LEFT (in KB)]

FROM sys.dm_io_virtual_file_stats(2, NULL)

                    INNER join

      sys.sysfiles

                    ON sys.dm_io_virtual_file_stats.FILE_ID = sys.sysfiles.fileid

A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.

SELECT

                    transaction_id AS [Transacton ID],

                    [name]      AS [TRANSACTION Name],

                    transaction_begin_time AS [TRANSACTION BEGIN TIME],

                    DATEDIFF(mi, transaction_begin_time, GETDATE()) AS [Elapsed TIME (in MIN)],

                    CASE transaction_type

                                         WHEN 1 THEN ‘Read/write’

                    WHEN 2 THEN ‘Read-only’

                    WHEN 3 THEN ‘System’

                    WHEN 4 THEN ‘Distributed’

                    END AS [TRANSACTION Type],

                    CASE transaction_state

                                         WHEN 0 THEN ‘The transaction has not been completely initialized yet.’

                                         WHEN 1 THEN ‘The transaction has been initialized but has not started.’

                                         WHEN 2 THEN ‘The transaction is active.’

                                         WHEN 3 THEN ‘The transaction has ended. This is used for read-only transactions.’

                                         WHEN 4 THEN ‘The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.’

                                         WHEN 5 THEN ‘The transaction is in a prepared state and waiting resolution.’

                                         WHEN 6 THEN ‘The transaction has been committed.’

                                         WHEN 7 THEN ‘The transaction is being rolled back.’

                                         WHEN 8 THEN ‘The transaction has been rolled back.’

                    END AS [TRANSACTION Description]

FROM sys.dm_tran_active_transactions

Issues with TempDB and Indexes

Besides the disk space required for the source and target structures, temporary disk space is required for sorting, unless the query optimizer finds an execution plan that does not require sorting.

If sorting is required, sorting occurs one new index at a time. For example, when you rebuild a clustered index and associated nonclustered indexes within a single statement, the indexes are sorted one after the other. Therefore, the additional temporary disk space that is required for sorting only has to be as large as the largest index in the operation. This is almost always the clustered index.

If the SORT_IN_TEMPDB option is set to ON, the largest index must fit into tempdb. Although this option increases the amount of temporary disk space that is used to create an index, it may reduce the time that is required to create an index when tempdb is on a set of disks different from the user database. For more information about sorting in tempdb, see tempdb and Index Creation.

If SORT_IN_TEMPDB is set to OFF (the default) each index, including partitioned indexes, is sorted in its destination disk space; and only the disk space for the new index structures is required.

For an example of calculating disk space, see Index Disk Space Example.

When you perform index operations online, additional temporary disk space is required.

If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks. If the SORT_IN_TEMPDB option is set to ON, this temporary index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. The temporary mapping index contains one record for each row in the table, and its contents is the union of the old and new bookmark columns, including uniqueifiers and record identifiers and including only a single copy of any column used in both bookmarks. For more information about online index operations, see Performing Index Operations Online.

Note Note
The SORT_IN_TEMPDB option cannot be set for DROP INDEX statements. The temporary mapping index is always created in the same filegroup or partition scheme as the target index.

Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. This avoids 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. For more information, see Performing Index Operations Online and Understanding Row Versioning-Based Isolation Levels.

If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index. The CREATE INDEX statement fails if there is insufficient free space and there is some reason the databases cannot autogrow to acquire more space, such as no space on the disk or autogrow is set to off.

Click here to know more about index disk space requirements

http://msdn.microsoft.com/en-us/library/ms191183.aspx

Moving  tempDB

A. Moving the tempdb database

The following example moves the tempdb data and log files to a new location as part of a planned relocation.

Note Note
Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.
  1. Determine the logical file names of the tempdb database and their current location on the disk.
    Copy
    SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
  2. Change the location of each file by using ALTER DATABASE.
    Copy
    USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
  3. Stop and restart the instance of SQL Server.
  4. Verify the file change.
    Copy
    SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
  5. Delete the tempdb.mdf and templog.ldf files from the original location.
Advertisement

One thought on “tempdb – Space Usage, Capacity planning, Performance Tuning”

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s