Query Plan Archives | Page 56 of 147

Profiling Query Performance In SQL Server With Extended Events The Easy Way

Posted on May 29, 2024January 22, 2026 by Erik Darling

Profiling Query Performance In SQL Server With Extended Events The Easy Way

Thanks for watching!

Video Summary

In this video, I delve into using my free, open-source store procedure, SP_human_events, for profiling stored procedures in SQL Server. This tool is incredibly useful when you’re working on performance tuning and need a detailed view of what’s happening within complex or poorly understood stored procedures. By setting up an extended event that captures query-level performance information, we can monitor specific session IDs or individual stored procedures to identify bottlenecks without the overhead of Query Store. I walk through examples of how to set up SP_human_events to capture and analyze execution plans, parameter values, and other critical metrics from a single window in SQL Server Management Studio (SSMS). This approach helps pinpoint exactly where performance issues lie, making it easier to apply targeted optimizations.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about how you can use my free, open source, bug-free store procedure, SP underscore human events, to profile store procedures. Now, I use this all the time when I’m working with clients. And the main way that I use it is to set up a new user, an extended event that captures as much performance information as I can about something running from a single window in SQL Server Management Studio. So, this is very useful if you have stored procedures that, you know, either do a lot of tiny little things or are just very long, and you’re not really sure where to start your performance tuning, because you’re not really sure what the worst of the worst of the worst of the worst is. Now, you can get some idea about this stuff from Query Store.

Now, you could also use my other store procedure, SP underscore Quickie Store, to search for a specific store procedure in a database. And you could look at the top 10 queries ordered by average CPU and probably do a pretty good job of figuring out where to start in there. But, you know, with Query Store and, you know, with the unfortunateness that is the plan cache, the thing that you may often run into is that you will see one of these, you know, cached plans, which is the equivalent of an estimated plan, right?

There’s no actual execution runtime statistics stored in them. And it might be a little hard to figure out exactly which part of a BigQuery plan is slower. It might be tough to figure out exactly, like, which part you might want to start focusing on. And this will help you do that. So, over in this window, I have a few different examples of how you can set up SP underscore human events to do that.

So, the top one is the one that we’re going to be using today, because we’re just going to be watching a specific session ID. That’s this window over here. We’re going to use the query event type, because this one will get all the query level performance information that we could possibly care about. So, we’re going to only get queries that run for 500 milliseconds or longer. And this is the session ID that we’re going to focus on, 107.

That should be the one that we’re using in this window, but I’ll double check that before we kick things off. And we want to use this keep alive thing, because what the keep alive thing does is it sets up an extended event that SP human events won’t automatically tear down. So, like, one other way of running SP human events is to give it a number of seconds you want it to run for.

And what it’ll do is it’ll start an extended event session, like, grab whatever event type information you decide you want to capture in here. And then at the end of that sampled seconds period, it will, like, parse out all the information from the event and then kill off the session so that it doesn’t keep running and running. Why the keep alive thing is useful is because you can just right click on it, save, watch live data, and then, like, run your store procedure and watch data as it comes in.

So, there are a couple other examples down here. And these two just show you how to collect stuff from one specific store procedure. Right. So, these are a little bit different because they don’t use session ID. We’re just using object name and we’re going to say we just want to get stuff from this store procedure. And if you’re feeling a little scared of, you know, let’s just say that you’re casting a wide net with what you want to collect, one thing that you can skip over is collecting actual execution plans by using the skip plans parameter.

So, this will get you all sorts of other information, you know, parameter value, statement level, CPU duration, all the other stuff, just without the actual execution plans. So, like, maybe this might just be good enough for you to, like, grab an example query execution of something that ran for a long time, rerun it in SSMS and get query plans from it that way. Or use the other, use the other, use the, use SP human events to focus in on a single session, just like recreating that stuff.

So, I do try to make it easy to, to use all these things. You know, there is a, there is a help parameter that lets you figure out exactly which parameters and which valid, which parameters are available and what valid arguments for them are. So, let’s just double check this. We are indeed using session ID 107 here. So, what we are going to do is we are going to execute this block and I don’t know why there is so much white space over there.

That is quite strange looking. And then we are going to go into management and extended events and we should probably refresh this. And now we have this keeper underscore human events query, right? So, it is prefixed with keeper because it is going to stay alive.

And of course, human events because it is SP human events. And then we have underscore query because we are capturing query events. Now, you probably don’t want to have multiples of these running on the same server because, you know, stuff might get a little wonky.

But then what we are going to do is just right click on this and select watch live data. So, we will do that. Now we have this wonderful video, window here where nothing is happening yet.

But if we come over to this window and we execute our stored procedure, we don’t have to have query plans turned on here. We are just going to run this. And as this thing executes, we are going to watch this window.

Because eventually, something will show up in here. Like this is still running. So, we don’t have any, nothing has come in yet. So, some query is running for a good long time. Laptop is starting to make some noise.

And now we have a couple rows in here. And now we have a couple more rows in here. Now, if you are feeling real scared of like, you know, maybe SQL Server, like over collecting your right over to observer overhead, you can stop the extended event from collecting data once it is finished.

But here we have a timestamp, which is oh so very useful. And then we have some of the events that fired in here. So, here’s a query plan for a slow thing.

And you can see the full query plan here. And we can zoom in and, you know, see, oh wow, this took a real long time. It took 12 seconds to insert into this table variable.

Table variables, boo, hiss. And, you know, we had a, this thing took 7 seconds to do a seek. And this thing took 4 seconds to do a scan. And oh boy, the whole thing is just a mess.

Now, if it were me doing my professional query tuning song and dance with this, I would, first thing that I would probably point out is that, you know, you’re inserting into a table variable. And table variables have all sorts of downsides in SQL Server.

Like, you’re not allowed to generate a parallel execution plan when you insert into a table variable. Or when you modify a table variable generally. And if you’re on SQL Server 2022 or various Azure nonsenses, you’ll get this warning.

If you’re on older versions of SQL Server, you’ll just say non-parallel plan reason. Could not generate valid parallel plan. And then we would talk about how table variables don’t get column level statistics, histograms associated with them.

And, you know, they’re a little bit of a black box to SQL Server. And, like, even in newer versions and with recompile hints, we can get, like, the table cardinality. Like, the number of rows that are actually in the table variable.

You still don’t have any, like, good histogram statistic information about the values in the columns like you get with temp tables. So, we’ve got some information here. And one thing that you can do, which I usually end up doing, especially for the longer stored procedures, where there’s a whole lot more stuff in them, is I’ll usually start right-clicking here and hitting Show Column in Table, so that I can see stuff like CPU time and I can see stuff like granted memory and I can see, you know what, this, the post execution plan one isn’t my, doesn’t have all my favorite stuff.

But you can also grab the statement text. You can grab the overall duration. Like, you can grab all sorts of, like, good information about what’s going on in here.

And like I said before, like, if you, even if you skip getting execution plans, there are a couple of the events that show, that are used as part of the event session definition that will show you the parameter values that got passed into the store procedure. So, this can all be very useful stuff to compare.

Now, the, what’s frustrating is that the CPU and duration is in microseconds and my brain does not compute or comprehend microseconds very well. So, like, I just, like, whenever I’m looking at this stuff, I always have to make mental notes. Like, like, go, like, look at the, look at the actual, like, look at the actual execution plan.

This was 12.135 seconds. And so, I know that any numbers that are about this length are going to be, like, over 10 seconds, right? Like, like, like, just, that starts at the 12 and there’s 1, 2, 3, 4, like, 7, 8, 57 digits afterwards.

And I know in my head that this is what, you know, took the bulk of the execution time in here because the full statement took about 15 and a half seconds. So, you know, this is where, like, the query tuning magic sort of kicks in. And let’s kill this thing off.

Let’s say, stop that and, you know, come back over here. Now, this is a lot, again, this is a lot more valuable when you have store procedures that do a lot of different things. Maybe even call sub-store procedures. You know, there’s a lot of, like, tiny little statements in them.

And the reason why this is so useful is because if you have store procedures that do, like, a bajillion things, right? Like, a lot of, like, you know, declaring variables, assigning variables things. And you turn on actual execution plans in SSMS, you’re going to flood that thing and have just a bad old time scrolling through a billion tiny little different execution plans trying to figure out where in the batch the slow stuff happened.

