SQL Server 2005 DMVs for monitoring/tuning TEMPDB

In SQL Server 2005 , TempDB plays a significant role in your overall SLQ server performance than earlier versions. In earlier version it was difficult to monitor the determine bottle neck.

In SQL Server 2005, you have number of DMVs to see which users are accessing the TEMPDB database , the internal object being used and version Stores size. The DMV are as follows

Sys.dm_db_file_space_usage
Sys.dm_db_session_file_usage
Sys.dm_db_task_space_usage

Note : In SQL Server 2005 these DMVs only applicable to Tempdb

Counters to Monitor Tempdb Row versioning usage

(a) SQL Server:Transaction Object : Version Generation Rate (KB/s)
(b) SQL Server:Transaction Object : Version cleanup Rate (KB/s)

Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx