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 | 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
Troubleshooting Insufficient Disk Space in tempdb – https://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
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/
SQL SERVER – Who is Consuming my TempDB Now – http://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/
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/