So, you know, just to do a little query tuning song and dance stuff. Let’s say, you know, we looked at all this stuff and we said, ah, table variables. And we shook our fists and we got, you know, gathered up the angry villagers, rounded up a constabulary.

We did everything we could. We, you know, united our forces. And we were like, you know what?

We’re going to do the query tuning professional thing. And we’re going to replace at signs with pound signs or hash signs depending on where in the world you live. And let’s just say that we wanted to change this query to use temp tables instead of table variables.

And just to make life a little bit easier because there’s only two statements in this one. Let’s run this. And now we’ll see that this didn’t take 15 seconds.

We had two quick. We have our queries now that finish very quickly. This one takes 2.2 seconds. Yay. And this one takes 352 milliseconds.

So, you know, the temp table wins again. The old temp table versus table variable foot race. And just to preemptively answer any questions, no, a CTE wouldn’t be better here. You stop it.

Stop it with the CTE. They’re not, they’re not your friend. So, again, this is how you can use SP human events to monitor. You could even say, you could use it to profile the activity of a single spid.

And that’s what we did here. And we got valuable information and insight into what our query was doing, why it was taking a long time. And then we knew exactly what to do to fix it.

So, if you find yourself in your job as a, whatever you do, having to tune SQL Server queries, this is a great way to capture the performance details of store procedures that do a whole lot of different crazy things so that you can figure out exactly where you should start tuning them. Granted, on this one, I started with a very low query duration. That’s a very low ceiling for me.

If I’m looking at something for the first time, I’ll probably set this higher, like one second or five seconds or ten seconds, depending on, you know, like what I know about the store procedure. Generally, if someone tells me that something runs for a half hour and I see there’s like, you know, like, get some information about it and I see that like the worst running queries run for like over a minute, I might even set that query duration thing to like 30 seconds so that I focus on the big stuff first. And I don’t get like all squirrel brained about, oh, this thing takes like five seconds.

I should also fix that even though I know that like getting like improving something that runs for like 30 seconds by, you know, five seconds or like 30 minutes by five seconds isn’t going to make anyone happy. I want to focus on the big stuff first. So this is a few different ways you can do that.

You can get this again for totally free from my GitHub repo. The link to the GitHub repo will be in the in the video description. And you can you can you can start your fun there.

So that’s that’s good, too. All sorts of interesting stuff in there. And you can use SP human events for several different different things that you might care about in SQL Server, Blank, Compiles, Recompiles, things like that.

So there’s all sorts of good stuff you can do in there. And yeah, this is the most common way I use it. I hope I hope you I hope I hope you find it as useful as I have found it. So thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope that you’ll go use this store procedure that was a quite a monumental chunk of effort to get get working and correct and all that other good stuff. God, extended events are hard.

That’s why I made this thing. But anyway, if you like this video, there are you have options for showing me your your mass approval. There’s a thumbs up button, which is good.

There’s a comment area where you can leave nice comments, no mean comments. Please, I’m a fragile human being. And if you like this sort of stuff generally, subscribe to the channel. I want to say that at this point, if you subscribe to my channel, you will be one of nearly 3,448 people who have also subscribed.

So the next person will be 3,448. Imagine that. Quite a quite an island of misfit toys.

Mass dear. Anyway, thank you for watching. It’s hot under these lights. And I’m going to go drink some water or I fall over. Thank you.

We’ll catch it. Now not to close the house or I go dorm if you have to. Guess what just?

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.

Plan Cache Pollution From Temporary Objects In SQL Server

Posted on May 28, 2024January 22, 2026 by Erik Darling

Plan Cache Pollution From Temporary Objects In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into an interesting aspect of SQL Server stored procedures involving temp tables. Specifically, I explore how creating a temp table in one stored procedure and using it within another can lead to unexpected behavior in the plan cache and query store. By walking through a detailed example with two stored procedures—one that creates a temp table and calls another that uses it—I demonstrate how this setup results in multiple compilations and recompiles, even when using the `KEEPFIXEDPLAN` hint. I also highlight the differences between traditional plan caching and Query Store, showing that while the optional spid issue affects the plan cache, it does not impact Query Store, leading to a cleaner execution history. This video aims to provide insights for database administrators and developers who need to optimize their stored procedures and understand how SQL Server manages query plans in complex scenarios.

Full Transcript

Erik Darling here with Darling Data Enterprise Edition. That’d be nice, right? Everyone should be Erik Darling for 15 minutes just to experience how bizarre it is. Have to talk about these things. Today’s video is actually sort of a reader mailbag comment. It wasn’t really a question. But it was a comment that I identified with because it’s something that I’ve run into. And it was, I’ve actually had two questions about it because there’ll be a link to the blog post in the show notes. And a while back, I sort of co-authored a blog post with Mr. Joe Obish about how this certain use of temp tables in stored procedures, like when you have a stored procedures, like when you have a stored procedure that creates a temp table and then calls another stored procedure where that temp table also gets used, not like you can share temp tables between stored procedures, how that can cause weird plant cache pollution. And there was also another sort of like reader comment slash concern about if that would affect query store. And we’re going to look at that today. So, I’ve already got my index created. I’ve already successfully used a stack overflow database. And these are the two stored procedures in question. Now, we’re going to look at these in one slightly different way towards the end of the video. But for now, this is good enough. Good enough for government work, as they say. So, in this stored procedure, this is the inner stored procedure, because this is the stored procedure that gets executed inside of an outer stored procedure. And this may look familiar because I use this in a recent video to do some other stuff. But in this one, we just select from a temp table. I’m just going to use a couple different variations on this query, one with no hint whatsoever, and the other with the option, keep fixed plan hint. Now, this is the outer procedure, where we’re going to create a temp table, we’re going to insert data into that temp table, and then execute the inner stored procedure to finish things off. We’re going to look at this from two different, two different, well, actually, like four different ways. We’re going to look at this from the point of view of compiles, recompiles, and of course, how it affects the plan cache. So we’re going to look at the plan cache information in here. And so what I’m going to do is to start things off, I’m going to make sure that query store and the plan cache have been cleared out. We don’t need this just yet, we’re going to do this towards, well, after we do some other things, just got to make sure that, make sure that, make sure that I have my semicolons in there. Can’t go a day without semicolons. And then we’re going to explore a blog post from 2019 written by the most beautiful man at Microsoft, Joe Sack, about reduced compilations for workloads using temporary tables, where some stuff happens. We’re going to click on the link in a little bit, don’t worry.

So what I need to do in order to start proving that I know anything about SQL Server to you is fire off a couple of different instances of my store procedure, along with these funny loopy things. So much like in the last video where I talked about how recompile, keep plan, keep fixed plan, and other things of that nature are compiled and recompiled and plan get reused in different ways amongst them. This is just a loop that goes through, executes this door procedure, increments some stuff, and eventually finishes and returns some results. And we just want to get a good, healthy number of executions. And for this particular demo, I’m executing this from three different windows, because what I want to do is get three different spids, three different session IDs, all using this stuff together.

All right, that’s the goal here, because that’s where we see stuff start to get way out wiggy weird. Now, this is going to be different from if we had just done all the work within a single store procedure, because that spid is actually going to get cached along with the execution plans. So if we come over to look at the compile situation for this store procedure, we’ll see a total of three compiles.

And it’s a little bit easier to just click on these things. So this is the store, this is the statement that does not have a query hint on it. And you’ll see this one looks a little bit different, because this one actually has some compile CPU stuff alongside it.

This one also shows three compiles, but there’s like no compile time or anything associated with it, all this line here. And this is going to be the one that has that keep fixed plan hint. Now, what the recompile situation looks like is a little bit different.

So because statistics changed on the temp table, this statement without that keep fixed plan hint recompiled 123 times. So we have 123 recompiles plus three compiles, right? Keep that straight in your head a little bit.

And let’s come over here and let’s look at the plan cache. What we’re going to see is something pretty close to what we saw last time, except a little bit different. It’s a teeny tiny bit different.

Because all those different spids were executing the store procedure at the same time from different connections. And SQL Server was caching those temp tables. If we look over here, we’re going to see three different spids show up in this optional spid attribute.

Not throw up in. That would be gross and smelly and like a New York subway. I mean, it could be either the New York subway, the sandwich chain or New York subway, the mode of transportation.

Either one, you get what you get. So if we look at really this statement here, right? This one in the middle.

Oops. Scroll bars. Just mutiny. Mutiny today across all technologies. Scroll bars included. We have 126 executions of the get average score by year outer, which essentially just creates a temp table and does the insert.

Right? And so there’s 126 of those. And that number adds up.

If you remember, if you come back to these windows, we have the 123 recompiles and the 3 compiles. Right? So that adds up to 126.

So every time this statement ran across those three spids, it either compiled an initial plan or recompiled. Right? So we had the initial three runs where it was like compile a plan and then 123 where it was like recompile.

Pretty crazy. Now, I guess the point that I’m making with this is that even though we get good plan reuse across the three statements that have the keep fixed plan hint. That’s these three right here.

I wonder if we just make this a little bit bigger. These three down the bottom have that keep fixed plan hint. So they don’t recompile constantly, but they do initially all compile a different execution plan. Right?

Because this optional spid value makes new plan cache entries. And if we look at these spids, 68, 79, 71, that’s going to match up with what we have down at the bottom for session IDs. I’m trying to figure out a good way to get.

There’s 68 right there. You can see it right next to the shoulder. If we do it, if we zoom in on this one, come over here. Where are you, baby?

Where are you? Why are you running away from me? There’s spid 79. And this should be spid 81. If we zoom in correctly over here. Sorry, 71.

Right? So we see that even with the keep fixed plan hint, we do get that optional spid cache. And we do get an initial plan compilation, but we still get the good plan reuse after that. Right?

So even with keep fixed plan, we don’t get the same, well, I guess we do get the same behavior in that we don’t constantly recompile after we compile a plan. But the optional spid cached along with that does create three different plan cache entries. This does not happen in Query Store, though.

And if we look at entries in Query Store for the get average score by year inner procedure, and only that. All right, if we look at this, we’re going to see, come over here a little bit, we’re going to see just 126 executions each. So by the time SQL Server gets to sticking stuff in Query Store, all the optional spid stuff is pulled out.

So the optional spid issue with the plan cache doesn’t transfer to Query Store. Query Store just shows 126 executions of each. Of course, you know, the one with the keep fixed plan hint is going to have just the three compiles.

And the one that doesn’t have the keep fixed plan hint is going to show the three compiles plus 126 recompiles. Sorry, 123 recompiles to add up to 126. But just looking at this, right, like we just get the, we just see the 126 total executions in there.

Where this changes just a tiny, itty bitty little bit. And we should probably, probably click on this post to, oh, Microsoft Edge, what’s new? I don’t know.

Just look at what’s new in Chrome. You’ll see about the same thing. So here we are. Again, the most beautiful man at Microsoft, Joe Sack. Back in 2019, which is a blog post about one of the early CTP releases of SQL Server 2019, 2.3. And in his pattern, the temp table is created here, but then the insert is done here.

Now, well, this does help with the recompiles. You still have to compile a different entry. So if I were to move the insert statement from the outer procedure and put that in the inner procedure, we would still see that insert with the optional spid.

We would see three entries for that. And I suppose I can just go and show you that so you don’t call me a liar because, you know, for some reason a lot of people think I just make stuff up, which, you know, I wish that I had the moxie to just make stuff up about SQL Server like so many other people seem to do in their various posts and other things, just make things up entirely or paste things in from chat GPT.

And it’s all just sad and lonely. All right. So we’re going to rerun this.

And what I should probably do just to make absolutely positively extra extra sure is reset query store and the plan cache. And again, we’re going to do the same thing where we go, but up, but up. And then we’re going to run these three.

And we’re just going to twiddle our thumbs until this finishes. Now, I do think that, you know, SQL Server 2019 did help with the recompile issue, but the optional spid thing does still contribute to the, like, extra plans being compiled thing, which isn’t great because the plan cache is such, like, a terrible, noisy, ephemeral place anyway that stuff like this can really add up.

And stuff like this can really make the plan cache a pretty bad place to look for performance problems. There are certain performance problems. I mean, not even performance problems. There are certain, let’s just call them workload oddities, that will only show up in the plan cache because you can’t, this stuff doesn’t affect query store.

Right? Like, by the time you get to query store, the option, like, see, query store is like optional spid. Who?

What? Just get, get, go away, go away from me. Why are you here? I don’t need an optional spid. Plans don’t get used from here. Get, get, get it away from me. So if we look at this, we have the, well, now we have 126 total compiles of the, the hintless plan. And we have something a little bit interesting in here, right, where now we have an additional statement has entered the fray.

And we have the three compiles here, right? So the, the, the, the insert statement didn’t recompile, but it never recompiled. We just see the text for it in this now because we moved it into the inner procedure.

So we still have the three compiles of the insert because if we go and look at the, we go and look at the plan cache now, that insert statement is going to be included in the, what do you call it? The inner store procedure.

So these three lines with the insert into filtered posts, these all get, I mean, again, they still get the three separate entries for the three separate spids, but they do get good plan reuse within that. So there’s 42 executions across all these, whereas these have, you know, 42 executions, but 43 plan generation numbers. Because we do this as a new plan almost every single time with the stats changes.

And if we come over here and we look at, let’s just narrow it down to this chunk with the, sorry, the inserts and the select statement. So we’re just like this span of like six rows. You know, we’re still going to see the optional spids now cached with the insert query now too.

This only happens with queries from the inner store procedure, not with the outer store procedure. So in a weird way, it might even be better. It might be better if we kept this the original way where the insert is done in the outer procedure, because then you only get the one plan cache for it.

You don’t get three separate plans cached for it with the optional spid put in there, right? So depending on how, like, how big of an issue this might be for you, you might want to consider moving your inserts into whatever outer store procedure calls there are, and moving your temp table stuff into just the queries from, like, not the inserts into the inner store procedure.

Now, this is one of those funny things where, like, I’ve long believed that query store should take over responsibility from the plan cache in many ways. Because in a lot of ways, it’s doing that more and more. Because in query store, you can force execution plans.

In 2022, you can apply query hints. Like, there’s all sorts of, like, feedback mechanisms within query store that don’t exist in the plan cache. And I really think that the query store should take over more responsibility from the plan cache, because the plan cache is just such an awful, again, very just unpredictable place.

It almost seems like a retrograde way of doing things now that we have query store enabled, you know, by default with SQL Server 2022. Now, query store does need help in a few different places. You know, the GUI is a disaster.

And a lot of the cleanup stuff gets real screwy. Like, with a few different clients now, I’ve had to run the SP flush query store to disk procedure on, like, an hourly schedule, so that query store doesn’t fill up because cleanup only happens, like, once a day or something.

And, like, it just gets too big and no new plans get in. It goes into read-write or it goes into an error state. There’s a lot of stuff with query store that needs help before it can really take the championship belt from the plan cache with these things.

But I think with the amount of responsibility that query store has now for directing query execution, both from the plan forcing, adding hints to queries, like, different intelligent query processing feedback mechanisms, that really we’re at the point now where query store should be taking more and more ownership of things that the plan cache used to take care of.

When you add into that, the query store is much better at managing historical data, and you have a much better track record, and you can see query regressions and, you know, all sorts of other stuff, there’s almost no sense in relying on the plan cache for much anymore, at least from what I can tell because the plan cache is just full of spiders and ghosts and ghouls and gremlins that I just don’t, I don’t like messing with it.

I avoid looking at the plan cache as much as possible when I have something better like query store or a third-party monitoring tool that I can rely on rather than having to, like, run it and just be like, oh, well, I mean, 99% of the plans were created in the last hour.

Do you care about, like, did anything bad happen in the last hour? Do you care about any, were there any performances in the last hour? No.

Okay, well, we’re out of luck. Like, the plan cache is just so often useless that I just kind of stopped looking at it. Unless I need to explore sort of weird issues like the optional SPID thing, and, like, why, you know, different queries might end up with different execution plans and how temp tables get used and shared and all that other stuff.

So there are times when it’s, you know, you do need to get some information there, but even getting that information is hard unless you’re constantly monitoring it because there’s so much churn and flush with the plan cache. So, anyway, if I had to summarize this video, it is that, well, it is an interesting and novel method of sharing data between store procedures to use temp tables to do that.

It can certainly have some interesting side effects on the plan cache. As much as possible, I would say that you should keep temp table creation population and, like, you know, select logic contained within one store procedure as you can.

If you have to, like, share temporary table data from one store procedure to another, just be a little bit careful about how you do that because the more logic you put inside the inner store procedure and the more concurrency there is with that store procedure, the more plans you’re going to see popping up and doing that stuff.

