Tempdb is a very important SQL Server system database. DBA should pay very meticulous attention to tempdb settings and carefully monitor usage of this db. Overall system performance strongly depends on the way tempdb is configured.

Tempdb is recreated each time sql server restarts which means any data present in tempdb is lost after sql server restart. Tempdb recovery model is simple and you can’t change it. Tempdb is prohibited from creation of user defined filegroups but you may create as many files in existing filegroups as you need. Tempdb operations are minimaly logged and because tempdb is recreated after sql server restart there is no need to REDO tempdb operations so transaction log does not need to be flushed after commit.

Usage of tempdb

 Tempdb is used by internal and user objects. Starting from sql server 2005 it is also used as version store.

Internal objects are created by sql server itself based on execution plan to store intemediate results, cursors etc. User objects are temporary tables and table variables. Indexes for temporary tables are also stored in tempdb. If you intensively use tempdb for user objects of large size it is recommended you create indexes for temporary tables. Temporary tables support syntax of create index operator, however table variables support only one index which can be specified for primary key in declare table statement.

Intenal objects are also called worktables or work tables. So worktables are temporary internal objects that are used to store results for query spool, LOB variables, and cursors.You can find distribution of tempdb space usage by session using DMV sys.dm_db_session_space_usage. Space used by worktables query will look like

select *

from sys.dm_db_session_space_usage

 

This query can be filtered by session_id which is actually the spid of a process. See msdn article for more info.

 There is a great article about How to shrink tempdb. But the bad news is that in most cases the only way to reduce space used by tempdb is to restart the sql server service because dbcc shrinkfile and dbcc shrinkdatabase will not result in any luck.

For more information visit tempdb database and working with tempdb in sql server 2005 link.

MS SQL Server 2008 tempdb

In Microsoft SQL Server 2008 you should also consider encryption of tempdb. Tempdb gets encrypted by TDE (Transparent Data Encryption) if any of user databases becomes encrypted. You can't manually manage encryption status of tempdb so take into account if you have sql hotel of 50 databases and one of your databases is encrypted then all other 49 databases will use encrypted tempdb. This fact may have really appreciable impact on your overall system performance.You can see whether databases are encrypted using query

select

    DB_NAME(database_id)

   ,case encryption_state

        when 0 then 'No encryption, no key'

        when 1 then 'Unencrypted'

        when 2 then 'Encryption in progress'

        when 3 then 'Encrypted'

        when 4 then 'Key change in progress'

        when 5 then 'Decryption in progress'

     end as TDE_status

from sys.dm_database_encryption_keys

  For more information about TDE see also Database Encryption in SQL Server 2008 Enterprise Edition

 Considerations for tempdb configuration

 Even though tempdb is recreated each time sql server is started the good thing is that it remembers files settings. So you don't need to change model db file setting (any other settings tempdb inherits from model database). You should set initial sizes for data and log file of tempdb large enough to avoid file growths. Default tempdb file settings for production databases are inadmissible. You will be getting error

Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

which root cause is that tempdb is filled on a rate much bigger than it can grow. For instance your tempdb size is 3 MB and autogrowth is set to 10%. If you run a heavy query with joins and orders tempdb will get full very soon and it will need to grow. So first growth will be only 300 KB but there might be much more in the buffer so your join will fail with the error mentioned above.

How to define the size of tempdb? For production databases it is quite easy just set initial file sizes to be equal to current sizes (if server uptime is long enough – 2-3 weeks). For databases under development or testing see Capacity planning for tempdb.

There is a general guideline to have number of tempdb data files equal to the total number of processors  (logical processors). These files should be configured to have the same initial size. This trick should reduce contenction on GAM and SGAM pages.

Tempdb should be placed on a separate disk array, preferably RAID 1 or RAID 10. This can also be RAID 0 if system requires more performance than fault tolerance (if this RAID 0 fails you may recover sql server). RAID 5 is the worst choice for tempdb since this database is very write intensive.

To move tempdb from default master device you can use SMSS (tempdb properties) or ALTER DATABASE statement. For more information see How to move tempdb to different location.