SQL Server: TempDB Basics December 6th, 2012

Vinod Kumar

TempDB is an interesting and really important database inside SQL Server’s architecture. We hardly get a chance to look at it closely and learn from it. This database is used by all the other databases and is sometimes a unsung hero for our functioning of SQL Server. In this post, let me continue with the same logic of understanding the basics of the TempDB Database. This is not exhaustive of everything that this database is used but can be a great start. TempDB is just like any other database from an architecture perspective of pages, GAM, SGAM, PFS, Extents etc but its functioning and importance is way beyond any other system database available inside SQL Server.

  1. TempDB is a system database which is a shared database for the whole SQL Server instance.
  2. There is only one TempDB database per-instance.
  3. Re-created at startup, clone of model database, however, recovery model isn’t copied; tempdb uses simple recovery.
  4. TempDB is used for temporary user objects like local temporary tables, global temporary tables, table variables, cursors, worktables, sorts inside query, index rebuilding operation, online indexing, table valued functions etc.
  5. TempDB is also used for row versioning when used with snapshot isolation level or read-committed snapshot settings.
  6. Can’t set OFFLINE, READONLY options neither can you drop tempdb database
  7. Simple recovery model: tempdb’s log is constantly being truncated, so it can never be backed up.
  8. Operations within tempdb are minimally logged so that transactions on temporary objects can be rolled back.
  9. Highly recommend using of CHECKSUM for new installations for TempDB. This is enabled from SQL Server 2008 version.
  10. There are few restrictions for tempDB like:
  1. Additional filegroups cannot be created.
  2. We cannot drop the database or create snapshots on this database.
  3. We cannot change the collation or change the ownership of this database from dbo.
  4. The database cannot be set to OFFLINE and filegroups cannot be set to READ_ONLY.
  • Highly recommend to pre-allocate the size required for TempDB just like we discussed the same for normal databases.
  • Put tempDB into fastest IO subsystem if you see contentions for TempDB.
  • Three types of objects in tempdb
    1. User Objects – all users have privileges to create and use local and global temporary tables. sp_spaceused can show the size occupied by these objects. User objects include both user-defined tables and indexes and system catalog tables and indexes.
    2. Internal Objects – not visible via normal tools, not in catalog view as they are in-memory only. Three basic types:
      1. Work tables – created by the server when spooling to hold intermediate results during a large query, or running DBCC CHECKDB or CHECKTABLE, working with XML or varchar(MAX) variables, processing SQL Service Broker objects or working with static or keyset cursors
      2. Work files – used when the server is processing a query that uses a hash operator either for joining or aggregating data
      3. Sort units – created when a sort operation takes place ex: ORDER BY clause. SQL Server uses sorting to build indexes or to process queries involving grouping or when certain types of joins require data to be sorted before performing the join
    3. Version Store – supports technology for row-level versioning of data. Older versions of updated rows are kept in tempdb:
    1. When an AFTER trigger is fired
    2. When a DML command is executed on a database that allows snapshot transactions
    3. When multiple active result sets (MARS) are invoked from a client app
    4. During online index builds or rebuilds when there is concurrent DML on the index
  • When monitoring TempDB look for various symptoms:
    1. Disk running out of space for log files and data files. Keep them in check always.
    2. Look at allocation bottlenecks because of DML operations.
    3. Look at slow IO bottlenecks where the database files have been placed.
    4. Look at metadata structure contention because of heavy dropping and creation of temporary objects.

    Thanks for reading this far. Will love to hear your feedbacks on if you want me to cover any other basic topics like these in the future too.

    Tags: , , , , , , , ,

    This entry was posted on Thursday, December 6th, 2012 at 10:19 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


    10 Responses to “SQL Server: TempDB Basics”

    1. RaviTeja says:

      Pretty nice presentation with strong infromation… Thanks Vinod..

      Just would like to ask if the point about the effect of CHECKPOINT on TempDB can be mentioned here in brief..

    2. Ramu says:

      very nice article . I am regularly reading your blogs/articles. Thank you sir.

    3. Devi Prasad says:

      Thanks a lot for this nice aricle to community. We would like to see next about functions/system SPs those we use in our daily development cycle.

    4. Deepthi says:

      This is a very helpful information.Could you please cover COLUMNS_UPDATED() of trigger and its workaround.
      Thank you.

      • Vinod Kumar says:

        Thanks Deepthi for your comments. Well Triggers and some of the behaviours are good topic for future too. Thanks for the mention, will surely keep in mind.

      • Vinod Kumar says:

        Thanks Deepthi for your comments. Well Triggers and some of the behavior’s are good topic for future too. Thanks for the mention, will surely keep in mind.

    5. very important information!

    Leave a Reply