Of course, like we talked about, that, you know, doesn’t really affect Query Store because by the time everything gets to Query Store, everything is, like, the optional spit stuff is all parsed out anyway.

It doesn’t matter. So, anyway, a fun Monday video. Got to, it’s a good one for me because I got to share some stuff. I got to do some demos.

I got to rant a little bit about Query Store and the plan cache. And really, I don’t find those things fulfilling. It’s just, it’s nice to get them off my chest because who else would listen? I can’t, I can’t tell my wife about this.

I can’t, like, you know, head downstairs and be like, honey, you wouldn’t believe the plan cache. You wouldn’t believe what the plan cache does. You share temp tables between store procedures. You’d immediately zone out.

Like, yeah, okay. Go right to the phone on that. So, anyway, thank you for watching. I hope you enjoyed something. I hope you learned something.

Enjoyed yourselves? Learned something? If you like this video, there’s a thumbs up button that’s the nice thing to push for me. So I feel better.

And I also like getting comments. Topical comments. Thought-provoking comments. Good questions about what we’re talking about. If you like this sort of SQL Server content, you can subscribe to the channel.

Almost 3,500 people have done that so far, which, you know, maybe if I did a little bit more ASMR, my numbers would be better. But from what I can tell from previous recordings where there were perhaps some audio issues where there was a little accidental body noise ASMR, y’all don’t want to hear that.

So, you know, probably going to avoid that. So, anyway, thank you for watching. And like and subscribe. And temp tables.

Yeah. Temp tables.

Going Further

Erik and Kendra Rate SQL Performance Tuning Techniques

Posted on May 27, 2024May 17, 2024 by Erik Darling

Erik and Kendra Rate SQL Performance Tuning Techniques

Wanna catch us at PASS Data Summit this November? Get your tickets here!

Thanks for watching!

Going Further

Updated First Responder Kit: A Remembrance Of Scripts Past

Posted on May 24, 2024May 24, 2024 by Erik Darling

In The Beginning

Or at least at some point, back when I gave all my precious blog blood somewhere else, I used to quite enjoy writing the release notes for the First Responder Kit. It was fun, and there were a lot of contributors to credit.

This most recent release had a note in it that got me taking a stroll down memory lane.

Deprecating sp_BlitzInMemoryOLTP, sp_BlitzQueryStore, and sp_AllNightLog

sp_BlitzQueryStore was originally written by Erik Darling when he worked here. He’s moved on to start his own excellent company, plus his own sp_QuickieStore. You should be using that instead.

sp_BlitzInMemoryOLTP was always kinda distributed as a courtesy – the real home for it is in KTaranov’s Github repository, and you can still find it there. It hasn’t been updated in over 6 years, and I’ve never seen anyone using it, so I’m removing it to streamline support issues.

sp_AllNightLog was a ton of fun when we built it several years ago, but it’s consistently had a problem. Companies start using it, then decide they want to build something even more ambitious, typically a C# service with robust error handling and scheduling. sp_AllNightLog isn’t the kind of thing I want to encourage beginners to use – it’s complex.

I didn’t have much to do with sp_BlitzInMemoryOLTP. I’m still not entirely sure what it does. All I know is that In-Memory was the hottest frog in the pan for exactly 14.9 minutes.

But I have some quite fond memories of building sp_BlitzQueryStore, and sp_AllNightLog.

I didn’t write every bit of code in either one, but I definitely started work on and them pitched in quite a bit on both. Other contributors deserve whatever credit they’re publicly willing to take.

If you’ll permit a fella with a lot more grey on his face than there was when these two procedures were first F5 birthed into the world to reminisce a bit, I would like to eulogize them here.

sp_BlitzQueryStore

I had been working on sp_BlitzCache just about full time for a couple years, since Jeremiah (who wrote it originally) had decided to embark on a professorial career. When Query Store got announced, I knew I wanted to write something for it.

After all, this seemed like a no-brainer for folks on SQL Server 2016 to adopt. I just had no concept of what I wanted to do, until one day…

I had just gotten off the phone with the worst credit card company in the world, because someone had purchased ONE-HUNDRED $99 Play Station gift cards from a Russian IP address with a .ru email, and they told me that I would need to fill out 100 dispute PDFs to dispute each charge separately.

I forget where I was walking home from, but I was in the company Slack, and I had a message from BrentO asking how I wanted to approach it, and I felt like I had to make something good up on the spot. It turned out to be: I want to find all of the worst metric spikes, and grab the queries that ran during them. So it would look for the highest CPU, reads, writes, memory, tempdb, etc. and look for the queries responsible for them. And since we have all this groovy historical data, I wanted to show which queries were sensitive to parameter sensitivity by looking for wild swings in those metrics.

In theory, this was a great idea. In practice, those queries were god awful slow. It wasn’t all my fault, of course; I can’t take full credit. I see a lot of scripts (including queries from the SSMS GUI) that hit Query Store which are equally as God awful slow.

Perhaps ironically, some of the absolute slowest points in any Query Store query are the ones that hit the “in memory” tables.

sql server query plan — that is seven minutes, thank you for asking

At any rate, SQL Server 2016 adoption was fairly slow, and Query Store adoption was even slower. It was even hard to recommend turning it on at first because of all the bugs and issues that were cropping up and getting fixed in CUs (and even then, Service Packs). SQL Server 2017 didn’t help things at all, and I was out on my own in the world by the time SQL Server 2019 got released.

So poor ol’ sp_BlitzQueryStore languished a bit. Of course, as I added checks and gizmos to sp_BlitzCache, I’d also add them to sp_BlitzQueryStore, but… It just wasn’t the same every day utility belt tool for me.

When I sort of lost faith in the whole thing was sometime in 2018 when I tweaked a query in sp_BlitzQueryStore to try to speed things up, and it made my local SQL instance stack dump, and I had to manually restart it. If that happened with a client, hoo boy!

But here’s to you, sp_BlitzQueryStore! It’s how I first started learning the Query Store DMVs, how they related, and what data was in them.

You’re like that long-term relationship that ends before you meet the person you end up marrying.

sp_AllNightLog

This made me feel cool, because:

I’d always loved Log Shipping (still hate AGs)
It was my first “programming” stored procedure

Let me clarify point 2 a bit, because I’m not one of those “SQL isn’t coding” people. Most things that I write only work with data. This worked with REAL LIVE FILES. Writing them. Copying them. Restoring them. Across two different servers. Where neither one knew the other existed.

Wild. WILD!

I’m not sure if I’m allowed to say the name of the company that wanted it, but they were based in Chicago, so I ended up flying out there a couple times to work on it along with Brent.

That kind of stuff had never happened to me before, and has only happened a couple times since.

I learned some hard lessons from this one:

If you fat-finger an invalid date value in an output parameter, you’ll end up with a REALLY HARD BUG TO FIND
If you don’t add a WAITFOR to looping code that’s constantly looking for new databases, new backups to take, and new backups to restore, you can really drive one CPU crazy

It was also when I learned that you can’t add triggers to “system” tables, like restorehistory, in the dbo schema, in msdb. If we could have done that, a few things would have been way easier.

Of course, my fondest memory of this one was when it finally worked. I remember hitting a bazillion errors and issues and debugging stuff for ages. And then one magical day, the Agent jobs started up, and it was all green.

I was far from alone in working on it; I don’t want it to sound like THIS ONE’S ALL ME. There were a group of 3-4 people who put in work writing and testing things.

That was kind of the nicest thing about it — real collaboration with real people in real life — not just pushing changes around the internet and asking people to test them.

The Departed

While it is a bit sad to see them go, I totally understand why they had to. It’s difficult to be responsible for large code repos that you don’t use a lot, and have become unfamiliar with because you don’t use them regularly.

I’m a bit surprised that sp_BlitzBackups didn’t also end up in the deprecation pile. It hasn’t had an issue opened since 2019, or any commits aside from version bumps. But maybe it’s just that well-written! If that ever does get deprecated, I have exactly one funny memory of the writing process, and it probably won’t get a blog post. I can spare you the drama of “it runs really slow when there are hundreds of databases” and “how slow?” and “like a minute” and “why do you need to run it every 15 seconds anyway?”.

Perhaps the most surprising thing about the years since 2016 is that not a single third party monitoring tool has embraced Query Store data in their performance diagnostics. Hopefully someday someone tells them about it, I guess?

But hey, that’s enough about that! Go get the most recent release of the First Responder Kit because it has a bunch of new and improved in it. Enjoy your shinies while they last.

