Tuning the tempdb System Database
The tempdb system database plays a particularly important role in SQL Server 2005, which uses this temporary workspace for operations involving temporary tables, table variables, cusrors, hash-joins, and row versioning. Although you might not be using any of these explicitly, your SQL Server instance might be implicity using tempdb for operations such as for DML after triggers, Multiple Active Result Sets (MARS), and online index operations, all of which use row-versioning in the background. DBCC checks also use row-versioning as a means of checking a consistent state of the tables or indexes.
Consequently, it is important to optimize performance of your tempdb system database, especially for an intensive OLTP ...
Get Microsoft® SQL Server™ 2005 Administrator's Companion now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
