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 connectionUNIONs, ORDER BY, GROUP BY clauses; Internal worktables for spool and sorting.Row versioning (SI and RSCI isolation levels);
The insertedand 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 tempdbdm_db_session_space_usage – Returns the number of pages allocated and deallocated by each sessiondm_db_task_space_usage – Returns page allocation and deallocation activity by taskCombination 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.
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/
