DMVs to provide state of TEMPDB during a performance tuning analysis

TEMPDB - though the name sounds like temporary database, it most important for any user database!

Coming to performance tuning exercise or analysis you would go through Task Manager initially to see how system's  memory &  processor are coping up. Though they give initial picture of usage it is always better to go through by SYSMON counters, so to be specific about application database performance refer to SQL Server performance counters that will provide information about the system performance impacted by SQL Server processes specifically. As per the subject of this blog, the issue with TEMPDB means looking at the performance counters related to tempdb will help assessing the issue and to be specific counters are contained in the SQLServer:Transactions performance object. As per the BOL notes the amount of Free Space in tempdb is estimated using the size of the version store. For long-running transactions, it may be useful to monitor the generation and cleanup rate to estimate the maximum size of the version store:

[size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

You  may be aware REINDEX operations and many other processes are totally dependant on TEMPDB within SQL 2005 verson, so for instance if a longest running time of transactions held up in the TEMPDB and online index builds kicks in means TEMPDB will be stuffed out completely. Because nature of these operations may take a long time on very large tables, online index builds use a separate version store. The approximate size of the online index build version store equals the amount of data modified in the table, including all indexes, while the online index build is active.

So to dig out further using or taking help of DMVs the following will provide information about the current system state of tempdb and the version store, as well as transactions using row versioning (for me helped to assess a performance problem that is hammering TEMPDB even for smaller query results):

sys.dm_db_file_space_usage - Returns space usage information for each file in the database.

sys.dm_db_session_space_usage - Returns page allocation and deallocation activity by session for the database.

sys.dm_db_task_space_usage - Returns page allocation and deallocation activity by task for the database.

sys.dm_tran_top_version_generators - Returns a virtual table for the objects producing the most versions in the version store. It groups the top 256 aggregated record lengths by database_id and rowset_id. Use this function to find the largest consumers of the version store.

sys.dm_tran_version_store - Returns a virtual table that displays all version records in the common version store.