sp_WhoIsActive: Tracking Down Implicit Transactions
Video Summary
In this video, I delve into the intricacies of implicit transactions in SQL Server using SP who is active as a tool to track down these pesky issues. As usual, I begin by explaining why Microsoft hasn’t provided a straightforward way to monitor implicit transactions and how they can cause unexpected behavior, especially with drivers like JDBC and Python. I then demonstrate how SP who is active can be enhanced with specific parameters to reveal more detailed information about queries within implicit transactions. However, even with these enhancements, the tool still falls short in fully capturing the complexity of such transactions. Throughout the video, I illustrate this through practical examples, showing that while SP who is active is a valuable resource, it isn’t sufficient on its own for comprehensive transaction tracking. The takeaway? Implicit transactions are best avoided unless you have a deep understanding of their implications and can manage them carefully.
Full Transcript
Erik Darling here with Darling Data, as usual. Forever, forever and so forth. So be it as it may. And in this video, I’m going to show you how you can tell with using SP who is active. Still waiting for Microsoft to write SP whom is active or something. To track down if queries are using implicit transactions. Now I have another video fairly recently about just how bad implicit transactions can be. Should probably watch that. Promise it’s a barn burner, as they say. But you see them a lot from the JDBC driver, you see them a lot from the Python driver, and they can come and cause some real weirdness in a lot of ways. Now, SP who is active is no shortcut around that weirdness. And I’ll show you why and how wow that is true. So if I run this now, again, we’re going to have my noisy CDC job in the background because I am working on CDC stuff for a client locally. Figure out some ins and outs there.
Some specific use cases, as it were. So I’m going to use get transaction info. And if you have not updated SP who is active in the last year, year and a half, you’re going to need to do that in order to see this information. So that’s what that’s what this is the secret sauce, as they say. But you get a new column called implicit underscore tran. And that that’s what’s going to tell you if something’s going to be an implicit transaction or not. This is a null. In this case, not every transaction is going to have a on or off here. I don’t know why. Something’s just weird in the place that this is tracked. Maybe an is null or a coalesce would help with that. Let’s say is null. No. So what I’m going to do is over in this window, I’m going to set implicit transactions on and I’m going to show you why things are weird. And then I’m going to run this count query. I’m going to get a result and I’m going to run this query and I’m going to get a result, but I’m not going to hit commit. I would have to use commit to close this transaction out.
So if I run SP who is active now, I get this back. And in the SQL text, it just says select count big from badges, which, you know, is not really the full story because we had that other select from the users table in there. Now, if I use another parameter for SP who is active called get full inner text and I set that to one, you might think that you would get the full inner text of the set implicit transactions on the select count from users and then this, but you don’t. You still only get the full command. You still only get the select count big. Notice up here in the SQL text, we do get the full store procedure instead of just the wait for command.
Just to refresh your memory, if I quote that out and I run this, all we see is wait for delay, whatever. Now, there’s another parameter you can use with SP who is active called get outer command. In order to see that, we need to change this a little bit to say SQL command.
And if we run this, we’re still not going to get the full story of what else happened in there. All right. So like notice for SQL text, we get the procedure that’s running and we even get we get the name of the procedure, the execution of it over here. But over in this column, we still only get the select count.
So even SP who is active can show you somewhat misleading information about about multiple queries in an implicit transaction. Of course, if I come over here and I hit commit, then everything goes away. I’m running. Now I can’t I can’t double commit.
But just because I have implicit transactions on, I have to commit this here. And now if I run this whole thing, all I see is the information about the CDC job. So what else is there to say?
I’m going to say implicit transactions are a real pain in the butt. They’re a real nuisance. I really don’t suggest that you use them without a full understanding of exactly what they’re doing.
The full the full understanding of everything that you’re doing in a single batch that might hold locks for a really long time. In the implicit transactions specific video that I have, I talk about how that how that can happen. So, yeah, most of the time I just tell people don’t use them because most people just don’t have a good handle on what they’re doing with them.
And there’s no global way to say not allowed because any user session can say, well, I feel like using them. I’m going to override you. I’m not listening to you, mom and dad.
So, yeah, that’s that. Implicit transactions, bad for the body. Bad for the body SQL.
Avoid them. They’re confusing. Even SP who is active has a tough time seeing through their dismal charade. And so do not recommend.
Thumbs down. Thumbs down. Anyway, thank you for watching. Hope you learned something. I hope. Well, I would say that I hope you enjoyed yourselves, except this video was rather depressing, wasn’t it? It was a bit dour.
We didn’t. We didn’t. We only learned how bad something was. We only learned how tough it is to deal with this thing. It is so bad that it even makes SP who is active look foolish, which is a tough gig. Anyway, please find your way to the like and subscribe buttons.
If only so you can see my life and say, God, everything’s kind of all right for me. Anyway, I’m going to record something hopefully less depressing after this one. So thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.