Tempdb Database

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server or connected to SQL Database. Tempdb is used to hold:

Temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.

Internal objects that are created by the database engine. These include:
o    Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
o    Work files for hash join or hash aggregate operations.
o    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; an IAM page and an eight-page extent. For more information about pages and extents.

Version stores, which are 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:
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 so that transactions can 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.

Physical Properties of tempdb in SQL Server
The following table lists the initial configuration values of the tempdb data and log files in SQL Server, which are based on the defaults for the Model database. The sizes of these files may vary slightly for different editions of SQL Server.
Logical name
Physical name
Initial size
File growth
Primary data
8 megabytes
Autogrow by 64 MB until the disk is full
Secondary data files*
8 megabytes
Autogrow by 64 MB until the disk is full
8 megabytes
Autogrow by 64 megabytes to a maximum of 2 terabytes
* The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

The following operations cannot be performed on the tempdb database:

Adding filegroups
Backing up or restoring the database
Changing collation. The default collation is the server collation
Changing the database owner. tempdb is owned by sa
Creating a database snapshot
Dropping the database
Dropping the guest user from the database
Enabling change data capture
Participating in database mirroring
Removing the primary filegroup, primary data file, or log file
Renaming the database or primary filegroup
Setting the database to OFFLINE
Setting the database or primary filegroup to READ_ONLY

Any user can create temporary objects in tempdb. Users can only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but is not recommended as some routine operations require the use of tempdb.

Optimizing tempdb performance in SQL Server

§The size and physical placement of the tempdb database can affect the performance of a system. 
§For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with auto growing tempdb to the size required to support the workload every time you restart the instance of SQL Server.
§  If possible, use database instant file initialization to improve the performance of data file grow operations.
§  Always give the tempdb a sufficient capacity. Leave it to grow on automatic. 
§  Set the recovery mode to a Simple. Not everything is recorded in the transaction log, means when a transaction completed it is removed from the transaction log. Therefore the log file does not continue to grow unnecessarily.
§  The creation of additional data files can optimize to get access to the data carrier, as this storage conflicts can be avoided. There is a pi * thumb rule: number of CPUs equals the number of files. This improves the access to the data carrier.
§  Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. Preallocation prevents tempdb from expanding too frequently, which affects performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
§  Data files should be of equal size within each filegroup, as SQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. Dividing tempdb into multiple data files of equal size provides a high degree of parallel efficiency in operations that use tempdb.
§  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 and affect performance.

To check current tempdb size and growth parameters, use the following query:

 SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file grows to a maximum size of 2 TB.'
    growth AS 'GrowthValue',
    'GrowthIncrement' =
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
FROM tempdb.sys.database_files;

Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks. Individual or groups of tempdb data files do not necessarily need to be on different disks or spindles unless you are also encountering I/O bottlenecks.
Put the tempdb database on disks that differ from those that are used by user databases.

Performance improvements in tempdb for SQL Server
Starting with SQL Server 2016 (13.x), tempdb performance is further optimized in the following ways:
§  Temporary tables and table variables are cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
§  Allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
§  Logging overhead for tempdb is reduced to reduce disk I/O bandwidth consumption on the tempdb log file.
§  Setup adds multiple tempdb data files during a new instance installation. This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command-line parameter /SQLTEMPDBFILECOUNT. By default, setup adds as many tempdb data files as the logical processor count or eight, whichever is lower.
§  When there are multiple tempdb data files, all files autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.
§  All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.
§  For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

Troubleshooting Insufficient Disk Space in tempdb

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.

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.

The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

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

tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.
Determining the Amount of Free Space in tempdb
The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

One of the Resolution for TempDB issue:
I gone into an interesting situation today where the tempdb was full and only had 10 MB left on the respective drive.  Even though there was more than 90% space available, I was not able to reduce the size by running the shrink command. Then the below steps I tried and each of them ran successfully but couldn’t really shrink the database.

Here are the serial lists of things performed.  Please use them under circumstantial situations as this is not a solution that may be applicable each time.

When DBCC ShrinkDB or shrinkFile doesn’t work.. Try these steps.









5.DBCC SHRINKFILE (N'datafilename' , 0)


do ShrinkDB or shrinkFile .

If you still see issues, increase the size of tempdb by 1MB from its existing size. For example, if the current tempdb size is 10000 MB, you increase it to 10001MB

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10001MB )

After the above command, Try to rerun the steps 1 till 5 again and shrink the DB.

No comments:

Post a Comment

Popular Posts