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.

FeatureTable VariableTemp TableNote
Table NameMax 128 charactersMax 116 characters
Data StorageIn memory and TempDBTempDB
Meta DataIn memoryTempDBA 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.
ScopeCurrent batchCurrent sessionTemp 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.
ConstraintsAllowedAllowedFor table variables, since no DDL is allowed, constraints cannot be created in separate DDL statements.
DDLNot allowedAllowed.E.g. create Index on the temp table.
ConcurrentSupportedSupportedConstraints and Indexes with explicit name in a temp table cause duplicate name error.
StatisticsNot supportedSupportedEstimated row number in execution plan for table variable is always 1
Parallel execution planSupported only for selectSupportedParallel query execution plans are not generated for queries that modify table variables.
Transaction and LockingNot participatedParticipatedData in table variable is not affected if the transaction is rolled back
Cause RecompileNoYesTemp Table creation causes SPs/batches to recompile
SELECT INTO Not supportedSupported
INSERT EXECNot supportedSupported
UseUDFs, Stored procedures, Triggers, BatchesStored 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/

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Reply

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

*