TempDb and recommendations

Introduction

TempDb is an integral part of SQL Server under system databases whose architecture is designed to be shared for SQL Server instance. TempDb is related to connection and sessions and, thus, SQL Server reset TempDb when restart. That means, TempDb state is not persistent like other databases. When SQL Server restarts, TempDb is copied from model database. Technically, it never gets deleted & recreated, TempDb gets cleared and copied when instance restarts. It is important to note that when on restart TempDb data file size it reset to the initial size defined (or 8 MB, if kept default).

The tempdb is used for many SQL operations, which include creating user-defined temporary objects, internal temporary objects and version stores, and other features like online re-indexing, multiple active record sets (MARS), and others. Optimizing the temp database for the server is one of the greatest ways to increase Server performance. Keep on reading to learn some points on how to optimize tempdb in SQL Server. 

Features and Attributes of tempdb:

• • Tempdb is always set in a simple recovery mode, that means the transaction log records for committed transactions are marked for reuse after every checkpoint.

  • Tempdb can only have one filegroup, you can’t add more.

  • Tempdb can store three types of objects: user objects, internal objects, and the version store.

You can create tables in TempDb like you do in other databases and it does perform faster in TempDb because most of the internal operation doesn't generate log in TempDb as rollback is not required.  The real issue with TempDb is missing "D" from ACID property which indicates

" That the database won't be able to keep track of pending changes by which server can recover from an abnormal termination "

So, even if you use TRANSACTION and server restarts you won't be able to recover it.

TempDb is an important part of SQL Server database as it manages a temporary object which includes:

1.  Internal Objects

Objects created internally by SQL Server to process SQL queries.

• 1. Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.

  2. Work files for hash join or hash aggregate operations.

  3. 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.

  4.  Index rebuild

  5.  System Tables and Indexes

2. User Objects [Temporary]

Created by users explicitly

• 1. Temp Table [global or local]

  2. Temp variables

  3. Cursor

  4. Temp procedure

 3.  Version stores

A version store is a collection of data pages that hold the data row that are required to support features that uses row versioning.

There are two types: 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 through 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.

  1.  Row version which are generated by data modification transactions for features

     1. 1.  AFTER trigger

        2.  Multiple Active Result Sets (MARS)

        3.  Online indexes

        4.  INSTEAD OF Trigger

  2.  Row version which are generated by data modification transaction for databases

• • • 1.  Database which is using Snapshot isolation

      2.  Database which is using read-committed isolation

Note : Above specified list is a broad way specification of objects used by or in TempDb. 

Few important facts about TempDb

There are misconception about TempDb working and configuration, so below are few important point which shows the fact about TempDb

• • There can be only one TempDb per database instance.

  • TempDb is recreated every time SQL Server is started. Which means that configuration changes to TempDb needs restart.

  • You cannot backup or restore TempDb database

  • You cannot enabled Change data capture in TempDb

  • You cannot drop TempDb

  • Database owner for TempDb is sa, which cannot be changed

  • TempDb use the same database collation as your server. So, it cannot be changed

  • You cannot change the RECOVERY mode of TempDb. It will always be SIMPLE.

  • Cannot enabled auto shrink on TempDb

  • TempDb is always ONLINE, you cannot make it OFFLINE.

  • TempDb will always works on MULTI_USER mode.

  • TempDb does not support ENCRYPTION.

  • TempDb will not allow deleting primary data file or logging file.

You can check properties of database by using sys.databases catalog view; sys.databases contains one row for each database and database_id ideally represents TempDb

USE [MASTER]

SELECT * FROM sys.databases WHERE database_id = 2

Similarly, you can use sys.master_files catalog view to get the files related to TempDb database

USE [MASTER]

SELECT * FROM sys.master_files WHERE database_id = 2

With the help of below DMV, you can check how much TempDb space does your session is using. This query is quite helpful while debugging TempDb issues

SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = @@SPID

Memory = TempDb

Memory is directly or indirectly related to TempDb. As mentioned in introduction part of this article TempDb is used both for internal and user objects. Poor written query can cause high memory utilization which is one of the most common scenario while dealing with performance.

• • Statements like CREATE INDEX or CREATE STATISTICS are not run after table is created
  • Temporary object is created under UDF, Stored procedure, Trigger
  • Object is not created using dynamic SQL
  • Named constraints are not created

• • Spool - Full input row set will be stored
  • Sort - Full row set
  • Hash Join or aggregation - Hash table might store in TempDb

• • Each data file should have same initial size [ Auto growth may cause fragmentation]
  • Each data file should have same auto growth [you can skip this by setting correct initial size for each data file]
  • Number of data file should be equal to number of processors [arguable, see below discussion]

• • Calculate the largest index size on the database.
  • Monitor current space usage of log file
  • Estimated number of rows (n) written on TempDb for largest transaction per minute

• • Remove SQL Server related files from antivirus check
  • Keep TempDb files on the fast IO system; preferably RAID 0 or RAID 10
  • Avoid using drive for TempDb which is already being in use by other database files.
  • Run index management(i.e. rebuild index) and monitor TempDb usage
  • Check if SORT_IN_TEMPDB is set ON for rebuilding indexes. Default and preferred value is OFF.
  • Monitor disk Avg. Sec/Read and Avg. Sec/Write
    • • Less than 10 milliseconds (ms) = Very good
      • Between 10-20 ms = Borderline
      • Between 20-50 ms = Slow, needs attention
      • Greater than 50 ms = Serious IO bottleneck
  • Check Server level parameter Ex. MAXDOP
  • Enable TF 1118 - There is no downside effect for enabling trace flag 1118 on SQL Server version greater or equal to 2000.
  • Though, I recommend to not set auto growth for data files. But if you do so, then enable IFI (Instant File Initialization) on Windows Server to improve the performance.
  • If you have set data file to auto grow then enable trace flag 1117 to ensure uniform data grow for all data files.
  • Waits which could be related to TempDb 

• • Identify TempDb contention

• 1. Disk I/O issue, use better disk
  2. Configure multiple data files
  3. Equalize data file size

• 1. Add data files to TempDb
  2. Reuse temporary objects

• • Monitor file space usage

Sp_WhoIsActive is a nice script to check which query is consuming your TempDb space. Alternatively, below query can provide a quick glance to the SQL text causing overhead on TempDb

SELECT TST.session_id AS [Session Id],

 EST.[text] AS [SQL Query Text], [statement] = COALESCE(NULLIF(

 SUBSTRING(

 EST.[text],

 ER.statement_start_offset / 2,

 CASE WHEN ER.statement_end_offset < ER.statement_start_offset

 THEN 0

 ELSE( ER.statement_end_offset - ER.statement_start_offset ) / 2 END

 ), ''

 ), EST.[text]),

 DBT.database_transaction_log_bytes_reserved AS [DB Transaction Log byte reserved]

 , ER.Status

 ,CASE ER.TRANSACTION_ISOLATION_LEVEL

 WHEN 0 THEN 'UNSPECIFIED'

 WHEN 1 THEN 'READUNCOMITTED'

 WHEN 2 THEN 'READCOMMITTED'

 WHEN 3 THEN 'REPEATABLE'

 WHEN 4 THEN 'SERIALIZABLE'

 WHEN 5 THEN 'SNAPSHOT'

 ELSE CAST(ER.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

 END AS [Isolation Level Name],

 QP.QUERY_PLAN AS [XML Query Plan]

FROM

 sys.dm_tran_database_transactions AS DBT

 INNER JOIN sys.dm_tran_session_transactions AS TST

 ON DBT.transaction_id = TST.transaction_id

 LEFT OUTER JOIN sys.dm_exec_requests AS ER

 ON TST.session_id = ER.session_id

 OUTER APPLY sys.dm_exec_sql_text(ER.plan_handle) AS EST

 CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(ER.PLAN_HANDLE) QP

WHERE DBT.database_id = 2;

Shrinking file is not recommended as it may fragment data and slow down your server performance. Even if you try to shrink data file it will only shrink user objects. 

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 run very quickly. Caching also reduces page allocation and metadata contention.

• The 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. You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the 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 the 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 can't be modified.