Thanks for reading!

Going Further

The Difference Between Read Committed And Read Committed Snapshot Isolation In SQL Server

Posted on May 23, 2024January 22, 2026 by Erik Darling

The Difference Between Read Committed And Read Committed Snapshot Isolation In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the differences between read-committed isolation and read-committed snapshot isolation (RCSI) in SQL Server, specifically addressing how RCSI mitigates some of the issues that can arise under the pessimistic isolation level. I demonstrate these concepts using a simple example with tables named consultants and clients from my CRAP database, showing how read queries behave differently when using RCSI compared to traditional read-committed mode. By walking through the process step-by-step, I explain why optimistic isolation levels like RCSI can be more aligned with developer expectations in many scenarios, while also highlighting potential trade-offs and considerations for different workloads.

Full Transcript

Erik Darling here with Darling Data. And spring is all around us here in the Northern Hemisphere, but for me more specifically, spring is all in my nose. So if I sound a little weird today, that’s why. And spring being in my nose is not a Deadpool-esque drug reference, it is an allusion to my allergies, which is alliteration. So last week I went on vacation, but before I did, that was a rhyme too. Wow, I’m nailing it today. Last week I went on vacation, but before I did, I recorded a video about different read phenomena that can happen under read-committed, the pessimistic isolation level that can make query results look weird. And in that video, I made several sweeping proclamations that this sort of thing wouldn’t happen with an optimistic isolation level like read-committed snapshot isolation.

Then of course, while I’m away, exploring business opportunities across Europe, I get questions in my email like, How would it be different? Why would that be different? Please tell me. So I’m recording a video to answer all of those questions today.

So the first thing that we’re going to do is we’re going to alter the CRAP database. I do not distribute the CRAP database. It is not an open source project. If you would like the CRAP database, you are free to create a database called that, and you are free to fill it with whatever you’d like, all the joy in life, your hopes, your dreams, whatever they may be. And then we’re going to redo a couple steps from the last video where we get rid of any tables that might exist that might cause strange things that would make the demo not work.

So I aim to be idempotent. It’s a tough word sometimes. And so what we’re going to do is create a table called consultants.

And even though there is only one consultant in there, it’s called consultants because I think naming tables as plural is kind of the right thing to do. You have consultants in a table. Each row is a consultant. Many consultants are plural.

So for now, there’s just one of me. And we’re going to stick old Erik Darling in there. And then we’re going to create a table called clients just like last time. And just like last time, the clients table will have a lovely primary key and will feature a magnificent foreign key that references the consultant ID in the consultants table.

Good stuff there. All right. Cool. So let’s insert a couple rows into clients because we have Erik Darling, the consultant at Darling Data, has many clients.

Right. So it makes sense to call that clients, even though there’s only two here. I promise there’s been more in a few. And then if we look at the data that we currently have in clients, then well, sorry, consultants and clients joined together, everything looks good.

Right. Consultant ID, first name, last name, invoice ID. The consultant ID again, a bit repetitive, but that’s what you get with select star. And of course, the invoice amount, which is an amount that I would love to invoice in American dollars to someone someday. Perhaps if the government is looking for some SQL Server help, we could talk.

Print some more money for me so that whatever number that is, is worthless. Worth five dollars in real life. So over here and let’s take this.

Let’s stick this over here. I think, oh, look, I already did. I’m so, I’m so smart. I see ahead. I see into the future. I see all things. All right.

So that returns right results. Now, in the last video, what happened was I said, begin tran and didn’t update. And then over here in this window, I ran this query and this query got blocked. This query got blocked until I committed the transaction in the other window and then it returned inconsistent results.

Since read committed snapshot isolation is now working with the version store, we are getting a snapshot of this data from prior to the update happening. So we still see all the right stuff in here because if you look over what this update is doing, we are incrementing that invoice amount by $1. I got a dollar tip on that huge invoice.

Someone was just like, yeah, Eric, darling, you’re worth that extra buck before we overflow the big value for SQL Server. Top you off. So in the last video, this query got blocked and this query, it doesn’t.

This query runs and returns the values as they existed prior to the update running. And the same thing will happen if I run this update, right, and I changed my last name to Darling Data because Erik Darling married to the data game, right? So we run this.

This will still not be blocked, but it still won’t reflect the change within that transaction, right? That’s still out there in the open. Nothing going on there. If I run this query from within the transaction, well, then I can see all the changes that happened because this query is working within the transaction that made the changes.

And then finally, if I commit this transaction, let’s make sure we’re extra committed to that transaction. And we come over here. Now this query will finally see the changes.

So the difference, of course, is that your read queries don’t get blocked, but your read queries might be seeing older versions of how data existed before changes started happening to it. So that can be great for some people. That can be great for 90 something percent of the people who have a workload in SQL Server because it’s great for most people who have workloads on other database platforms that made better choices of default isolation levels like Oracle, Postgres, and probably DB2 if anyone can find DB2 out there.

So that’s how they’re different. And we can repeat the same thing with the other query that I showed you where it looked like a unique constraint had been violated in SQL Server. Where it looked like we had duplicate values in unique constraint.

In this case, we won’t see any weird results. So we’ll run these first three updates right here. One, two, three.

And we’ll just refresh your soggy memories. There is a unique constraint on Butthead. And so if we were to try to insert a fourth row, it would fail because we would violate that unique constraint. And that was sort of the gist of the last demo where I was like, hey, if enough changes happen and things swap around, your query results could make it look like that column returned non-unique results.

And that would be confusing and awful and terrifying. You would question SQL Server and you’d call up Microsoft and be like, Microsoft, SQL Server has a memory leak. And it’s broken.

You better catch it for whatever kids do these days when they make prank calls. So let’s grab this query just to prove out our point once again. Let’s paste that in there.

And if we run this select, we get exactly what we should. Because no changes have happened with the table. We get Beavis incorrectly saying huh and Butthead incorrectly saying other things. Well, I guess the one at the bottom is right.

But the first two, absolutely wrong. Non-canonical Beavis and Butthead laughs. All of Flutter here in this demo. But now, let’s begin trend and update. And just like last time, we are not going to see any blocking here.

But we’re going to see the data the same way that it was before the update started. And if we run a couple more updates, we are still going to not be blocked. But we’re still not going to see any changes.

Now, if I were committing these changes along the way, of course, we would see them over here. But because these changes haven’t been committed yet, we’re still seeing that snapshot of the data from before the changes. And just like in the prior demo, if I select from this, and here we can see the changes, right?

Then if I commit and then extra commit that transaction over here, those changes will finally show over here. But the important thing is that this select will never look like it returned a violation of our unique constraint on the Butthead column.

So, when I talk about workloads that would benefit from RCSI, this is a big one. Not get, not your read queries, not getting blocked, and returning correct results from prior to modification starting, is usually what people want.

Using no lock hints, being able to see transactions in flight is not good, right? And seeing dirty data, it’s dirty reads. The concern here, of course, is reading stale data, right?

Because if your select queries do require getting blocked, and then seeing changes after the blocking, read committed might be the right isolation level for you.

But with the huge caveats that came with stuff in the last video, about how if there are multiple queries in a transaction that make changes, if your query got blocked after like, like let’s say that there are 10 of them, if your query got blocked on like the fifth one, and then five more things happened, your query would have seen like five things that changed, five things that didn’t change, and then five things that did change, and that can be really screwy for results too.

So, again, no isolation level is generally 100,000 million percent perfect for every workload. However, I do find that the way optimistic isolation levels is far closer to developer expectations than the read committed reality that they’re faced with, and of course the no lock or read uncommitted shortcuts that they take when blocking happens, and they’re like, why God, why have you forsaken me here, letting my queries get blocked like this.

So, that’s the difference between read committed, the pessimistic isolation level, and read committed snapshot isolation, the optimistic isolation level. I hope that this makes things more clear, and I hope that this helps you with anything that you might want to test in your own environment to make sure that if you are going to start using an optimistic isolation level, that you use the right one.

Because remember you have snapshot isolation over here, which requires queries to ask for it, and you have read committed snapshot isolation over here, which affects all of the read queries that come into your database. So, if you find that there are queries that would operate incorrectly under read committed snapshot isolation, you could choose snapshot isolation and have certain queries that don’t require that to opt in to using an optimistic isolation level, or you could add read committed lock hints to queries that do require not reading from a version of the data, so that those queries would obey the locking semantics of read committed.

