Satheesh Kannan Chandra's Blog

Tempdb is the global storage location for SQL Server which can be used by all SQL users as well by SQL Server itself. It is shared by all the processes / threads in a SQL Server instance.

SQL Server users habitually use tempdb for storing temporary tables (global / local), table variables, temporary store procedures and other temporary objects. Apart from this SQL Server use tempdb internally for some of its operation. Few of them are:

-> Work tables associated with CURSORS.
-> Work tables associated with an ORDER BY clause.
-> Work tables associated with a GROUP BY clause.
-> Work files associated with HASH PLANS / spooling operations.
-> DBCC commands like DBCC CHECKDB

Facts about tempdb:

We can not drop Tempdb.
Can not backup or restore Tempdb.
Can not change the db ownership (sp_changedbowner)
Can not drop "guest" user from Tempdb.
Can not change the recovery mode from "simple"

Tempdb uses the server level collation by default. So we have to be careful about the collation settings and their impact. I have blogged some information on collation issues here.

One more important issue is when we intensively use operations which ustilises tempdb, it can lead performance bottleneck. Dont place tempdb in the slower drive. The drive speed limits the overall performance of SQL Server.

There is a known issue and fix on tempdb contention, which can be found from here.