With the expansion of the row versioning framework in SQL Server 2005 tempdb becomes an even more important part of the platform than it used to be. Snapshot transaction isolation as well as database snapshots make big use of this "temporary" storage facility and keeping track of its usage is the key to the continuous efficiency of data management solutions based on the SQL Server platform.

As mentioned in my post on SI in SQL 2005 there are several dynamic management views that can be used to monitor tempdb activity and usage, but for a more comprehensive overview of the subject I recommend this fine article by Wei Xiao, Matt Hink, Mirek Sztajno and Sunil Agarwal.

IMHO a must-read when considering the implementation of Snapshot Isolation.

And speaking of tempdb – have you read this article by Tony Rogerson?


Update (November 14th 2006)

On the SQL Server Best Practices site we can find another comprehensive study of the use of tempdb, specifically for the purposes of index creation and rebuilding:

The SQL Server Best Practices Toolbox includes two methods useful in monitoring tempdb use:


ML