But, just remember, read committed leaves a lot to be desired as far as guarantees go. The guarantees for read committed, the pessimistic isolation level, are very, very weak. If you truly have queries that need to operate off of the freshest data without having, like, changes and other things happening all around them mess them up, you might be looking at needing to use repeatable read or serializable as an isolation level for absolute correctness.

Because under concurrency, read committed the pessimistic isolation level wipes out pretty hard a lot of the times. Does not, well, I would say it doesn’t live up to expectations, but the reality is, it’s really just behaving as it’s documented and your expectations were wrong.

Read committed is just kind of a crappy isolation level. Even in the crap database, it’s a crappy isolation level. So, before I need to blow my nose or sneeze or anything else that would be untoward to do on camera, I’m gonna end this video.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you missed me terribly while I was away exploring business opportunities in Europe. If you like this video, the thumbs up button is a great way to show that.

I also like comments that say, Yay, good job, Eric. And I also like subscribers, because the more subscribers I have, the more people I reach in teaching people how to use SQL Server correctly, which is nice, because very few people do that.

And I’d like to see more of it, so that one day I can do something else with my time. I don’t know. Start a channel about how to behave properly in a bar.

How to not annoy bouncers. I don’t know. How to not annoy bartenders. There’s lots of things. Lots of things that I could do. Lots of things I could do with my time.

But here I am, talking about SQL Server. So, that’s enough of that. Again, thank you for watching, and I will see you in another video, another time, another place, another you, another me.

Who knows? Goodbye. Goodbye. . .

Going Further

Is Using OPTIMIZE FOR More Reliable Than Forced Plans In SQL Server?

Posted on May 22, 2024May 9, 2024 by Erik Darling

Parameter Sniffing?

I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.

There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.

This is especially common in third party vendor environments where code and/or index changes may not be allowed without the okay from the high priest of tech support who only answers questions when their celestial craft passes near Earth every 27 years.

Of course, forced plans and plan guides can both fail. You may also run into a “morally equivalent plan” in Query Store that looks quite morally ambiguous.

Recently while working with a client, we came across just such a scenario. And of course, of the many reasons why a forced plan might fail, this one was just a… general failure.

The fix we came up with was to track down the compile values for that nice middle ground plan, and use OPTIMIZE FOR to push that plan shape into reliably reality.

Territory

Here’s a close enough approximation to what we did, with a good-enough demo. Trying to get a more realistic one was hard without a much more complicated schema, which the Stack Overflow is not.

An index!

CREATE INDEX 
    p
ON dbo.Posts
    (OwnerUserId)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

And a procedure!

CREATE OR ALTER PROCEDURE 
    dbo.OptimizeForStuff
