Analysis of tempdb changes in SQL Server 2005

The tempdb database is a database that SQL Server uses for temporary or switch operations. Many of the optimizations for tempdb are transparent and allow processing to accelerate, and this article describes the effects of tempdb on SQL Server 2005 to help you use these to write better, more advanced SQL Server 2005 code.

All changes in the SQL Server 2005 version can be written as a book, in fact, several books have been written. Among them, the most important change is not functional change, these changes occur in the user or the administrator can not immediately feel the internal behavior. That said, it would be useful to know what they are and how they will behave in a situation.

One of the best examples of these hidden performance improvements is the database used by the tempdb database--sql server for temporary or switch operations. Many of the optimizations that are made to tempdb are to allow processing to accelerate in a transparent situation:

Temporary tables are cached to accelerate performance when temporary tables are created. However, there are some tables that are explicitly not cached:

Temporary table associated with an explicit DDL

Temporary table with named constraint associated with

A temporary table as part of a dynamically generated SQL statement-for example, a stored procedure sp_executesql environment

The cache for the worksheet has been upgraded. To conserve space, the worksheets in the execution plan that are executed repeatedly are truncated; only the first nine pages of the worksheet are now reserved.

Many other types of temporary objects are also cached to improve speed: table-valued functions, table variables, and local temporary tables are cached when used in stored procedures, functions, or triggers. The catalog entries for a particular temporary object are not discarded immediately, but are cached for future reuse (after a while, the least used objects will be purged from the cache).

Some of the changes recorded in tempdb will no longer be recorded in the log, which can reduce the I/O traffic to the logs and devices generated by tempdb. The insert operation only records the inserted record, and the delete action only records the deleted record. Only the update operation will record both the original value and the updated value. (according to Microsoft's internal data, the previous version, three operations will record the value before the operation and the value after the operation)

Redesign tempdb files are balanced written in a way that reduces competition for system resources. A balanced write means that each tempdb file (assuming there are multiple files) is written at the same time. Therefore, if each physical file is dispersed to a different head, then writing to those files can be more efficient in parallel processing. It is recommended that you create a temporary file on each CPU for a SQL Server and place each temporary file on a different head.

If a temporary table is discarded from tempdb, it is processed in the background to reduce the waiting time for the host program. In fact, it will be dealt with immediately.

Not only performance, but some changes also affect the statistical collection operations of SQL Server 2005:

Dynamic Management views of SQL Server 2005 report statistical data about the tempdb space usage, which can be retrieved from a query. For example, SELECT SUM (Unallocated_extent_page_count) *8 as [free spaces] from Sys.dm_db_file_space_ Usage returns the total amount of free space calculated in kilobytes in the tempdb file.

The trace flag TF-1118 (representing the allocation of the entire extents to each tempdb object) has been rebuilt to reduce resource competition. You can use it for trace checking without worrying about the effects on tempdb performance.

Two new performance metrics, temporary table new rate (temp tables creation Rate) and temporary table destroy count (temp tables for destruction), appearing in SQL Server 2005. They indicate how many new temporary tables were created per second and how many temporary tables were queued for destruction.

These changes can give programmers some insight into how tempdb actually works, rather than being blinded by its apparent behavior--an innovative programmer can, no doubt, use it to write better, more advanced SQL Server 2005 code.