Why INSERT/EXEC Causes Weird Blocking In SQL Server
Video Summary
In this video, I delve into the potential pitfalls of using `INSERT EXEC` in SQL Server, particularly when it comes to stored procedures. I highlight how `INSERT EXEC` can inadvertently wrap your inner procedure in a transaction, leading to unexpected blocking and even deadlocks if locks are taken during execution. By walking through two example stored procedures, one acting as an inner procedure executed via `INSERT EXEC`, I demonstrate how this setup can cause issues, especially when combined with `WAITFOR DELAY`. The video showcases a practical scenario where a simple insert operation results in significant blocking, emphasizing the importance of careful consideration and best practices when using dynamic SQL and transactions.
Full Transcript
Erik Darling here with Darling Data. And today we’re going to talk about why you need to be really careful with insert exec. Now, not necessarily insert exec with dynamic SQL because dynamic SQL is typically, I’m not going to say always, but typically limited to just one query, right? You build up a string, it’s usually a select, and then you execute it. Where you have to be careful of it is with stored procedures. And the reason why is because, and this is something no one believes me, is that when you do insert into a table and exec the stored procedure, that stored procedure executes in a transaction. Okay? So, if you take any locks during that stored procedure, you could be holding onto locks and causing blocking and even deadlocking with that particular query setup arrangement, if you will. I like arrangement better, I think. Sounds more French, right? Ooh la la. So, I’m going to walk you through two store procedures and I’m going to show you a blocking pattern with insert exec. Alright? So, that’s what we’re going to talk about today. A little about insert exec. So, this is the second store procedure. We’re going to call it, we’re going to think of this as the inner store procedure. This is the store procedure that our outer store procedure is.
going to execute inside of itself to insert into a temp table. So, what we’re doing here is we’re obeying the laws of good sequeling. We’re going to set no count and exact abort on. And then we’re going to delete from this table called lock me. And this is just here to show you that when you take a lock, you hold a lock. I know that we could truncate. I know we could do other things, right? Here, we don’t have a where clause, God forbid, but we’re just going to do a delete from this one row table because I don’t think there’s going to be much of a difference if we delete one row from a one row table versus if we truncate it. Do you? I don’t think so. Alright. The second thing we’re going to do in this procedure is we’re going to insert the result of this at at trend count. I guess it’s kind of like a global variable, right? Because it’s got two at’s in front of it. It’s like a global temp table has two pound signs. So, I guess this is kind of like a global variable. And I’m going to show you this because I want you to see, like I can’t select at at trend count in here because we’re going to be doing an insert exec with this procedure. And so, like, that select gets ignored, right? Because the select that gets run is this one at the end. So, I’m going to insert the result of at at trend count to show you that it does go up when the inner store procedure executes. And then I’m going to stick this wait for delay in here just to simulate long running queries, right? In real life, I know your store procedures. They don’t execute in sub millisecond time. They might execute in 5, 10, 15, 20. I’ve seen some of your store procedures out there execute for days.
So, don’t give me that nonsense. And then the final thing we’re going to do is we’re going to select out from this table. In the outer store procedure, this is insert exec 1, right? This is, I created them in this order because I didn’t want to deal with stuff. We’re going to follow the same pattern, be diligent T-SQL practitioners. We’re going to set no count in exact abort on.
In this store procedure, I’m allowed to just do a regular select here, right? Because this one is not being inserted into anything. And then after I select the trend count here, the reason I’m doing this is to show you what the trend count starts at, right? So, this is the starting point for the trend count. And the other one that we just looked at, that’s like the second step.
I’m going to create a temp table called Transporter. I guess that’s a Jason Statham movie. I don’t think I’ve seen it, though. So, you’re going to have to, you’re going to have to, no spoilers.
All right, no spoilers. You’re going to have to keep your mouth shut about what happens in the transporter. And then what we’re going to do is we’re going to insert, I’m going to cough. I’m going to insert the result of our second store procedure, our inner store procedure, into this transporter temp table.
And then I’m going to select star from transporter. And I’m going to get rid of this errant empty line at the bottom there. Are there any other errant empty lines? No.
All right. So, this is just insert exec running, right? I’m going to kick this off. And it doesn’t matter what query plans are for this, because really our main problem with this query is a 10-second wait for.
And when this thing finishes, we’re going to see two result sets come back. We’re going to see that we started with zero and that we ended or that we incremented to two. Crazy, right? Crazy.
How did that happen? All right. So, we’ve established that the inner store procedure gets implicitly wrapped in a transaction, right? Okay. Now, let’s run this again.
And let’s try to select some data from LockMe. And we’re going to come over here. We’re going to wait until this hits about eight seconds. And then we’re going to run who is active.
And what do you see here? Our wait for delay, which at this point had been waiting for 8.647… Sorry.
8,647 milliseconds, or 8.6 seconds, had been blocking our select query, right? This count big over here, this big counter, for almost six and a half seconds. All right?
That’s no good at all, is it? It’s no good whatsoever. Now, of course, you may not experience this because you are either a super genius who uses an optimistic isolation level, or you are some kind of groveling low-level fool who uses no-lock hints everywhere.
All right, we don’t like groveling low-level fools, do we? First percentilers. All right.
So, to wrap things up in today’s first video. Actually, I haven’t had a chance to record at all this week. I’ve just been very busy with things. Also, the weather has gotten nicer, so I’m a little bit more susceptible to going other places to enjoy other things.
But I’m going to record this video and one more video today. You might be able to guess what that other video is about if you diligently look at the titles of some of the tabs that I have open. I’ll give you a hint that it’s not going to be perfmon.
All right. Cool. So, when you use insert exec and you execute a store procedure, be very, very careful and mindful about what that store procedure is doing. Because if you are doing any inserts, updates, or deletes throughout that store procedure, blocks are going to get held until that store procedure completes.
And you could end up with very, very strange-looking blocking chains, especially if you put wait-for-delays in your query. I don’t necessarily recommend you do unless you’re writing, like, a cursor or a loop and you don’t want to just, like, burn out your CPUs on cursors and loops. So, that’s that.
Thank you for watching. Thank you for taking time out of this. I hope the weather is beautiful where you are. I think only our friends around the Rocky Mountains are having a tough day. All right.
So, I hope the weather is beautiful where you are when you see this. I hope you learned something. I hope you enjoyed yourselves. If you like this video, there’s a thumbs-up button somewhere on this screen. I’m not sure what the accessibility options are like in the thumbs-up.
I’ve never had to experience them. But I’m told there’s a thumbs-up button on the YouTube screen. You should hit it if you liked it. All right.
Smash, et cetera. If you enjoy this type of SQL Server informational content, you could pretty please give my channel a subscription. Subscribe.
Like and subscribe. All right. That’s what we’re supposed to say here. And I will see you in the next video that I’m going to record very shortly. So, cool.
Thank you for watching. Thank you.
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.