 |
|
| SQL Server Tips: |
|
|
|
DATABASE ADMINISTRATOR
Tempdb changes in SQL Server 2005
By Serdar Yegulalp
03.15.2007
Rating: -4.50- (out of 5)
|
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.
| ABOUT THE AUTHOR: |
|
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:
Configure SQL Server 2005 TempDB for performance
Working with multiple collation types
SQL Server 2005 performance tuning tools A-Z
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
