SQL Server Transactions and TEMPDB - DMV a key relationship to optimize the performance

How many times in a day you observe the TEMPDB in your SQL Server enviornment? 

How many times in a week you monitor the TEMPDB in your SQL Server environment?

Well, these are not an interview questions to ask and as you aware that because TEMPDB is so much more heavily used in SQL Server 2005 than in previous versions, you have to take much more care in managing it.  Talking about a database TEMPDB is no different to any other database, but it has an unique behaviour that its a mutual dependancy with the SQL Server instance which is recreated if you restart the SQL instance and not recovered. So this is where the attributes of TEMPDB as a workspace is refreshed which is used for temporary user objects and internal objects that are explicitly created (on fly) by SQL Server processes.

Another topic I would like to refer that logging for TEMPDB is entirely different as compared to other databases in SQL Server, some of you may assume that there is no logging in TEMPDB, which is not true! Every operation in TEMPDB is logged so that transactions on a temporary objects can be rolled back (until certain point of time). Whereby certain records in the log contain only enough information to rollback a transaction and not to recover it. Talking about objects there are 3 objects within TEMPDB, user objects, internal objects and version store. User objects can be identified by the way of using temporary tables or global temporary tables that are created by the user processes. Also its worth to mention about table variables and table-value functions, whereby the free space availability is most important and you can see the user objects by refering to the system catalog views such as sys.objects and sys.partitions along with sys.allocation_units views. Internal objects are not visible to the user or with usual management studio tool. They are not listed in the catalog views because their metadata is managed/stored in the memory.

Talking about Transactions in the subject line above, you will see that DMVs starting with DM_TRAN_* are related to transactional processing within TEMPDB and other user databases. Few changes to the transactional processing within SQL 2005 brings out the concept of version store and snapshot isolation, as they are related to each other. To refer the same to Oracle world this works as UNDO tablespace, which is a place a previous version of data is stored. Using the transaction isolation level SNAPSHOT is a click of actual situation is taken and transaction referring to read the same data is redirected to this version store. That is why it is most important factor for the DBA to keepup the size and free space availability to the TEMPDB, so you must monitor the tempdb to ensure that there is enough space there to support the transactions asking for records located in the version store. Not only that TEMPDB manages the tasks such as hash operations, eagle spool and table spool operations, temporary objects and so on (your best bet to read about these topics is to refer latest BOL).

To get a glimpse of  data for the transactions within TEMPDB execute:

select * from sys.dm_tran_active_snapshot_database_transactions

select * from sys.dm_tran_database_transactions

To get all the information regarding the user transctions and LSN information refer to sys.dm_tran_locks:

Select * from sys.dm_tran_locks;

--To display virtual table with records in version store itself, a little bit confused because the record information comes in a binary format.

Select * from sys.dm_tran_version_store;

Here is the script that I got it when dealing a problem with MS PSS team:

Select transaction_id, db_name(database_id)as database_name,
database_transaction_begin_time,
case database_transaction_type
when 1 then 'Read/Write'
when 2 then 'Read only'
when 3 then 'system transsction' end as transaction_type,
case database_transaction_state
when 1 then 'not initialized'
when 2 then 'init. but no log records'
when 3 then 'log records'
when 4 then 'prepared'
when 5 then 'committed'
when 6 then 'rolled back'
when 7 then 'being committed' end as transaction_state,
database_transaction_log_record_count,
database_transaction_log_bytes_used,
database_transaction_begin_lsn,
database_transaction_last_lsn,
database_transaction_most_recent_savepoint_lsn,
database_transaction_commit_lsn,
database_transaction_last_rollback_lsn,
database_transaction_next_undo_lsn
from sys.dm_tran_database_transactions
where transaction_id>1000;

Also the reference from Kalen Delaney's site: http://www.insidesqlserver.com/companion/SE_Chapter8code.txt & http://www.insidesqlserver.com/companion/QTO_Chapter6code.txt, too good to get you complete code snippets on finding out the locking & concurrency  and their problems.

So the bottom line is best to keepup the transactions in smaller portions that can keepup the performance of the TEMPDB and overall Applications too. The important point and outcome I get for such action is, it avoids lock contentions and deadlocks on a busy database/SQL instance.

Finally a good reference on TEMPDB basics by Sunil Agarwal here:

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions) and corresponding blog posts there, in addition to TempDB Monitoring and Troubleshooting: Allocation Bottleneck , TempDB Monitoring and Troubleshooting: IO Bottleneck and Managing TempDB in SQL Server: TempDB Configuration links.