( 
    @ParentId integer = NULL, 
    @PostTypeId integer = NULL,
    @OwnerUserId integer = NULL
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    
    SELECT TOP (1000) 
        p.*
    FROM dbo.Posts AS p
    WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
    AND   (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
    AND   (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
    ORDER BY 
        p.Score DESC, 
        p.Id DESC;
END;

All good so far, even if it does have an air of laziness.

Darwin

The problem was that when the query executed something like this:

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = 22656, 
    @ParentId = NULL, 
    @PostTypeId = 2;

It got a good-enough fast plan:

But when the query executed in almost any other way:

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = 8, 
    @ParentId = 0, 
    @PostTypeId = 1;

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = 1349, 
    @ParentId = 184618, 
    @PostTypeId = 2;

It got this sort of lousy plan.

Even Worse

When stranger executions came along, things got way worse!

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = NULL, 
    @ParentId = 0, 
    @PostTypeId = 1;

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = NULL, 
    @ParentId = 184618, 
    @PostTypeId = 2;

We need to avoid all of this.

Step Up

Here’s what we did (again, round about) to make sure we got the generally good plan across the board, without failures!

CREATE OR ALTER PROCEDURE 
    dbo.OptimizeForStuff
( 
    @ParentId integer = NULL, 
    @PostTypeId integer = NULL,
    @OwnerUserId integer = NULL
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    
    SELECT TOP (1000) 
        p.*
    FROM dbo.Posts AS p
    WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
    AND   (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
    AND   (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
    ORDER BY 
        p.Score DESC, 
        p.Id DESC
    OPTION
    (
        OPTIMIZE FOR 
        (
            @OwnerUserId = 22656,            
            @ParentId = 0, 
            @PostTypeId = 2
        )
    );
END;

Which gets us the original fast plan that I showed you, plus faster plans for all the other executions.

For example:

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = 8, 
    @ParentId = 0, 
    @PostTypeId = 1;

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = 1349, 
    @ParentId = 184618, 
    @PostTypeId = 2;

Go from 1.5 seconds to ~300ms:

And the two outlier queries improve quite a bit as well (though neither one is exactly great, admittedly).

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = NULL, 
    @ParentId = 0, 
    @PostTypeId = 1;

EXEC dbo.OptimizeForStuff 
    @OwnerUserId = NULL, 
    @ParentId = 184618, 
    @PostTypeId = 2;

In all cases, the plan is generally better and faster, and sharing the plan across (though imperfect for the outliers) tamped down the extreme performance issues that were there before with attempts at forced plans.

Posit Hell

While I’m no great fan of OPTIMIZE FOR UNKNOWN, using a specific value can act like a less faulty version of plan forcing.

You shouldn’t pull this out every time, because it is a bit of duct tape to keep a sinking ship above water, but in oddball cases, it can be a quick and rather painless fix.

At some point, better solutions should be explored and implemented, but emergencies don’t generally allow for the greatest care to be taken

Thanks for reading!

Going Further

Compiles! Recompiles! Stored Procedures! Temp Tables! Extended Events! In SQL Server

Posted on May 21, 2024January 22, 2026 by Erik Darling

Compiles! Recompiles! Stored Procedures! Temp Tables! Extended Events! In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of stored procedures, temp tables, compiles, recompiles, and query hints—essentially packing as many fascinating topics as a human hand can hold. Using `spHumanEvents`, an extended events wrapper I developed to simplify tracking for SQL Server users, we explore how to monitor and manage these processes effectively. By running a series of queries with different hints in a loop, we uncover the nuances between recompiles and compiles, highlighting the potential benefits of using the `KEEP PLAN` hint to stabilize query plans across multiple executions. This exploration not only sheds light on common issues but also offers practical insights for optimizing stored procedures that rely heavily on temp tables.

Full Transcript

Alright, Erik Darling here with Darling Data and this happy, friendly, amazingly kind video, this community spirited video, we’re going to talk about a cacophony of fascinating things. Namely, stored procedures, temp tables, compiles, recompiles, and query hints. It’s as many things as I can fit on a hand. If I were blessed by any of the gods with additional fingers, we would talk about more things today, but I’m all out of fingers, so you get what you get. In order to track compiles and recompiles, we’re actually going to do, we’re going to look at this three different ways. One of those ways is not going to be query store because query store is somewhat uninteresting in this way. We’re going to look at, we’re going to use spHumanEvents in two different ways. We have one event up here to track compiles. We have one event up here to track recompiles. Alright, and then we have this query plan clash, plan cache query that we’re going to use to track the same thing in the plan cache. Cool, great, wonderful. What are we tracking exactly? Well, it’s really better if I start this loop off and then we talk through exactly which specific things we’re dealing with here.

So what I need to do is in very quick succession. Not too quick. I gave myself a little room to wiggle. I do like having room to wiggle. I’m going to start this off here. spHumanEvents, that’s my store procedure that I use to make extended events a little bit easier on the average SQL Server user because Lord knows Microsoft didn’t make it easy. And as you all know, I love and care about you far more than Microsoft ever will. They’re a nameless, faceless, conglomerate bunch of people who say no. Me, I say yes. Darling Data, we say yes to you.

So I’ve got a store procedure and this is the very store procedure that’s running in the loop that’s executing right now, if you would believe that. And what this store procedure does is it takes some parameters, obviously. That’s what store procedures typically do. They take a parameter. It’s like we here at Darling Data will take a drink. Store procedures will take a parameter. We create a table called filtered posts. We insert into that table the results of this query where our wonderful parameters are used, where SQL Server drinks greedily from our parameters. And then we run the same parameters. And then we run the same query, essentially, in four different ways. So the first way that we run this query is with no hints whatsoever, just looking for where the score column in our temp table is greater than zero.

And notice that I’m doing the greater than select zero thing here. The reason that I’m doing that is to avoid simple parameterization, not because it actually interferes with this demo, but just because a lot of people see it and say, oh, that looks weird. What is that? And then everything gets derailed, hopelessly derailed. We have another iteration of the same query, except this one has a recompile hint on it. So this one we’re explicitly saying, SQL Server, please recompile this plan. Tear down that old plan. And then we have two more queries that we run. One of them uses the keep plan hint right here. And I wanted to use this to see if keep plan would get us anything in this situation.

Not to jump too far ahead, but keep plan really seems to work better for like actual tables and like an actual schema, not like 10 tables. And then we have keep fixed plan down here. All right. Wondrous, wonderful, amazing. You believe that I do this for free for your benefit. You believe that the things that I do for you, all the typing that I do for you, brothers and sisters. And so, well, I mean, there’s our loop, right? Our loop is finished. And if we go get the results from this thing back and we look at what happened, we can see this loop ran 21 times with post type ID one.

And then another 21 times with post type ID two for a total of 42 trips to that store procedure, right? What this loop does is what as long as post type is less than three, and that’s not a weird heart. I don’t play those games. I’m married to the data. I can’t go making hearts at every single thing in SSMS.

And then while end date is less than this, we do this and we increment our dates by three months at a go. And then when we finally reach our limit here, then we reset our dates and we set post type ID one higher. And only on that trip around do we raise our loop counter.

So 21 times for post type ID one, 21 times for post type ID two, which is a total of 42 trips to that store procedure. I did not have 42 drinks. You are watching me. There’s no way I could have had 42 drinks during the execution of that store procedure.

Though Lord knows sometimes I wish I could. If we come over here and we look at the compiles, we are going to see a whole bunch of statistics gathering in tempDB. And if we expand this a bit, we’ll see that this is all coming from the filtered post table in our store procedure.

All right. This is all compiling filtered post statistics. And we have 35 rows of that, I guess. And we have most of these just have one compile, but this one at the top has eight compiles.

I’m not sure why this one thinks it’s so special that it can go and compile eight times in total, but it went and did it anyway. And if we look at the results of the recompile extended event that we had in here, look at what we’re going to see. This is actually interesting to me.

I found this part fascinating. Utterly fascinating. You might be wondering, Eric, why did you find this utterly fascinating? Well, let’s look at what we have some recompile causes in here, right?

For two of them, we have statistics changed. For one of them, you can probably guess which one this is. We have option recompile requested, right?

Which is great. Love the capitalization there. Capital O option, all lowercase recompile. And for the two down here, we have deferred compile. All right.

All right. Cool. Those all happened in Stack Overflow 2013, which is exactly where we executed our store procedure. They were all from the store procedure that we’re talking about. But if we come over here just a wee little bit, and I have no idea why one of them has that little extra less space in it.

A little strange. And we’ve got to do a little bit of work to get this expanded out to where we want it. Because what we want to see are which compile, which query text with which hints or no lack of hints, ended up with all these compiles and all this stuff going on.

And now let’s slide over this way. Since we’ve got this all framed up nicely here, we have our first query, which is no hints, which had 42 total recompiles and almost 10 seconds of recompile CPU, on average taking 232 milliseconds to recompile this query.

All right. That’s a fairly crazy thing, right? And remember, the recompile cause, and this is statistics changed.

Down here, where we have our keep plan hint, we still ended up with 42 total recompiles, but we just used far less recompile CPU on that. Almost nothing there.

This is pretty wild that just like a regular query requested that required, not requested, required. Didn’t just say, can I have like 10 seconds of recompile CPU? And SQL Server was like, yeah, I guess.

It was just like, no, I need to do this. If I don’t get this, I’m going to go crazy. And down here at the very bottom, we have our two other queries, which only recompiled once, which probably means they just kind of compiled once or something, I guess.

I’m not entirely clear on all of the inner workings of how Extended Events tracks these things. Nor do I have the moxie to care too much about all of that. So, you know, that’s fun.

Sorry if you don’t like it. But there’s only so much time in a day, and there’s only so much free I can give. So these two here, the keep fixed plan did like no work, and the insert select into the temp table did absolutely no work. Now, if we come over here and we run our plan cache query, we’re going to see nearly the same thing, except it’s going to be a little misleading.

I mean, sure, these three up here have crazy high plan generation numbers on them, probably much higher than they should. Well, I’m not going to say much higher than they should be. Just like 129.

We only executed the thing 42 times. I don’t know. I don’t know. Pretty wacky stuff.

Pretty wacky stuff. But what’s misleading is that these queries all look like they only executed once. So that’s because of all the compile, recompile, stats change, go get stats, go make a new plan, kick out the old plan, we don’t need it anymore, get the new plan in here. And all that stuff, which is, I don’t know, can be aggravating.

To be perfectly honest with you, that stuff can be quite aggravating. And the two queries down at the bottom, this is just kind of to show you that the plan cache does agree mostly with the extended event recompile stuff that we saw, where these queries both have 42 executions, right?

And they did very little on the recompile tip. So the moral of the story here kind of is, if you have stored procedures, and those stored procedures feed data into temp tables, and you’re having trouble with, like, high recompilations, and you want to, and you, like, you’re, like, confident and comfortable in the query plan that you’re getting across all those executions, which can be, granted, tough to figure out if you’re constantly recompiling.

But if you’re getting the same plan over and over again, right, and you want to reduce all that compile overhead, right? You have stored procedures that execute a ton, they use temp tables, you put, like, a relatively stable amount of data in those temp tables, and you’re just, like, SQL Server, just use a decent plan, just reuse it, I don’t care, then the keep fixed plan hint can be pretty useful to tamp down the recompiles and potentially take a lot of the stress and strain out of all the many, many executions of that stored procedure.

Will that be perfect in every case? Of course not. But we live in a complex, data-driven database, data face, data world, and it’s up to us to decide when it’s appropriate to do these things. It’s up to us to figure out when these things are causing problems and come up with a reasonable fix for them.

In this case, I would recommend for the query in that stored procedure that we stick with the keep fixed plan hint because that would tamp down on the recompilations, and it would give us a nice stable execution plan that not only would we be able to reuse, right, but we would be able to track exactly how that plan is performing over time.

If we find that that plan is not performing so well, we could, you know, recompile the stored procedure and let SQL Server come up with a new plan the next time, and maybe that one will be better. We always, we pray that SQL Server will make good choices, don’t we?

Lord knows we don’t. It’s up to SQL Server to make these good choices. So, this is just kind of a fun little demo that I put up with while investigating something else entirely, but I thought it was worth sharing.

If you’re not familiar with SP Human Events, the link to my GitHub repo where it lives will be in the show notes, and you’ll be able to go and install it in all of your servers and run it in all its glory and be able to track down these interesting problems the same way I do, this professional stored procedure-y way.

So, anyway, that’s good for today, I think. I hope you enjoyed learning about compiles, recompiles, temp tables, stored procedures, and extended events. I don’t know if that was the same five from before, but again, we’ve hit the magic number, and this is also how I wave goodbye.

So, if you enjoyed this video, I do like thumbs-ups and… Family-friendly thumbs-ups. Do not stick our thumbs where they don’t belong.

I like comments, and I like subscribers. So, if you like this and you like me, then subscribe to the channel and you get more of me and more of this. And it’s kind of a win-win for everybody, because then I get what I like, you get what you like, and we’re all the happier for it.

We’re all better off, aren’t we? So, as usual, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. And I will see you in another video, I guess probably tomorrow, when I’ve thought of something else to talk about, which amazingly I always do.

So, thank you for watching.

Going Further

A Mild Annoyance With MERGE Statements And Triggers

Posted on May 20, 2024May 20, 2024 by Erik Darling

I’m No Expert

I will leave the finer points of the problems with MERGE statements to the Michaels and Aarons of the SQL Server world.

This is just a… But why? post about them, because I’m honestly a bit puzzled by this missing implementation detail.

To get us to the point, I’m going to use a code snippet (with embellishments) from Aaron’s post here.

We’ll be starting with this table and trigger from the linked post, with a couple small tweaks to satisfy my OCD:

CREATE TABLE 
    dbo.MyTable
(
    id integer
);

INSERT 
    dbo.MyTable 
VALUES
    (1),
    (4);

CREATE OR ALTER TRIGGER 
    dbo.MyTable_All
ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    IF ROWCOUNT_BIG() = 0 RETURN;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;
  
    PRINT 'Executing trigger. Rows affected: ' + RTRIM(@@ROWCOUNT);
    
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am an insert...';
    END;
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am an update...';
    END;
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am a delete...';
    END;
END;

After all, one of the best ways to make sure you get code right is to copy and paste it from the internet.

Quiet On The Set

As much as we all love to dunk on MERGE, like cursors, heaps, and UDFs of various types, they did give you some neat options with the OUTPUT clause, like the $action column, and the ability to get columns from other tables involved in the query. You can’t do that with a normal insert, update, or delete when using the OUTPUT clause, though I think it would be cool if we could.

Working a bit with the code linked above, here’s an expansion on it showing the additional OUTPUT capability, but this is also where my annoyance begins.

BEGIN TRANSACTION
    DECLARE
        @t table
    (
        action varchar(6),
        i_id integer,
        d_id integer,
        s_word varchar(5)
    );

    SELECT
        mt.*
    FROM dbo.MyTable AS mt;

    MERGE 
        dbo.MyTable WITH (HOLDLOCK) AS Target
    USING 
        (
            VALUES
                (1, 'one'),
                (2, 'two'),
                (3, 'three')
        ) AS Source (id, word)
    ON Target.id = Source.id
    WHEN MATCHED 
    THEN UPDATE 
           SET Target.id = Source.id
    WHEN NOT MATCHED 
    THEN INSERT
           (id) 
         VALUES
           (Source.id)
    WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
    OUTPUT
        $action,
        Inserted.id,
        Deleted.id,
        Source.word
    INTO @t 
    ( 
        action, 
        i_id, 
        d_id,
        s_word
    );
    
    SELECT
        t.*
    FROM @t AS t;

    SELECT
        mt.*
    FROM dbo.MyTable AS mt;
ROLLBACK TRANSACTION;

You likely can’t guess what I’m sore about just looking at this, because this isn’t what annoys me.

This is all fine, and rather a nice showing of capabilities for an oft-maligned (by myself included) bit of syntax.

The problem is really in the trigger.

What’s Wrong With Triggers?

If you work with trigger code enough, you’ll get used to seeing:

Catch all triggers that do a lot of gymnastics to differentiate insert from update from delete
A few separate triggers to catch each modification type separately, and still do some checking to verify

The problem is that in any trigger, the $action column is not directly exposed for use to determine the action of a MERGE statement.

Sure, you can add a column to a table to track it, or some other hacky workaround, but I consider it a quite poor design choice to not have the $action column as a part of the Inserted and Deleted virtual tables.

Having it there would also benefit generic modifications that are captured by triggers in some manner to make the absolute type of modification quite clear to query writers.

Thanks for reading!

Going Further

Join @bobwardms At SQL Saturday Boston For The Azure Workshop for SQL Professionals (@NESQLServer)

Posted on May 17, 2024May 16, 2024 by Erik Darling

All’s Well That Friends Well

The nice folks at the New England SQL Server User Group (w|t) had me in town a week ago to present my workshop, The Foundations Of SQL Server Performance.

We had about 40 folks show up — which ain’t bad for a random Friday in May — including one attendee from Nigeria.

Not just like, originated in Nigeria. Like, flew from Nigeria for the workshop. That’s probably a new record for me, aside from PASS Precons where folks are already headed in from all corners.

Speaking of PASS Precons — me and Kendra are double teaming your Monday and Tuesday — have I mentioned that lately?

As a favor to some dear friends, here’s a promo code to get $125 off Bob’s Friday Precon, The Azure Workshop for SQL Professionals.

The precon takes places on October 4th, and the SQL Saturday is October 5th. You can register separately for the SQL Saturday only here.

If you want to catch Bob’s Friday workshop, use the QR code below, or register here and use the code PPP50 to get your discount.

Thanks for reading!

Going Further

Two Ways To Tune A Slow Query In SQL Server

Posted on May 16, 2024May 21, 2024 by Erik Darling

Like All Assumptions

You and Me might feel like the lower back end of a thing if we’re tuning a query that has other problems. Perhaps it’s running on one of those serverless servers with half a hyper-threaded core and 8kb of RAM, as an example.

When I’m working with clients, I often get put into odd situations that limit what I’m allowed to do to fix query performance. Sometimes code comes from an ORM or vendor binaries that can’t be changed, sometimes adding an index on a sizable table on standard edition in the middle of the day is just an impossibility, and of course other times things are just a spectacle du derrière that I’m allowed to do whatever I want. You can probably guess which one I like best.

This post is about the two other ones, where you’re stuck between derrière and ânesse.

For the duration of reading this, make the wild leap of faith that it takes to embrace the mindset that not everyone who works with SQL Server knows how to write good queries or design good indexes.

I know, I know. Leap with me, friends.

The Query And Execution Plan

Here’s what we’re starting with:

SELECT TOP (10)
    DisplayName =
       (
           SELECT
               u.DisplayName
           FROM dbo.Users AS u
           WHERE u.Id = p.OwnerUserId
       ),
    p.AcceptedAnswerId,
    p.CreationDate,
    p.LastActivityDate,
    p.ParentId,
    p.PostTypeId,
    p.Score,
    p.CommentCount,
    VoteCount =
        (
            SELECT
                COUNT_BIG(*)
            FROM dbo.Votes AS v
            WHERE v.PostId = p.Id
        )
FROM dbo.Posts AS p
ORDER BY
    p.Score DESC;

And resulting plan:

We can surmise a few things from this plan:

If there are good indexes, SQL Server isn’t using them
That hash spill is some extra kind of bad news
Spools remain a reliable indicator that something is terribly wrong

Okay, so I’m kidding a bit on the last point. Sorta.

The Query Plan Details

You might look at all this work that SQL Server is doing and wonder why: With no good, usable indexes, and such big tables, why in the overly-ambitious heck are we doing all these nested loop joins?

And the answer, my friend, is blowing in the row goal.

The TOP has introduced one here, and it has been applied across the all of the operators along the top of the plan.

Normally, a row goal is when the optimizer places a bet on it being very easy to locate a small number of rows and produces an execution plan based on those reduced costs.

In this case, it would be 10 rows in the Posts table that will match the Users table and the Votes table, but since these are joins of the left outer variety they can’t eliminate results from the Posts table.

The row goals do make for some terrible costing and plan choices here, though.

This all comes from cardinality estimation and costing and all the other good stuff that the optimizer does when you throw a query at it.

The Query Rewrite

One way to show the power of TOPs is to increase and then decrease the row goal. For example, this (on my machine, at this very moment in time, given many local factors) will change the query plan entirely:

SELECT TOP (10)
    p.*
FROM
(
    SELECT TOP (26)
        DisplayName =
           (
               SELECT
                   u.DisplayName
               FROM dbo.Users AS u
               WHERE u.Id = p.OwnerUserId
           ),
        p.AcceptedAnswerId,
        p.CreationDate,
        p.LastActivityDate,
        p.ParentId,
        p.PostTypeId,
        p.Score,
        p.CommentCount,
        VoteCount =
            (
                SELECT
                    COUNT_BIG(*)
                FROM dbo.Votes AS v
                WHERE v.PostId = p.Id
            )
    FROM dbo.Posts AS p
    ORDER BY
        p.Score DESC
) AS p
ORDER BY
    p.Score DESC;

You may need to toggle with the top a bit to see the change on your machine. The resulting plan looks a bit funny. You won’t normally see two TOPs nuzzling up like this.

But the end result is an improvement by a full minute and several seconds.

Because the inner TOP has a bigger row goal, the optimizer changes its mind about how much effort it will have to expend to fully satisfy it before clenching things down to satisfy the smaller TOP.

If you’re only allowed quick query rewrites, this can be a good way to get a more appropriate plan for the amount of work required to actually locate rows at runtime, when the optimizer is dreadfully wrong about things.

The Index Rewrite

In this case, just indexing the Votes table is enough to buy us all the performance we need, but in my personal row goal for completeness, I’m going to add in two indexes:

CREATE INDEX 
    v 
ON dbo.Votes 
    (PostId) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE INDEX 
    p 
ON dbo.Posts 
    (Score DESC, OwnerUserId) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Going back to the original query, we no longer need to play games with the optimizer and pitting TOPs against each other.

This is obviously much faster, if you’re in the enlightened and enviable position to create them.

Perhaps you are, but maybe not in the exact moment that you need to fix a performance problem.

In those cases, you may need to use rewrites to get temporary performance improvements until you’re able to.

Thanks for reading!