amachanic / sp_whoisactive

sp_whoisactive
GNU General Public License v3.0
1.17k stars 289 forks source link

Add implicit transaction column to output #41

Closed erikdarlingdata closed 2 years ago

erikdarlingdata commented 3 years ago

On servers using the JDBC driver and other smelly unfortunates, implicit transactions can be quite an annoying problem. I wrote code into sp_BlitzWho to identify them, and I'd love to get it in sp_WhoIsActive so I don't need to use another proc for it.

It looks something like this:

SELECT
    des.session_id,
    CASE 
        WHEN 
            EXISTS 
            (  
                SELECT 
                    1/0 
                FROM sys.dm_tran_active_transactions AS tat
                JOIN sys.dm_tran_session_transactions AS tst
                    ON tst.transaction_id = tat.transaction_id
                WHERE tat.name = 'implicit_transaction'
                AND des.session_id = tst.session_id 
            )  THEN 1 
        ELSE 0
    END
FROM sys.dm_exec_sessions AS des;
amachanic commented 3 years ago

Definitely. Maybe as part of the @get_transaction_info output?

erikdarlingdata commented 3 years ago

Sure, wherever you think it makes sense.

amachanic commented 3 years ago

What do you think makes sense, Darling?

erikdarlingdata commented 3 years ago

Oh, you know, I think it would make more sense under @get_additional_info = 1, since there's other details about blocking/transactions in there, and implicit transactions are technically an ansi default setting

amachanic commented 3 years ago

Hm, really annoying that we can't harvest that information from the sessions/requests DMVs. I'm pretty sure that as far as the proc is currently concerned, the transaction DMVs are only touched when @get_transaction_info is enabled. Maybe there's a nice way to combine the two?

erikdarlingdata commented 3 years ago

You mean if someone reads the documentation and uses both transaction info and additional info? 😂

amachanic commented 3 years ago

I would never, ever imply that someone will read the documentation.

erikdarlingdata commented 3 years ago

That's a relief, I thought he Postgres might be going to your head.