What is TempDB database?

In SQL Server 2005, TempDB plays a very important role and some of the best practices have changed and so has the necessity to follow these best practices on a more wide scale basis. In many cases TempDB has been left to default configurations in many of our SQL Server installations. Unfortunately, these configurations are not necessarily ideal in many environments.

Let's see how TempDB can be optimized to improve the overall SQL Server performance.

Responsibilities of TempDB

Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.

Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.

Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).

DBCC CHECKDB work tables.

Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

Best practices for TempDB are:

Do not change collation from the SQL Server instance collation.

Do not change the database owner from sa.

Do not drop the TempDB database.

Do not drop the guest user from the database.

Do not change the recovery model from SIMPLE.

Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server. Keep in mind that if TempDB is not available then SQL Server cannot operate.

If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.

Size the TempDB database appropriately. For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Hope it will help develop some love for TempDB also.