tempdb

   tempdb

tempdb globally stores both internal and user objects and the temporary tables, objects, and stored procedures that are created during SQL Server operation.

There is a single tempdb for each SQL Server instance. It can be a performance and disk space bottleneck. tempdb can become overloaded in terms of space available and excessive DDL and DML operations. This can cause unrelated applications running on the server to slow down or fail.

Some of the common issues with tempdb are as follows:

·         Running out of storage space in tempdb.

 

 

·         Queries that run slowly due to the I/O bottleneck in tempdb. This is covered under I/O Bottlenecks earlier in this paper.

·         Excessive DDL operations leading to a bottleneck in the system tables.

·         Allocation contention.

Before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. It can be grouped into four main categories.

 

Category	Description
User objects	These are explicitly created by user sessions and are tracked in system catalog. They include the following: Table and index. Global temporary table (##t1) and index. Local temporary table (#t1) and index. Session scoped. Stored procedure scoped in which it was created. Table variable (@t1). Session scoped. Stored procedure scoped in which it was created.
Internal objects	These are statement scoped objects that are created and destroyed by SQL Server to process queries. These are not tracked in the system catalog. They include the following: Work file (hash join) Sort run Work table (cursor, spool and temporary large object data type (LOB) storage) As an optimization, when a work table is dropped, one IAM page and an extent is saved to be used with a new work table. There are two exceptions: The temporary LOB storage is batch scoped, and the cursor worktable is session scoped.
Version store	This is used for storing row versions. MARS, online index, triggers, and snapshot-based isolation levels are based on row versioning.
Free space	This represents the disk space that is available in tempdb.

 

The total space used by tempdb equal to the user objects plus the internal objects plus the version store plus the free space.

This free space is same as the performance counter free space in tempdb.

 

    Monitoring tempdb Space

It is better to prevent a problem than it is to work to solve it later. You can use the Free Space in tempdb (KB) performance counter to monitor the amount of space tempdb is using. This counter tracks free space in tempdb in kilobytes. Administrators can use this counter to determine whether tempdb is running low on free space.

However, identifying how the different categories, as defined earlier, are using the disk space in tempdb is a more interesting, and productive, question.

The following query returns the tempdb space used by user and by internal objects. Currently, it provides information for tempdb only.

 

Select

    SUM (user_object_reserved_page_count)*8 as user_objects_kb,

    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,

    SUM (version_store_reserved_page_count)*8  as version_store_kb,

    SUM (unallocated_extent_page_count)*8 as freespace_kb

From sys.dm_db_file_space_usage

Where database_id = 2

 

Here is one sample output (with space in KBs).

 

user_objets_kb   internal_objects_kb   version_store_kb   freespace_kb

---------------- -------------------- ------------------ ------------

8736               128                    64                    448

 

Note that these calculations don’t account for pages in mixed extents. The pages in mixed extents can be allocated to user and internal objects.