Problem solve Get help with specific problems with your technologies, process and projects.

Tempdb changes in SQL Server 2005

SQL Server 2005 has improved tempdb, and that means faster performance for your SQL Server operations. Serdar Yegulalp shares the tempdb advances taking place behind the scenes, including changes to the way temporary tables are created, caching of worktables, the logging of record changes and also new performance counters available now.

The total number of changes to SQL Server in its 2005 edition could fill a book, and in fact has filled several such books. The most significant changes are not feature changes; They're changes in internal behavior that aren't always immediately visible to a user or administrator. That said, it's useful to know what they are and under what circumstances they express themselves.

One of the best examples of such an under-the-hood improvement is in the tempdb object — the database that SQL Server uses for temporary or on-off operations. Many of the optimizations made to tempdb are designed to speed things up, and typically remain hidden:

  • When a temporary table is created, the resulting table is now typically cached to speed up performance. Some table types are explicitly not cached, however:
    • Any temporary table with an explicit DDL created for it.
    • Any temporary table with a named constraint.
    • Any temporary table created as part of a dynamically-generated SQL statement — for instance, in the context of the sp_executeSQL stored procedure.


  • The caching of worktables has been improved. Work tables for execution plans used across multiple executions are truncated to save space; the first nine pages of the work table are kept.


  • Many other temporary object types are now cached to speed things up: table-value functions, table variables and local temporary tables are all cached when used in a stored procedure, function or trigger. The catalog entry for a given temporary object type is not immediately dropped, but cached and preserved for future reuse (with the least-used objects cycled out of that cache over time).


  • Certain changes to records in tempdb are no longer logged, as a way to reduce the amount of I/O traffic for tempdb, its log and the devices both are hosted on. INSERT actions only log what something has been changed to, and DELETE actions only log what was deleted. Only UPDATE actions log both, the original and changed data. (Previously, all three actions logged both the "before" and "after" values, as they are known in Microsoft's internal documentation.)


  • The way tempdb's files are proportionately filled out has been reworked to be less contentious with system resources. Proportionate fill means that each tempdb file (if there's more than one) is filled at the same time. So, if each physical file is on separate spindles it'll be parallelized more efficiently. It's recommended that you create one temp file per CPU (physical or virtual) for a given instance of SQL Server, and place each temp file on a separate spindle.


  • If a temporary table is dropped from tempdb, it's now handled in the background to reduce waiting by the host application; in effect, it happens instantly.

Several of the changes also affect SQL Server 2005 statistics gathering operations, not just performance:

  • SQL Server 2005's Dynamic Management Views report back statistics about tempdb's space usage, which can be retrieved with a query. For instance, SELECT SUM (unallocated_extent_page_count)*8 as [Free Space] FROM sys.dm_db_file_space_usage would return the total amount of free space in kilobytes for tempdb's files.


  • Trace flag TF-1118 (which allocates full extents to each tempdb object) has been reworked to be less contentious. You can use it for trace checking without worrying about impacting tempdb performance.


  • Two new performance counters, Temp Tables Creation Rate and Temp Tables for Destruction, are now available for SQL Server 2005. They list, respectively, how many temp tables are being created per second and how many temp tables are queued for destruction.

These changes give a programmer that much more insight into the way tempdb actually behaves, as opposed to how it ought to behave — something a creative programmer can no doubt use to write far better and more advanced SQL Server 2005 code.

Serdar Yegulalp is editor of the newsletter, Windows Insight. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2007 TechTarget

More on this topic:


Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.