Sql-server – Why is there a difference between these transaction DMVs in SQL Server 2008 R2

dmvsql serversql-server-2008-r2

When I execute the two queries below, 

SELECT 
    session_id, transaction_id
    FROM sys.dm_tran_session_transactions;

AND

SELECT 
    session_id, request_id, at.transaction_id
    FROM sys.dm_tran_active_transactions at
        JOIN sys.dm_exec_requests r
            ON r.transaction_id = at.transaction_id;

I've read the BOL for both 1 and 2 but don't see any clear explanation as to why the difference would occur.

I get different results. The former query returns no results, but the latter returns active transactions with session and transaction ids. The request_id is 0 which, I think, means that it's the only request made by the session. Could someone help me understand why there is a difference between the two concepts I've queried above?

EDIT

I just reran the queries and now I get a result for the first DMV which has a session_id that is not actually contained in the second result set.

Best Answer

It appears that sys.dm_tran_session_transactions does not include auto-commit transactions:

-- No result
SELECT 
    session_id, 
    transaction_id
FROM sys.dm_tran_session_transactions;

Using an explicit transaction does return results:

BEGIN TRANSACTION;

    -- Row returned    
    SELECT 
        session_id, 
        transaction_id
    FROM sys.dm_tran_session_transactions;

ROLLBACK TRANSACTION;

The DMVs are views on internal structures, and the documentation is not often as comprehensive as in other areas. Some of that may be because it would be inconvenient to go through a complete deprecation cycle each time a change in DMV behaviour occurs, but it's likely just an oversight in this case. You could report the documentation deficiency on Connect.

Adam Machanic found all sorts of odd little behaviours with the DMVs while writing his sp_WhoIsActive tool. If it suits your purposes, you could use that rather than trying to write your own monitoring queries.

Related Question