SQL Server Community Tools: Using sp_WhoIsActive To Track Down Implicit Transactions

In The Annals

When people talk about bad ideas in SQL Server, implicit transactions are pretty high on the list.

I’ve talked about why in the past, but up until recently sp_WhoIsActive didn’t do much to surface queries using them.

By “not much” I mean “nothing at all”. Which isn’t to knock the procedure at all; it hasn’t quite lived long enough to become a villain.

But anyway, you should avoid implicit transactions as much as possible. They most often show up in the Microsoft JDBC driver queries that only people who hate you use.

When I first started to realize how bad they are, I wrote a bunch of checks into the Blitz scripts (I’ll cover those next week) that check for them.

I also opened an issue to add the check to sp_WhoIsActive, because it’s rather popular, I hear.

In The Actions

To see when queries are using implicit transactions, you need to do this:

EXEC sp_WhoIsActive
    @get_transaction_info = 1;

Which, I mean, seems like a rational design choice. Want information about implicit transactions? Get transaction info!

In the results, you’ll see this helpful column that will tell you if something is setting the implicit transactions property to true for a session.

SQL Server Implicit Transactions
please don’t

If you see this, burn the application down. It’s time to start fresh.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

3 thoughts on “SQL Server Community Tools: Using sp_WhoIsActive To Track Down Implicit Transactions

  1. Hi Eric

    I brought your course in the past. I need help, Sir.

    The execution plan shows implicit conversation but I am not sure as all the indexes are getting scanned. could you please provide your feedback. when i run the above query EXEC sp_WhoIsActive
    @get_transaction_info = 1; i get the result as Implict_tran is OFF.


    1. Hey — implicit transactions and implicit conversions are two totally different things.

      Implicit transactions (which this post is about) are a connection property.

      Implicit conversions (which this post isn’t about) are things you see in query plans when data types for joins and where clause items don’t match.

      Hope this helps!

Comments are closed.