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.

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 *

*