Two Insert Exec Problems
Chapters
- 00:00:00 – Introduction to the Learn T-SQL with Eric series
- 00:01:34 – Understanding Insert Exec and Its Implications
- 00:07:18 – Blocking Issues with Insert Exec
- 00:12:59 – Performance Issues with Insert Exec
- 00:16:07 – Conclusion and Next Steps
Full Transcript
Erik Darling here, Darling Data, I’ve got a rather exciting one for you today I think, probably anyway, we’re going to talk about two problems with insert exec, one of them I’ve actually shown on this very channel before, but since I have a new one I also want to include the last one because who knows how many of you have shown up to love, adore, and cherish our time together. Since I recorded the last one, I don’t know, I suppose there’s always a chance that some of you found that first video and that’s where you just decided this is the place for me, I’m here for life, but I don’t know, I don’t have those kind of metrics, no one tells me anything, so you’re getting the twofer, alright, good for you. Down in the video description, you’ll find all sorts of useful, helpful links in order for you to give me money in exchange for goods and services.
Services like SQL Server consulting, perhaps you would like me to address performance issues on your SQL Server, wouldn’t that be nice for you, right, you wouldn’t even have to talk to a robot for that to happen, I mean, aside from me. You can also purchase my training, down in the video description, there’s even a coupon code for the Learn T-SQL with Erik course where I talk about things just like this for hours and hours. It’s a Tantric experience, a Tantric T-SQL experience, perhaps the T in T-SQL is for Tantric, I don’t know, it’s Transact, alright, whatever. You can also become a subscribing member of the channel where you give me as few as four American dollars a month in exchange for all of this wonderful content.
You can continue to ask me office hours questions, I’m going to have to work on your taste in music and cloud providers. In the future for those, given recent dilemmas, but that’s, you know, something we can address later. And of course, if you enjoy this content, please do like, subscribe, tell a friend, yada, yada, yada, yada, yada.
If you would like free, as in gratis, gratis, gratissimo, SQL Server performance monitoring, boy, have I got a deal for you. More free, totally free, totally open source. You don’t need to give me an email address or, you know, worry about me, like, looking at your data secretly.
I don’t want it, unless you pay me. It is a bunch of T-SQL collectors running, getting all the important information about performance on your SQL servers and laying them out in nice charts and graphs for you to peruse, browse, and otherwise stare at in a flummoxed state of bafflement for as long as you can bear them. But there’s also…
There’s also a really nice thing in there. There is a built-in MCP server that is optional. You have to enable it yourself. I don’t turn it on by default so that you can have your robot companion friends read just your performance data. Look at just the nice collected aggregated performance data and perhaps give you a better chance of analyzing things a little bit more quickly.
It’s really helpful for folks who are not maybe as well-informed. in SQL Server performance issues as they would like to be or perhaps as well-versed as they should be. But a lot of folks do seem to like that part. But anyway, let’s you and I talk about Insert Exec because you got all sorts of stuff to talk about in here.
All right. So, the first thing I’m going to show you is the blocking problems that Insert Exec can incur. And the reason…
why this happens is because when you use insert exec, the exec portion of the insert has a transaction opened around it. So if your exec is doing more, is like say executing a store procedure that does a bunch of stuff which might include taking locks on things, might include executing other store procedures that perhaps take locks on things, those locks will be held until the insert completes. That can be a very very shocking experience for a lot of people.
Very very shocking. So I’ve got a store procedure here. This is insert exec 2. We’re gonna have to nest things a little bit so I’ve got a 2 and then a 1. Insert exec 2 declares a trancount and holds the current trancount, deletes from a table called lockme, inserts into a table called lockme, and the insert of course does this. Now just to sort of exacerbate a locking issue, I have a wait for delay of 5 seconds inside of insert exec 2. So that’s gonna hold the locks from the delete and the insert above for 5 seconds.
Insert exec 1 just looks at the current trancount, creates a temp table, and then inserts the trancount into the table. Because remember insert what insert exec 2 to or insert exec 2 does is inserts the transaction count from in here right so really what this does is it just shows you the transaction count incrementing to prove to you that in the context of insert exec there is a transaction right so that’s the whole point of this one so if i just if i run insert exec 2 first this will run for five seconds because there is a five second wait for and it will return a tram count of zero right because there is no current insert exec for this but if i run execute insert exec 1 where there is an insert exec where because insert exec 2 up here right we have this block right this is the part where we run into trouble even just inserting into a temp table but the problem really is that we have a delete and an insert in here right and this delete and insert is going to hold locks while the other stuff happens so what i’ve got here is if i run insert exec 1 here and i run this over here and i run sp who is active over here uh we we lost it but that’s okay uh we can do that again real quick and we’ll see just you know immediately the tram count before insert exec was zero and then we smuggled back a transaction here so let’s do that again and let’s run that and let’s just get the lock information from this one so in here you’ll see that i’ve run this over here and i’ve run sp who is active over here and i run and i’ve run this over here and i run this over here and i run this over here and i run this over here we can see um this mouse wheel is weird we can see uh the the wait for delay right uh this is the five seconds that insert exec 2 puts into things to exacerbate locking and we can see our select query here trying to select from lock me uh getting blocked right there uh the blocking session id or rather the the blocking information from who is active points directly to uh session 78 blocking session 84 that’s our select here and if we look over in the locks portion the the locks portion for the query that’s taking the locks is perhaps not terribly terribly interesting um you know we can see the obvious stuff uh we took locks we deleted we updated blah blah blah um i don’t know that one’s not that cool then we we also see the open tran count of one over here right so there’s multiple ways to validate that the insert exec uh does take the lock and then the query takes the lock and then the query takes the lock and then the take uh open a transaction around the entire insert exec thing that does that does not let up until the insert is completed right so all the stuff inside the exec is like all the locks in there are held until the insert completes that can be a very shocking thing for a lot of people but what i want to show you next is something even well something even crazier right so uh what i’m going to do is show you um uh does it does this database matter no not really we’re using temp stuff anyway uh the insert exec can not only block stuff but uh with even just a moderately sized result set it can really really slow things down and it’s really hard to figure out where the time is going and being spent right so i have a temporary store procedure here and this temporary store procedure basically just takes a number of rows that we want to return that really should be a big end but uh we’re not we’re only using i think two million or something in this so it doesn’t matter too much but it should be noted that the input uh to top to a top and uh even offset fetch is is all big end based so don’t don’t be too harsh on me so we’re gonna make this store this temporary store procedure and uh we’re also going to make this one now this one has sort of two paths in it right um we’re gonna say if this temp table exists we’re going to insert uh this query directly into the temp table if not we’re just going to execute the query down here but and this is to show you sort of a fix for the um the insert exec problem right so let’s make sure that query plans are enabled uh and then here this is where we’re going to do two different things uh the first one that we’re going to do is we’re going to create a temp table and then we’re going to insert uh exec like this and uh and then second one uh what we’re going to do is just show um if we use the shared temp table and we insert into that shared temp table locally uh the the time is no longer weird with things but i i’m going to run this all at once because we declare some stuff up here and then we reuse it in both both branches and i think that’s probably not worth retyping for this demo because you know what they say typing in demos just gets you into nothing but trouble right so uh we’ve got some statistics time output which is sort of valuable here just to get sort of an initial look at things and we can see that the first batch in here uh takes about seven and a half seconds to complete we’ve got this weird sort of five and a half seconds thing here and then uh so that was batch a completing right that was this one so about seven and a half seconds for two million rows and then down below we have batch b completing uh which takes about 1.2 seconds for those same 2 million rows this is just inserting directly into the temp table now where things get interesting right is we have um this initial thing here right and this takes 819 milliseconds all right if we look at the properties of this and if you’re looking at query plans please always be looking at properties uh this query looks like it finishes in 819 milliseconds and if you are looking at this query plan and you said this finishes in 819 milliseconds i would not call you totally wrong but we have if we look over here we have an additional five and a half seconds right or we have five and a half seconds so let’s just pretend let’s round a little bit let’s just say we have five seconds of of time that we cannot account for right like maybe i don’t know something weird happened we also have this fun thing all right non-parallelizable intrinsic function uh darn it uh well that’s okay it maxed off one makes more sense for this anyway right and if we look down here that we have this sort of oddball second query right uh and if we look we have this insert exec dest right uh this takes 1.6 seconds uh and comes off 2.4 seconds here but this parameter table scan so what sql server does is it for when you do insert exec uh there’s sort of like a hidden work table type thing where sql inserts all of the rows into that work table right and this is again there’s a transaction here and then from that print from that work table which is the parameter table scan then inserts all your rows where you want them to go so you’re doing like a double right copy thing here with that right so that’s not a very good time if we look at the query time stats on this one right this will actually line up pretty well with what we did right they have cpu and elapsed time at 1.7 seconds there that’s close i mean we’re we still like kind of lost a second or i don’t know like 100 milliseconds or so but i’m willing to forgive 100 millisecond loss but it’s it’s just quite interesting the way that pans out there’s just a count query down here to sort of separate things make visually things make a little bit more sense and then of course we have down here the sort of plain uh insert uh into the temptation table in the store procedure and the query time stats here make total sense right this took one second of time here which lines up pretty closely to what we did here so the insert exec portion double copies the rows and we end up with this weird big sort of time suck of stuff that happens i did a lot of work with um like uh windows performance recorder and um the purview i think to like break down the call stacks and stuff there are some technically interesting points in there that just show like what functions internally the time is spent in but it’s not very interesting on video the big thing you you have to understand the big thing you should start doing is if you have insert exec code that is uh just slow for reasons that you cannot easily determine well stop doing this right because this is this is the insert exact pattern that we’ve shown is bad from a blocking perspective and from a performance perspective and instead inside of your store procedures where you have to um where you would uh let’s just say sometimes you uh and let’s let’s say sometimes you use insert exec and you dump the results into a temp table other times you just execute the store procedure and return the results out one way that you can get around that and you might have to do a little bit more work here with like dynamic sql or something but one way that you can get around that is just look to see and this is very similar to uh the pattern that i showed you about getting triggers to selectively fire if this temp table exists then insert the rows into the temp table right because the outer store procedure will create the temp table um so it’ll be visible to the store procedure on the inner block right so we can see this temp table i can’t it’s showing squiggles here but that’s okay because in the original store procedure up here we create the temp table and that’s that’s where that’s where it will go up and down so um and also That’s where, sorry, in the code itself, we create the temp table so the store procedure can see it, right?
That’s this part down here. We create a table called shared and then we execute this and sort of conditionally inside, the presence of this table means a store procedure takes a different path, does the insert.
If it doesn’t see that temp table, then it just returns the select. And you don’t have to worry so much about like weird if branching stuff. If your code is all parameterized in this way and you’re running the same query either way, then you’ll get the compiled plan for both branches for the set of parameters that you pass in.
It’s a pretty good situation. Anyway, that’s enough of that. We’ve talked for too long. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where we will talk about, I forget, probably go back to talking about date and time stuff.
Go back to the Learn T-SQL with Eric experience. All right. Thank you 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.