Question:

What is TempDB?

Answer:

This is a short blog post on TempDB.  TempDB is essentially a scratch database for SQL Server.  It’s also one of, if not the, busiest databases on an instance.  Learning what it does, and does not do, is important both for the DBA, but also for the developer.  Setting up TempDB is also an important task – sizing, creating multiple TempDB files, and configuring autogrowth correctly will help make your DBA life simpler.

I’ll start off with a list of facts on TempDB, followed by some DMV’s to look at TempDB useage and performance, and finally a word on troubleshooting.

Some TempDB facts:

  1. Only one TempDB file can be present on an instance
  2. Essentially the scratch database for SQL Server
  3. TempDB contains a data file and a log file
  4. You cannot backup TempDB
  5. TempDB uses Simple recovery model
  6. TempDB is cleared upon instance restart
  7. TempDB uses the Primary file group, you cannot add other file groups
  8. You can have multiple TempDB files within the filegroup
  9. Multiple TempDB datafiles should be sized the same, and their Autogrowth size should be the same. Don’t use percentages for autogrowth
  10. Don’t accept the default AutoGrowth configuration for TempDB
  11. TempDB holds three(3) things: user data, internal data, and the Version Store
  12. TempDB doesn’t log redo information, it does still log undo information. This allows better performance when performing large changes vs. a user database
  13. Table Variables: defined with the “at” sign – @
    • Declare @TempVar Table …
    • Not created “in memory” they live in TempDB, just like temporary tables
    • Cannot be assigned indexes, but you can assign constraints; including primary key or a unique constraint
    • Table Vars do not use statistics, even if a key or constraint is assigned. They use a set value (1) for cardinality
  14. Temporary Tables: defined with a hashtag – “#”, global Temp Tables are defined using a double hashtag – “##”
    • Create Table #TempTable (or ##TempTable) …
    • Unlike Table Variables, Temp Tables contain statistics
    • They can be indexed (clustered, non-clustered)
    • They can be defined as global access, where any session connected to the database can access them
    • Temporary tables are fully transactional aware, table variables are not
    • Temp Tables are limited in scope to the session that created them, once the session is gone, the temp table is dropped

# 3 & # 8.  TempDB contains a data file and a log file:

TempDB_Files_SSMS

# 4.  You cannot backup TempDB:

Backup_TempDB_SSMS

# 5.  TempDB uses Simple recovery model:

TempDB_SimpleRecoveryMode

# 6.  TempDB clears upon SQL Server restart:

Before:

TempDB_TemporaryTables_Before

After:

TempDB_TemporaryTables_After

# 7.  TempDB uses the Primary filegroup, you cannot add additional filegroups:

Secodary_FG

# 13 (d).  Table Variables do not contain statistics:

TableVar_Cardinality

# 14 (b).  Temporary Tables contains statistics:

TempTable_Cardinality

# 14 (c).  Temporary Tables can be indexed (Clustered and Non-clustered):

TempTable_Index

One additional item worth mentioning is the Version Store.  TempDB is used for Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation where the row version is stored.  You can read more about TempDB and the version store here:

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2008/12/30/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example/

Impacts on TempDB

Since each data file has an associated meta-data file, whenever requests come in to TempDB the meta-data file has to be updated.  This process is single threaded and can cause contention when many requests are pending.  To enforce data integrity, SQL Server uses a lightweight lock called a latch.  This latch makes sure only one request thread is accessing the page at a time.  Because of this, multiple requests can be queued waiting for their turn to update the data page.

Using more than one TempDB datafile helps reduce this contention because there is another file available along with it’s associated meta-data file.  While the process to update the meta-data file is still single threaded, the presence of two, or more, datafiles spreads out the requests.

You can view wait contention against TempDB using the DMV:  sys.dm_os_waiting_tasks, the wait types we are concerned with are:  PAGELATCH_EX, and PAGELATCH_UP.  You can dive further into the process of waits by using the DMV:  sys.dm_os_wait_stats.

Sizing the datafiles of TempDB the same.  TempDB uses a proportional fill algorithm to populate datafiles equally.  Sizing the datafiles equally allows this algorithm to distribute data properly.

Large datafiles may impede sql server start up when a shutdown has occurred.  Using IFI (Instant File Initialization) can help this process.   Instant File Initialization is set up at the server level.

Memory Spills to TempDB

There are a few ways that memory requests can “spill” over into TempDB.  One way is due to a cardinality mismatch between the table and what the optimizer sees.  Memory grants are created by the optimizer based upon the cardinality (rows that satisfy the query).  This is directly related to the presence of up-to-date statistics.  If the optimizer (cardinality estimator) uses the wrong value for cardinality, then it may not grant enough memory to perform the work.  When this happens, it spills to TempDB for more memory.  Since reading from disk vs. RAM is far slower, this can become an issue depending on the query and execution frequency.

Best Practices

  1. Place TempDB on either a fast disk, RAID, or SSD. This should be an autonomous device, containing only TempDB
  2. Use multiple data files within TempDB
  3. Size the data files the same, along with their associated autogrowth values
  4. Don’t use percentages in data file autogrowth
  5. Configure Instant File Initialization (IFI) – for data files, log files cannot use IFI
  6. Watch for latch contention with DMV’s:
    • dm_os_waiting_tasks
    • dm_os_wait_stats
  7. Latch types:
    • PAGELATCH_EX
    • PAGELATCH_UP
  8. Use Table Variables and Temporary Tables sparingly
  9. Table Variables do not use statistics, wereas Temporary Tables do. This can have a direct impact on performance
  10. Keep statistics current
  11. Eliminate unnecessary sorting operations – large sorts might spill into TempDB

Summary:

In this post I’ve explained what you can, and cannot, do when setting up and maintaining TempDB.  I’ve also shown some best practices and described temp variables and temp tables.  TempDB is being used more and more by the SQL Server OS to perform many functions.  Therefore, setting up TempDB correctly, on the correct drive type, can dramatically increase its performance.

Resources:

Books:

Microsoft SQL Server 2014:  Query Tuning & Optimization, Benjamin Nevarez, McGraw-Hill, 2015.

SQL Server DMVs In Action, Ian W. Stirk, Manning Publications Company, 2011.

MSDN:

Best Practices:

https://blogs.msdn.microsoft.com/cindygross/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices/

General:

https://msdn.microsoft.com/en-us/library/ms190768(v=sql.120).aspx