concept of TEMPDB in SQL SERVER

concept of TEMPDB in SQL SERVER

The SQL Server system database, tempdb, has undergone a number of changes in SQL Server 2005. There are new tempdb usages and internal optimizations in SQL Server 2005; tempdb architecture is mostly unchanged since SQL Server 2000.

The tempdb system database is very similar to a user database. The main difference is that data in tempdb does not persist after SQL Server shuts down.

Each time SQL Server restarts, tempdb is copied from the model database. It inherits certain database configuration options, such as ALLOW_SNAPSHOT_ISOLATION, from the model database.

Only one file group in tempdb is allowed for data and one file group for logs. You can configure the size of the files. When auto grow is enabled (which is the default), the file grows until the disk volume is full. When the server restarts, the tempdb file size is reset to the configured value (the default is 8 MB). Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts.

Users can explicitly create and use tables in tempdb. Transactions can be used to modify data in the tables. Transactions can be rolled back. However, there is no need to REDO them because the contents of tempdb do not persist across SQL Server restarts. Because the transaction log does not need to be flushed, transactions are committed faster in tempdb than in user databases. In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute.

SQL Server uses tempdb to store internal objects such as the intermediate results of a query. Most of these internal operations on tempdb do not generate log records because there is no need to roll back. So, these operations are faster.

There are some feature restrictions in tempdb. In addition, some of the database options cannot be modified for tempdb. For a list of these options, see tempdb Database in SQL Server 2005 Books Online.

Auto shrink is not allowed for tempdb. Database shrink and file shrink capabilities are limited. This is because many of the hidden objects that SQL Server stores in tempdb cannot be moved by shrink operations.

The following restrictions also apply to tempdb:

·         The database CHECKSUM option cannot be enabled.

·         A database snapshot cannot be created on tempdb.

·         DBCC CHECKALLOC and DBCC CHECKCATALOG are not supported.

·         Only offline checking for DBCC CHECKTABLE is performed. This means that a TAB-S lock is needed. There are internal consistency checks that occur when tempdb is in use. If these checks fail, the user connection is broken and the tempdb space used by the connection is freed.

tempdb Space Usage

The following types of objects can occupy tempdb space:

·         Internal objects

·         Version stores

·         User objects

Monitoring and Troubleshooting

The tempdb shared resource must be managed well for the smooth functioning of SQL Server. A rogue query or a poorly designed application can potentially consume all tempdb resources, thereby rendering a SQL Server instance unusable.

Administrators work with the following types of resources in tempdb:

·         Space. This refers to both used and unused space in data and log files.

·         I/O bottlenecks. 

·         Contention in DML operations. This relates to page and extent allocations and deallocations for all type of objects in tempdb.

·         Contention in DDL operations. This relates to the manipulation of metadata when user objects in tempdb are created or dropped. Note that internal objects are not reflected in metadata tables

One of challenges in managing tempdb is that there is no way to partition its resources based on user databases, applications, or user sessions. You cannot even partition resources based on the category of objects (such as version stores) that are stored. It seems useful to partition tempdb resources to isolate applications from one another but then the burden is on the applications or on the administrators to partition these resources in such a way so as to minimize waste. For example, if you put an upper bound on the tempdb resources that can be consumed by an application, you may be forced to abort one or more transactions even if those resources were not being used by any other applications or queries.

Dynamic Management Views

SQL Server 2005 provides a rich set of DMVs to track tempdb space usage. You can track tempdb space usage at the instance level, the session level, or by individual task (a task is also known as a batch) by using the following DMVs.

Sys.dm_db_file_space_usage

This DMV returns space allocation information for the files associated with tempdb. The allocation information is grouped by object category (user, internal, and version store) for the instance. The following code is an example.

SELECT

SUM (user_object_reserved_page_count)*8 as usr_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8  as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

The following sample output was generated when a query was executed with a hash-join that created a work table.

usr_obj_kb internal_obj_kb version_store_kb freespace_kb mixedextent_kb

8192            4608                 0                3840        1024

You can use the output to learn how tempdb space is being used. The following points will help you analyze the output.

·         A higher % allocation for user objects implies that objects that are created by applications (for example, global and local temporary tables and variables) are the major consumers of tempdb. This is not necessarily a cause of concern.

·         A higher % allocation for internal objects implies that the query plans make heavy use of tempdb. This is not necessarily a problem, but you may want to look at the query plans to see if alternate query plans can be generated by creating indexes or by re-formulating the queries so as to minimize tempdb space usage.