Tuning tempdb For Better SQL Server Performance

One of the most important areas of SQL Server performance is the tempdb database. It maintains temporary user tables and intermediate query results used to prepare and optimize queries. The tempdb in essence is very similar to a user database but it is not intended to persist data. The tempdb is replaced with a copy of the model database every time SQL Server starts. The architecture of the tempdb in SQL Server 2005 did not change much from that of SQL Server 2000, however, its usage has been optimized and expanded in SQL Server 2005.

Optimizations to tempdb introduced since SQL Server 2005

SQL Server 2005 has a plethora of new features among which internal optimizations tend to be lost. These are some of the enhancements made to tempdb in SQL Server 2005:

• Internal worktables for execution plans across multiple executions are truncated down to 9 pages to save space.
• Local temporary tables and table-valued functions are all cached when used in a stored procedure, function or trigger. Items that are used frequently stay in cache while least-used items are dropped to save cache space.
• Logging in tempdb has been lowered for certain changes to reduce the amount of I/O generated. While UPDATE queries still log the original data and the new data (updated values), INSERT queries only log the new data and DELETE queries only log what was deleted.
• Physical file contention in the tempdb database has been minimized with proportionate fills. Each tempdb file is filled at the same time so each physical file on a separate spindle will benefit from parallel efficiently.
• Temporary tables that are dropped from tempdb are handled in the background to reduce waiting by the host application.

Monitoring the tempdb database using performance counters

SQL Server 2005 provides a number of performance counters that can be used to monitor space usage in tempdb.

• The Database: Log File(s) Size(KB) performance counter returns the cumulative size of all the log files in the database. This size can grow if you have not set a maximum size for the log in tempdb.
• The Database: Log File(s) Used (KB) performance counter returns the cumulative used size of all log files in the database. A large active portion of the log in tempdb can be a warning sign that a long transaction that is preventing log cleanup.
• The Free Space in tempdb (KB) (SQL Server 2005) performance counter tracks free space in tempdb in kilobytes. Administrators use this counter to determine if tempdb is running low on free space so they can take appropriate corrective action. This counter accounts for space allocated by all three types of objects in tempdb.
• The Version Store Size (KB) (SQL Server 2005) performance counter monitors the size in KB in both version stores. If a version store is not shrinking, it implies that a long-running transaction is preventing version store cleanup.
• The Version Generation Rate (KB/s) (SQL Server 2005) performance counter monitors the version generation rate in kilobytes per second (KBps) in both version stores.
• The Version Cleanup Rate (KB/s) (SQL Server 2005) performance counter monitors the version cleanup rate in KBps in all version stores. If the version cleanup rate is lower than the version generation rate, the version store will use more and more space in tempdb. However, if the version cleanup rate is 0 but the version generation rate is not, there is probably a long-running transaction that is preventing the version store cleanup.

Monitoring the tempdb database using dynamic management views

SQL Server 2005 provides a number of dynamic management views (DMVs) that can be used to monitor space usage in tempdb. The DMVs provide rich features 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).

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.

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.

A higher % allocation for the version store implies that version store cleanup cannot keep pace with version generation. See if a long-running transaction is preventing version store cleanup. Or, a high transaction throughput might be generating a large number of versions per minute. The background task cleans up versions every minute.

sys.dm_db_session_file_usage

This DMV tracks the historical allocation/deallocation of pages in tempdb for the active sessions. A session is established when a user connects to the database. The session is active until the connection is terminated. During the course of the session, the user submits one or more batches. This DMV tracks the tempdb space usage only by the completed batches. The following code example shows the top five sessions that have allocated a maximum space for user objects and internal objects in tempdb. This represents the batches that have already completed, but the code lists sessions with heavy tempdb space use. You could look at these first if you want to minimize tempdb consumption. Note that this tempdb space usage does not take into account the impact of this session on the version store space.

SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

sys.dm_db_task_space_usage

This DMV tracks the allocation/deallocation of tempdb pages by the currently executing tasks (also called batches). This is extremely useful when you are running out of space in tempdb. Using this DMV, you can identify tasks with heavy tempdb space use and optionally kill them. You can then analyze why these tasks require heavy tempdb space usage and take corrective action. You can join this DMV with other DMVs to identify the SQL statement and its corresponding query plan for deeper analysis. The following query shows the top five tasks that are currently executing tasks and consuming the most tempdb space. The tempdb space usage returned does not allow for the impact on space consumed by the version store.

SELECT top 5 *
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC