The TempDB in SQL Server

Every SQL Server instance has a shared database named TempDB. It is a database where temporary objects are stored and used by other databases. Because it’s only one database for the instance, it often proves to be a bottleneck. Hence, a good configuration and understanding of the TempDB becomes a required task for the DBAs.

Short for the TempDB

A global resource that is available to all users that are connected to an instance of SQL Server.

Objects&Features in TempDb:

  • Cursors;

  • DBCC CHECKDB

  • SORT_IN_TEMPDB for indexes

  • (LOB) data type variables and parameters

  • Multiple Active Result Sets (MARS): SELECT, FETCH, DML with OUTPUT and etc. Connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection

  • UNIONs, ORDER BY, GROUP BY clauses; Internal worktables for spool and sorting.

  • Row versioning (SI and RSCI isolation levels)

  • The inserted and deleted tables used in Triggers

  • Temporary tables and table variables

Dynamic system views for TempDb

  • dm_db_file_space_usage – Returns space usage information for each file in tempdb

  • dm_db_session_space_usage – Returns the number of pages allocated and deallocated by each session

  • dm_db_task_space_usage – Returns page allocation and deallocation activity by task

  • Combination with sys.dm_exec_sessions, sys.dm_exec_requests, etc., and get to the actual TSQL statement and plan responsible for these allocations.

EXECUTE sp_help #temptablename – gives various info for a temporary table.

Next table shows the differences/similarities between table variables and temp table variables.

Feature Table Variable Temp Table Note
Table Name Max 128 characters Max 116 characters
Data Storage In memory and TempDB TempDB
Meta Data In memory TempDB A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB.
Scope Current batch Current session Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP.
Constraints Allowed Allowed For table variables, since no DDL is allowed, constraints cannot be created in separate DDL statements.
DDL Not allowed Allowed. E.g. create Index on the temp table.
Concurrent Supported Supported Constraints and Indexes with explicit name in a temp table cause duplicate name error.
Statistics Not supported Supported Estimated row number in execution plan for table variable is always 1
Parallel execution plan Supported only for select Supported Parallel query execution plans are not generated for queries that modify table variables.
Transaction and Locking Not participated Participated Data in table variable is not affected if the transaction is rolled back
Cause Recompile No Yes Temp Table creation causes SPs/batches to recompile
SELECT INTO Not supported Supported
INSERT EXEC Not supported Supported
Use UDFs, Stored procedures, Triggers, Batches Stored procedures, Triggers, Batches  Temp tables can't be used in UDFs.

Some extra tips for Temp tables

  • Temp tables are also memory resistant. They’d go to disk when it becomes necessary. But it’s the same with the table variables as well.

  • What if you create a UDT in the Master database? – Available for all databases.

  • Create some very general user UDTs in the Model database.

  • I prefer temporary tables, because:

    • Table variables cause Bad cardinality = bad query plans = higher than the expected IO and CPU.

    • Cause the disk queue length (I/O) to rise.

  • Recompilation threshold is calculated as follows for temporary tables: n is the cardinality of the temporary table when the query plan is compiled.

    • If n < 6, Recompilation threshold = 6.

    • If 6 <= n <= 500, Recompilation threshold = 500.

    • If n > 500, Recompilation threshold = 500 + 0.20 * n.

  • For table variables recompilation thresholds do not exist. Therefore, recompilations do not happen because of changes in cardinalities of table variables.

Some references for the TempDb

  1. Troubleshooting Insufficient Disk Space in tempdb – https://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

  2. Optimizing tempdb configuration with SQL Server 2012 Extended Events – https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/

  3. SQL SERVER – Who is Consuming my TempDB Now – http://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

  4. If you’re on SQL Server 2016, then the following link is a must for you to read – http://blogs.sqlsentry.com/aaronbertrand/sql-server-2016-tempdb-fixes/

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.