Tag: Parallelism

How I Used Claude Code To Refactor My Stored Procedures

---

Video Summary

In this video, I dive into my recent experience using Cloud Code from Anthropic as part of my work on Darling Data. Typically, my videos focus on T-SQL, query performance, and database optimization—topics that are near and dear to the hearts of SQL Server enthusiasts. However, in today’s episode, I explore a different aspect by sharing how Cloud Code helped (and sometimes hindered) me with tasks like refactoring stored procedures and generating README files for my GitHub repository. While some aspects were handled seamlessly, particularly with formatting code and creating consistent READMEs, other areas required more manual intervention. For instance, when it came to refining the SP Health Parser, Cloud Code was adept at condensing repetitive checks into templates, making the code cleaner and more maintainable. However, there were challenges in certain sections of Quickie Store where the end of the stored procedure was a maintainability nightmare, requiring extensive debugging and sanity-checking post-refactoring. Overall, this experience provides valuable insights into both the strengths and limitations of AI-assisted development tools in real-world scenarios.

Full Transcript

Erik dishwaskeldarling here with Darling Data. And in today’s video, we’re going to talk about something a little bit different than what I normally talk about in my videos. Usually we’re talking about T-SQL, query performance, indexes, you know, best practices, stuff like that. In today’s video, I want to talk about something that I was doing kind of over the last couple of weeks, where I was using a new offering from the Anthropic Cloud people. called Cloud Code. It’s actually still a beta thing, to refactor and some other stuff with the store procedures that I offer for free to, like, analyze SQL Server performance. Now, the tasks that I wanted it to do were things like, like, refactor big chunks of code, especially very redundant stuff. You know, create, like, readme files, like stuff that I’m really bad at doing, like the admin work of the GitHub repo. Like, making sure that everything has readme files and they’re up to date. And then also, like, over the last couple weeks, I was working on a brand new store procedure called SP Index Cleanup. This is where Cloud Code was somewhat less successful. It was good. It was good for some things in the development of it, but not very not, very much not good at other things. So I want to talk through all that. So I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that.

You know, the first thing I tried was this an application called Cursor and that thing just choked and died on my code immediately. Like, like it couldn’t under like just didn’t get any of what I was trying to do. And worse was like the few things that like it did pick up on. I was like, cool, can you fix this? And it was like, I can’t find the file. And I’m like, but you just read from the file. Like you, you, you see the code, like you just printed the code up for me. And it was just like, yeah, but I, I can’t find the file to write to it. And I was like, huh, that is that normal. And, and, and Cursor was like, no, I think I have a bug. And I was like, oh, this is, this is a strange self-awareness for a computer program, but okay, we’re just gonna, just gonna move on from here. So I tried out Claude Codes. I was very interested in it. And I’m going to talk about kind of what it did, where it was successful and where it wasn’t. If you would like to support this channel, and I want to bring up right now, Claude Code is not free. You know, like there are a lot of LLMs that you can have basic conversations with for free. It doesn’t cost you any money. Claude Code requires like an API, like API access and credits. And there were a few times me sitting there working with Claude Code where I was just like, oh, my credit card has been charged. What was that? Oh, another 50 bucks to Anthropic.

Because you have to keep refilling credits to keep using it. If you run out of credits while you’re doing something, Claude’s like, give me my $2, right? So if you want to support my Claude Addiction, excuse me, if you want to support my Claude Addiction, there’s a link down in the video description for you to do that. If you spent all your money on Claude Code credits as well, you can like, you can comment, you can subscribe. And if you want to ask me a question privately that I answer publicly during my Office Hours episodes, you can do all that. If Claude Code is not quite good enough to take the place of all of your DBAs and developers, and you still need some help with SQL Server, you can still hire me for not for Claude credits, but you can pay me real money to do any of this stuff, health checks, performance analysis, hands on server query index tuning, dealing with SQL Server performance emergencies, and of course, battering your developers and deep frying them in SQL Server knowledge until there are fewer performance emergencies for you generally, which is always a good thing.

If you would just like to get some training from me, I have about 24 hours of it. It’s all SQL Server performance tuning training, and you can get that for about $150 once you stick that discount code onto the pile. Upcoming events, the calendar has finally filled out, and there’s a lot to talk about. SQL Saturday New York City 2025 is taking place May the 10th. Redgate is going on tour with some mini-pass events in New York City, Dallas, and Amsterdam.

Those will be in August, September, and October. All the dates are right over there. And of course, Past Data Summit is taking place November 17th to 21st in Seattle, Washington. I guess that one was always out in the air, but, you know, tis the season to start making sure everyone knows these dates, has them in their calendars, and buys the appropriate tickets.

So, with that out of the way, let’s talk about Claude Code. I think I’m going to start over here, because this one’s a little bit easier. This is the…

So, like, when you start working with Claude Code, like in a GitHub repo or something, or wherever you work with code, one thing that you can do is feed it a bunch of, like, rules to follow. So, like, when Claude first started, like, spitting out code and doing stuff, the formatting was a disaster.

So, I was like, we got to work on this. So, one of the first things I did was create this Claude.md file in my GitHub repo, so that Claude knows, like, how I want my queries to look. So, I gave it all my very particular rules about comments and naming conventions and query structure and, like, join order and all this other stuff.

And, like, how to format things so that when it spits code out to me, I don’t have to spend another, like, hour reformatting everything that it gives me. So, there’s this whole long file in here where, like, you know, it’ll, like, it’ll, like, read through this at the beginning of a session. Or if I’m like, hey, you’re messing up, check this file again, and it will, like, reformat stuff the way I like it.

It gets pretty close most of the time. It’s, you know, still not perfect. There’s still stuff that I have to, like, adjust a little bit because I’m very particular about things.

But, in general, it’s pretty good. So, after… So, with that in there, the other task that I set it to with my GitHub repo, again, the admin work that I stink at was, like, making sure that all the readme files were, like, consistent and up-to-date.

And, like, everything had one because there were a bunch of folders in here where there was nothing. Some of them still don’t have them, and that’s okay. They’re ones that I didn’t want them for.

But they’re, like, the main store procedures and stuff and, like, install all, like, all of these things have readmes now so that you can, like, figure out, like, what the procedure does. There’s, like, you know, some examples of how to run it, things like that.

So, everything now has a nice consistent readme file. And Cloud Code was great at that. Cloud Code is actually much better at, like, just spitting out good markdown than I am. One thing I was really happy with was, like, when I did the human events one, I was able to tell it, like, hey, like, there are two store procedures in here that people should care about.

Like, let’s branch them at the top so that people can get right to the one they care about. So, like, it just, like, was able to do this stuff very easily and quickly. And it added a picture in there for me.

And I was very happy with this part of it. Like, this was great. The store procedure stuff was a little bit different. Let’s start off talking about where it was mostly successful. And then we can talk about where stuff was maybe not as great.

So, like, one of the first things that I had it do in SP Health Parser, when I first wrote SP Health Parser, I was, like, really just trying to get it to a point where it worked. And it did what I wanted.

And the results that it returned were, like, you know, sensible. Like, maybe not perfect, but, like, it got the results out. I could read them. Like, they were correct. Stuff like that.

But, you know, this thing reads from the system health extended event, which is all XML, parses all the XML out, and does a lot of stuff. And, like, along the way doing that, I didn’t, like, I was more focused on making sure that I just had everything. Like, just please, God, just work.

Like, then I was on, like, making sure that the code was, like, reusable, maintainable, like, not insane. Like, just adding length to the code where it wasn’t necessary. So, there was a lot of stuff in there I was doing that was very repetitive that could be condensed down.

So, one of the first things I asked Claude to do was, like, hey, like, I do this check over and over and over again to look at this one function that reads from the extended event file called sys.fnxe file target read file. It rolls right off the tongue because in SQL Server 2017, they added this column called timestamp underscore utc. So, like, it’s a lot easier to filter on that than it is to, like, parse the XML and filter on the event date in there, which I still have to do for, like, the 2016 and earlier stuff.

But there was, like, this branch in the code for, like, hey, if we do this, then we do this. Right? But it was, like, the whole query written out.

So, one thing I wanted to do was, like, template some of the dynamic SQL so that I could just, like, based on what I find here, like, replace things and just do the right, like, run the right query, like, over and over again. So, that was one of the first things I had to do. And it was pretty good at this.

So, like, and one thing that’s cool here is that in, like, Azure, well, I don’t know about Azure SQL DB because Azure SQL DB doesn’t have the system health extended event. Again, Azure SQL DB is stupid. It’s, like, running SQL Server in safe mode.

You don’t have access to a lot of stuff that you would need to do, like, good analysis on a regular SQL Server. Microsoft’s doing it for you. Anyway.

But, like, managed instance where you can do this stuff. And, like, on-prem SQL Server, there’s a bug report about the timestamp UTC column. It’s fixed in, like, the newer versions of SQL Server because, like, in the bug report it says this will be fixed in the next major release.

So, that’s not, like, NDA 2025 stuff. Don’t yell at me about that. But, like, prior to that, you had to, like, convert the column to a date time 2 before you could filter on it.

I’ll show you what the query looks like in a minute. But, like, I wanted to make that less annoying. So, I had to create this timestamp UTC mode local variable, which is a tiny int, not a bit.

And, basically, what I do is it starts at zero. If we’re on SQL Server 2017 or up, then we add one, right, because that column exists. But we have to use the convert where clause to do anything with it.

And then, excuse me, if the product version is greater than SQL Server 2022 or we’re using managed instance, then we add another one to that, right? So, if this number is, like, zero, then we use the old, like, event date from the XML filtering. If it’s one, then we use the 2017.

And if it’s greater than one, then we use the column without the convert on it. So, that was, like, one of the first things I had to do. It was pretty good at that.

Like, everything generally worked out. And then, the other stuff that I had to do was create templates for Dynamic SQL where I could easily do some, apply this stuff. So, the templates all look something like this, where we have, like, the insert list and, like, object name stuff here.

And then, we have the time filter that I want to use here. And then, the cross apply that I want to use here because that changes, too, depending on how the query ends up. There’s this thing, but this isn’t really what I want to show you.

This is more in line with what I want to show you here where, depending on how the timestamp UTC mode thing works or looks, we do different stuff, right? So, this will do this version of the cross apply for 2017 plus. We do this version of the cross apply.

And then, if it’s, like, a version where this isn’t fixed, oops, it’s a version where this isn’t fixed. Then, we do the convert date time offset 7 before we do the filtering. And then, if it’s the version where it’s fixed, then we don’t need to worry about that.

And then, you know, like, you run the replace on stuff in here, and then you have this set up to run over and over and over again. So, once you get into, like, the actual checks, this is the logging to table stuff. We don’t need to look at this again.

We need to get down a little bit lower to where the queries start running. So, what it does now is rather than, like, rather than that query with the cross apply that we just looked at being written out over and over and over again for every different event, this thing runs a little bit differently now. We fire up a cursor.

We work off a table variable that has everything in it that I want to loop over. I think that’s just up here. Yeah.

So, this is the collection table variable that I use. So, this figures out exactly, like, which, like, area to check in, the place that I get it from, and the temp table that it goes to, right? It’s in, like, the column that I need to select from and insert and everything.

So, all this data is here now. And now, when I loop over stuff down in this cursor, I just, like, generate that insert. I run a replace on this collection SQL local variable to replace the object name and the temp table I’m inserting to and the insert list for that table.

And then I just, like, that just runs in a loop and does the insert over and over again. And, of course, like, with debug and stuff, I can print that out. And then within the cursor, I just, like, update this to say which things are done, which is important because some of these collection areas work off the same thing, right?

So, like, SP server diagnostics component result, you’ll see that a few times in here. I don’t need to run this once for each one of those. All I have to do is collect this once and then parse the XML out from that.

So, it’s very useful to just have, like, hit that once. So, I don’t need to hit this over and over and over again. So, it was very good with helping me sort of condense this down.

Like, again, not perfect. There were things that I had to, like, fix and sanity check and make sure it still worked afterwards. But, like, as far as just, like, setting up, like, the bones of things, it was very good at that.

The other thing that I had to do was in Quickie Store. So, in Quickie Store, the end of the store procedure was a nightmare, like a maintainability nightmare. The output, and it was purely for, like, the output of Quickie Store, right?

So, there were, there’s two options. There’s expert mode, which returns a bunch of extra columns in the main result set and additional tables. And then there’s format output.

And format output, like, you have big numbers. It puts commas into the numbers for you. By default, expert mode is set to zero, but format output is set to one because, like, I don’t want to have to remember to turn format to one every time I run this, which is what I do.

So, I had it create two things for me to drive the final result. They’re both table variables. And this is actually a good use for table variables, right?

So, I actually had it use table variables for something table variables are good for. And what I, like, and this took a few iterations. I don’t want to pretend like it just spit this thing out perfect on the first go. Like, there was definitely stuff that needed tweaks.

But, like, I could type in the tweaks that I needed it to make, and it would make the right adjustments. The really cool thing, too, is that, like, it wasn’t just returning code to the screen that I had to go and, like, paste into the procedure and do stuff with. It was, like, actively editing the files that were in my GitHub repo.

So, I could just, like, you know, hit submit to GitHub and, like, push stuff around. It was, it’s a little tricky right now because Cloud Code doesn’t run on Windows. At least, I couldn’t figure out how to get it to run on Windows.

I don’t think it does. But, so, like, I, like, like, sitting at my Mac over here where I have the Cloud Code terminal window running. I’m going to, like, type stuff there, push it up to GitHub, pull it down on GitHub on Windows, then run the store procedure and, like, make sure that stuff works. So, there’s a little bit of, like, crosstalk back and forth right now.

This can be a little bit awkward if you’re, if you don’t have, like, a Mac to, or something else to use this on. But I figured it was worth it for the experiment. So, it creates these table variables.

And, like, it was, like, kind of amazing at, like, like, analyzing the select list, figuring out which temp tables things came from, which columns were expressions, and, like, which conditions those, like, those columns should show up under. And, like, making a table variable to store all this stuff. So, like, all of these things have, like, like, a format pattern.

And, like, if they need one, so, for, like, down here, like, all of these, all these columns in here have the option to format them. And, no, that’s the last execution time. I’m looking for, like, duration.

So, like, when I run the format function, we use that n0, right, just to put commas into, like, with no decimal point. That’s a n0 means zero decimal places. If you can do, like, n1 or n2 or n5 or whatever if you want decimal places.

n0 is just, like, a number formatted with commas that, or whatever local thing you use. In France, it’s spaces, I found out. But, yeah, you can, like, it just put all this information in there for me.

And, like, at first, like, it was just numbering these sequentially. And I was, like, hey, if I need to insert stuff in between, can you put this in, like, can you use a different numbering scheme so that, like, if I need to put stuff in, here or reorder this, it’s not a nightmare of me having to renumber everything.

So, and it was pretty good at this. One thing that needed help was, like, some of the new columns weren’t perfect. But, like, it was able to get that once we started, like, once I started, like, you know, telling it, hey, this isn’t going to work under these conditions. We need a column to, like, direct it to this.

Then, like, we need another, like, we need to, like, let, like, another level in the case expression to, you know, make sure that it, like, does these things right. So, like, one example of those would be, like, the hash totals for physical IO reads. This was one where we had to put, like, this thing in different than the other ones.

Right. So, that’s kind of fun there. But, yeah, anyway, like, it was very good with that.

And then the other thing that I had to do was, like, use a table variable to sort of deal with all the different parameters. So, the parameters that get passed in, like, indicate different temp tables get used and created and, like, stuff like that. So, this was all very useful here.

And then, like, there was a bunch of, like, sort of very repetitive code where, like, for all of these things, like, all of, like, all of the checks to include these things or, like, alternately exclude these things were all, like, very much written out very explicitly. Like, if this, this, if this, if this, if this, if this, then this. Right.

Like, I was just able to, like, have cod template all this stuff for me. So, like, one thing that was good in there was, like, let’s get down to, actually, let’s see. Let’s just do this.

So, the cursor that runs over this part does a bunch of stuff in here. And then, like, there’s just neat logic in here, right, where it’s, like, insert into, like, I could have done, like, the replace thing. But, I don’t know.

This, this, this just, this was just fine for this. Like, insert into the temp table. Insert the column list. Then run this. And then down here, like, like, even, like, made, like, the temp table part dynamic. So, if I need to include stuff, then I use include.

Otherwise, I stick the ignore on the temp table name. It was just very successful at doing all this. And then, way down at the bottom, this was where stuff got more interesting, I think. Was it dynamically builds up the column list based on this, right?

So, like, the column SQL is, like, if format output is zero, then we don’t do anything to it. If format output equals one, then when the format pattern is not null, we put wrap stuff in the format function. And, like, you know, based on this crazy where clause for stuff, then we do certain things in here.

And then, like, in here, we just insert the column list, right? So, like, we just tack the column list onto the SQL and then, like, do the rest of the from clause down here. I was able to do something fairly similar with the expert mode output, but I did not go as deep as that with it.

This, I was just like, you know what, we’re just going to do, like, because, like, with the format stuff in these queries specifically, it’s weird because, like, the columns are sort of interspersed. So, like, it didn’t make as much sense to do the table variable thing.

So, in here, it’s just, like, if format output equals one, then we, like, put this in there. Otherwise, you put this in there, and then we have this other column, and then this other column. And, like, you know, so, like, there was just a lot of, like, templating stuff that made sense, like, locally for this that didn’t make sense for the other parts.

Now, this was all the stuff that it was pretty good at. But in SP index cleanup, kind of what I tried to do with it was be like, hey, like, I need to, like, this is, like, the goal of my query. Like, what would I, like, how should I structure this to, like, give me what I want, right?

Like, what’s a good way to do this? And one of the first things I came up with was this insane cursor that just, like, never turned correct results. So, I was, like, like, I got to scrap this.

I’m going to, like, just write, like, very, like, procedural, like, like, rule check updates. And then there were, like, a lot of things that I had to fix to, like, make sure that the next update worked. Claude was a little less good at this.

It was helpful, but, like, like, it required a lot of me, like, like, looking at things, analyzing things, doing stuff. Where Claude did become helpful in this part was, like, like I said, like, coming up with, like, unit test indexes to make sure that all the rules that I cared about were being adhered to. And then one of the neat things that you can do, or rather that I did here, was I was, like, okay, like, based on the indexes you gave me.

One other thing that I had at Spitout, too, was, like, one thing that SP Index Cleanup does is, like, it ignores unused indexes for deduplication. So, like, they’re unused. We’re just going to disable them.

So, one thing that I had to do was, like, spit out queries that forced, like, a select count from the table using a specific index, right? And so, like, if there was a filtered index, it would put a where clause in there that matched the filter expression. So, like, it did a lot of cool things here.

But, like, one thing that I had, one thing I did was, like, I would take the final results of the query, like, hey, these are the actions that SP Index Cleanup is now saying I should take based on these indexes. Like, here’s all the data that I have. We could, like, paste in the table data.

Like, do the results match up with what I want? And, like, it was, like, a few times it was, like, no, no. Like, this is messed up. Like, one thing that I caught early on was, like, one thing I wanted SP Index Cleanup to do was differentiate between a column sort order and the key of the index. So, like, there was an index on reputation ascending and an index on reputation descending.

And one of the first bugs I caught using that method was that it was still saying that, like, those were duplicates when they were, like, I don’t want them marked as duplicates for safety reasons. So, it was able to actually caught that in the results output and was, like, I don’t think this rule is being followed. Like, we should fix that.

So, like, there were some, like, good parts to it. Like, some of, like, the, like, larger strategic things, it was less, it would, like, kind of go off the rails with. But there was a lot of stuff where, like, if I was able to give it very specific instructions and, like, very targeted, like, I need to do this specific thing, it was really good. Another thing that was cool with it, and this will be the last thing I say because this video is getting kind of long at this point.

One thing that was kind of cool with it is that since it’s all working in my GitHub repository, I could actually have it take, like, various things that I do in other store procedures and integrate them quickly into the new store procedure. So, like, there are things that I do in SP Index Cleanup that are repetitive to other store procedures that I do, like, to get all databases, include databases, exclude databases. And it was very easy to be, like, hey, I use this, I want to apply this pattern to this store procedure, take the stuff that I do in this store procedure and put it into this new one.

And it could just, like, go look at, like, Quickie Store or HealthParser or, like, whatever else, bring, and, like, bring in the portions of the code that I cared about. Like, it was able to, like, follow the get all databases equals one part, like, and see the, like, where that got triggered and, like, just bring all the code over. For me, that would have been hours of, like, split window pane pasting back and forth.

So, it was, like, there are some very cool things you can do with it. Writing code from scratch, maybe not just yet. It wasn’t a great experience with that, but, like, the refactoring and the tweaking the refactoring and the, like, applying patterns from one store procedure to another, that was all very good.

So, if you have, if you’re interested in cloud code and seeing what it can do for you in these sort of situations, like, you know, like, even just, like, hey, find where there’s redundancy in this code. Like, it was pretty good at identifying that stuff. But, you know, there are some times, like, you still have, like, you still have to babysit this stuff a lot and be, like, and, like, sanity check it a lot because some of the stuff it comes back with is bonkers.

Right? Like, like, it was still giving me, like, in, like, invalid columns that don’t exist in, like, select lists and stuff. And I was, like, but you can see everything.

So, you know, at this point, like, like, it was able to do some impressive things. But, again, you have to give it very specific, very targeted, like, you have to, like, basically tell it exactly what to do before it does it, like, correctly and to the spec you want. And it really helps to have, like, a list of rules that it has to follow for formatting so that it doesn’t just spit ugly-ass queries out at you.

But a lot of good, a lot of good. I think it did save me a lot of time. And it actually made it possible to do this stuff.

Otherwise, I probably wouldn’t have done some of this stuff. So, like, it was a very helpful sort of, like, companion in this adventure. But, again, there is a lot of hand-holding here still.

Not the end of the world. Like, not like you shouldn’t do it. But, you know, just be prepared to, like, you know, you’re actually teaching this thing how to do something right kind of deal. And it has a short memory and it’ll forget how to do something right the next time.

So, you do have to be careful. But, overall, pretty good time. Pretty fun experiment. It got a lot of good work done that I’m happy with. So, you know, I can’t, I can’t, I’d probably give it, like, a 7 out of 10 as far as, like, you know, nailing it type stuff.

But I think, you know, like, again, this is still in beta. So, is it, that gets better and, you know, stuff improves and I’m sure this will get better. One thing that is kind of annoying is, like, how long it takes to do some stuff.

And how many times you have to, like, hit okay for it to do something. Like, there are some times when it would spin for, like, five minutes to produce, like, five lines of code. Which I didn’t understand.

I have no idea what you’re doing. Other times, like, you would ask it to do a specific thing and it would be like, well, let me look at 50 other things. And you’re like, no, no, no, no, no. You’re not wasting time on that. I gave you this one thing to do. You don’t need to check every other store procedure and everything else.

And then there are things like, you know, like, there are some things where you can be like, don’t ask again for this session. But there are some commands where you have to, like, keep coming back and hitting okay. So, there are times when I’d be like, oh, I’m going to hit okay and go over here and do some stuff.

And then I’d look back and, like, just realize it was just sitting there waiting for me to hit okay again to do something. So, you know, again, not end of the world type stuff. Just, like, you know, kind of like, I don’t want to keep context switching to tell you it’s okay to do this.

Just do it. Like, you can’t say something like, just do this all unprompted. Like, don’t ask me any more questions.

Because it’ll say, okay, and then still ask you questions. So, you know, it is junior developer-y in a lot of ways. But it can be a very productive junior developer. So, I would suggest trying it out.

I don’t get anything from Claude Code or Anthropic or anything like that for telling you that you should probably try it out. But, you know, it’s a good time. I had fun.

Sort of. I didn’t sleep much the last couple weeks. Just getting back into a normal sleep schedule now, kind of. Because there were a lot of late nights working on SP Index cleanup. So, yeah.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in another video. After I have cut my breath. Goodbye.

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.

Recent Improvements to sp_PressureDetector

---

Video Summary

In this video, I’m Erik Darling from Darling Data, and we’re diving into some exciting updates to SP Pressure Detector. This improvement has been a long-requested feature by many of you, and after putting in the effort, it’s now available for everyone to use. The main enhancement allows logging results directly to tables, giving you detailed insights into when pressures occurred and what was happening at those times. With this capability, you can analyze historical data more effectively, making it easier to identify trends and issues within your SQL Server environment. Whether you’re a data professional or just want to support the work I do, there are multiple ways to contribute—financially through donations, engaging with my content by subscribing and asking questions during Office Hours episodes, or hiring me for consulting services to help optimize your SQL Server performance. Additionally, if you’re interested in training, I offer 75% off a comprehensive course that lasts a lifetime, providing valuable insights into SQL Server performance tuning.

Full Transcript

Erik Darling here with Darling Data. In this video, we’re going to talk about recent improvements to SP Pressure Detector. And this is an improvement that many people have asked me about and requested for a long time. And, you know, it was a lot of work, but well worth it because, you know, I love and care about you and I want you to have an easier time dealing with SQL Server stuff. So, the improvement, big improvement made to SP Pressure Detector, like a couple of the other store procedures we’ve talked about so far, is that we can log the results to tables. So, now you can figure, you can log all this stuff to tables and figure out exactly when your pressures happened and what was going on and how awful everything was.

If you like the work that I do with SQL Server and you like the stuff that I put up on YouTube, you can support it with money by clicking on it. I’ll see you in the link down in the video description. If you have no money for whatever reason, you’re a data professional with zero dollars, not even four dollars a month for a lowly consultant like myself. You can like, you can comment, you can subscribe and you can ask questions for free that I will answer on my Office Hours episodes.

If you have money, if you have like money money, and you want to hire me to consult and make your SQL Server, you know, less awful, I can do that. And I do all of these things, health checks, performance analysis, hands-on tuning of all varieties, dealing with SQL Server performance emergencies, and training your developers so that you have fewer SQL Server performance emergencies. You can also get training from me, 24, 25 or so hours of it, 75% off, that means about 150 US dollars.

And that lasts for life, that is no subscription required. So again, link to do all that stuff down in the video description. SQL Saturday, New York City, May 10th, 2025. Wow. Beautiful. Springtime in Times Square. What a great time to be alive in an office building for a Saturday. You can always just like, say you’re going and, I don’t know, go to a Mets game or something.

But with that out of the way, let’s talk about SP pressure detector stuff. Now, let me just make sure that everything from the last video in here is cleaned up. It’s not. Okay, let’s get rid of that. And so the main, again, the main improvement with SP pressure detector, like a couple of the other procedures, is the ability to log things to a table and retain that data for only as long as you care about it.

Much like the other procedures, there is some stuff that I do to prepare the results to do it. If you decide to log to a table, then I will validate things. I will make sure that all of the tables are created. I will make sure that whatever schema you want to put things in lives there. And then I will, I don’t know, create all the tables that we need.

And then down here where the queries start running, I will actually put stuff into those tables. I promise it is absolutely fantastic what happens. It is beyond compare how amazing what happens is. So let’s just go look at this thing in action, because that’s where, that’s what everyone cares about anyway, right?

So again, all my procedures have the help parameter. It gives a handy script introduction. Sometimes even some examples of how to run it. Not in this one, though. And then, of course, I say that on the one that doesn’t.

And then a handy reference to what all the parameters do. So this is all very good stuff in here. I’m going to run this with debug on and hopefully we won’t hit any errors.

It happened successfully. Now, the debug output, because we have that enable that’s going to return all sorts of stuff to us about what ended up where and all sorts of other things. But over the messages tab, we will see where we created all our tables.

We applied the retention policy. That should probably be seven days. I’ll fix that later.

And then you’ll see all of the pressure detector stuff that gets logged, right? So coming back over here, if we look at where or like rather what tables get created, it is all of the, it is all the results that you would expect to see from pressure detector. So wait stats, excuse me, file metrics, perfmon, memory counters, CPU information, queries consuming memory, queries consuming CPU, and any CPU, high CPU events.

So all sorts of stuff ends up in there. Just to give you a brief look at what that ends up looking like. If we hit refresh, we will have all these new tables.

And the wait stats table is usually the one that gets the most action. So if we just right click and say select top 1000 rows, we will get like all of the, basically the table of information that you would see when you run pressure detector, just in stored in a table. So you can, you know, again, you can put this in, well, there’s, I guess there’s two tables for sample time and collection time, but whatever.

These things happen. I don’t mess up the regular results, I think, if I did too much there, but we don’t have to talk about that. Dope.

So you get all this stuff back. And again, you can stick this into an Excel file. You can query it with DuckDB. You can do whatever you want. And you will be able to figure out what your highs and lows for SQL Server are. And that’s across all of these things, obviously, like anything that SP pressure detector would have logged when you hit F5 would end up in these tables over here.

So hopefully you find this very useful. Hopefully you will find this a beneficial improvement to the much beloved SP pressure detector. And you will be able to start figuring out your SQL Server problems a bit easier with this data.

These data’s, these datum. Anyway, that’s about it for this one. Not too much there.

The next thing we’re going to talk about is QuickieStore. So as always, I hope you enjoyed yourselves. I hope you learned something. I hope that you will use these scripts and take advantage of the table logging that these scripts now offer. And again, if you, to get this code, it’s all of my GitHub repo.

The link is in the video description. That’s also where you go to ask questions, get support, file bug reports, request improvements, yada, yada. Don’t send me emails.

I will just tell you to go to GitHub. So that’s, that’s how I roll. Anyway, 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.

Recent Improvements to sp_QuickieStore

---

Video Summary

In this video, I wanted to share an exciting improvement I made to SP QuickieStore, a tool designed for analyzing SQL Server Query Store data. While working on my GitHub repo and experimenting with Cloud Code, which was mostly successful but occasionally hit some snags, I decided to focus on enhancing the utility of SP QuickieStore. Specifically, I introduced a feature that aggregates metrics by query hash, providing a more comprehensive view of how often queries are executed and what their overall impact is. This change is particularly beneficial for those dealing with ad hoc queries that generate different execution plans but share the same query hash, offering insights beyond just the top 10 queries displayed in Query Store. If you’re interested in supporting my work or learning more about SQL Server performance tuning, you can find all relevant links and information in the video description below.

Full Transcript

Erik Darling here with Darling Data, and this is going to be a sort of short video. So I’ve been doing a lot of work on my GitHub repo yesterday. Yesterday, these like past two weeks, I’ve slept none whatsoever.

And like part of what I’ve been doing is like general repo improvements like readmes and code refactoring. QuickieStore was the beneficiary of a very large code refactoring to get rid of a lot of stuff. And as part of it, I wanted to experiment with Cloud Code, which was like 70% good.

I tried Cursor before that because everyone was talking about Cursor, but Cursor just completely like broke down on me. But I’m going to do a video about like the improvements that I made using that. It’s not going to be like a live video of me talking through that stuff because like honestly, it’s really boring like watching me type stuff into a command prompt and then watching like the thing spin and charge me 50 bucks and spin and charge me 50 bucks to do stuff.

But like I think the results of it for some things is pretty cool. The results for other stuff was way more mixed. So I’m going to talk through that in a different video.

But for now we’re going to talk about some improvements that I made to SP QuickieStore and the results. And this is actually a very, very useful change for people who have a lot of sort of ad hoc queries that hit SQL Server. So what I do in this one, and I do need to preface this and say that this is like this can be wrong because of a bug in SQL Server.

And it’s not just a bug with QueryStore. It’s a bug that goes back to plan guides. Paul White recently blogged about it where when you like create a plan guide and or like you force a plan in QueryStore, something in the SQL Server code swaps query hash and query plan hash.

So this is subject to like weird bugs that are not my fault. So if you see weird results in here, it’s not me. It’s Microsoft.

Blame them. Have them fix it. Call them up and yell at them. Get the pitchforks out. Whatever. But this is very useful for people who have lots of sort of ad hoc queries that hit SQL Server because what it does is it groups it like aggregates metrics for each query that would be in your results anyway. And like adds a column that shows you like like like like the totals by query hash, not just for like the query ID and plan ID combination that you’re looking at.

So that’s very, very useful for some people where you’ll see like way different numbers than you would see just in the regular results. Be like what this query ran once who cares. Like no, you can find find out this query ran like 3000 times and it got the same query hash and just got different query plans.

So neat stuff there. But before we get into that, let’s talk about this channel and you and me and birds and bees and butterflies and what’s that poem from Boogie Nights? Something about the slide or something.

It’s a slide, I think. Water slide. Anyway, if you like this channel and you like my content and you like my SQL Server stuff, you can sign up for a number ship for as little as few as $4 a month. That’s like a shot of New York espresso.

Link to do that is down in the video description. And I just want to preface most of this by saying almost everything that you are like is a actionable link is going to be in the video description. If you don’t have $4 per month to give to a handsome, charismatic fellow like me, you can like, you can comment, you can subscribe.

And you can ask me questions for my Office Hours episodes at that link, which is again down yonder. If you need help with SQL Server, if you find yourself struggling with performance or other issues, I’m available as a consultant to do all this stuff. And as always, my rates, they are reasonable.

If you would like some very reasonably priced training, you can get all of mine for about 150 US dollars. That’s with that 75% off code. The link again down in the video description for you to get all that fun stuff with.

SQL Saturday, New York City, 2025, May the 10th in Times Square at the Microsoft offices. Come, I don’t know, write something in the bathroom stall, I guess. I don’t know what you people do in bathrooms.

But with that out of the way, let’s talk about SP Quickie Store. There’s not a ton to show in here. Just really this one thing that I want to point out. But it’s a big enough improvement for me to want to talk about it like a lot because it’s something that a lot of people are going to find useful.

Like with all of my store procedures, there is a help parameter. And that help parameter will give you all sorts of information about what the store procedure does, document things within the store procedure. Like these are weight stat categories for query store.

But down here a bit, this thing will let me see. If this thing will scroll in the right pane and not just be a pane, you will find this include query hash totals thing somewhere. Where are you hiding?

There we go. There we go. Include query hash totals. So one fun thing about SSMS is that now copies the column header with everything. So that’s nice. But I just want to show you real quick kind of what this does in the code.

And we just need to get past a few things in here. This should be it. So what this does is when you say I want to include query hash totals, this thing will look at all your query store data for the queries that you get in your table of queries, like the top 10 queries that you would have gotten in here anyway.

My initial implementation of this was okay, but left something to be desired. So at first I was just doing like the query hash total for what was like in the results. And like that’s okay as long as there’s like duplicate queries, duplicate query hashes in the results.

This goes way back and finds everything. Aggregates like executions, duration, CPU time, reads, writes, memory, rows, everything across like all of your query store data, but only for the plans that end up in there. Otherwise this thing would be hell on earth to run query stores incredibly slow and painful to query.

So that’s what this thing does. And then in the end results, you will see the include query hash totals columns. What that looks like for my query store thing right now at this very moment in time is this.

So one thing that I, one thing that I change in the results when you say include query hash totals is to include the query hash column. I don’t, there’s at least a couple instances of there being duplicate query hash stuff in here. So we have that going for us, but if we come over and we look at the results a little bit further over where we start getting to like the places where you will start seeing these count executions by query hash type columns.

You’ll see that they are different from the columns in some other places. So this one found another query hash there. This one found two more.

This one found another one. This one found three more. This one found two more. So we like, you can already see where like something like aggregating things by query hash gives us way more information about like how many times a query did something. This also plays into like comparing like the total for some of these columns where the results are different.

Like this one here that goes from two to five, like the total duration in milliseconds for the two executions that we have in here is about 11 seconds. That’s about five and a half seconds a pop. But the total duration for this thing is 22 seconds, right?

So those extra three executions did way more. So I don’t do like the averages and stuff with also the total duration by query hash involved. I think just showing like the totals like with all the query hash stuff aggregated is enough.

But this column will repeat for all of the useful metrics in here. So it’ll do it for CPU. It’ll do it for reads, even though reads are a stupid meme and you shouldn’t pay attention to them.

It’ll do it for writes and physical reads, which are not a stupid meme. You should pay attention to those. So basically everything over here will end up with a total by query hash column that will complement other columns in here.

So that’s very, very useful for people who have lots of ad hoc queries that generate different query plans or have generated different query plans with the same query hash and whatever, because you get a much better idea of just how much these things are happening outside of just the results that you see in the top 10 by whatever metric you choose to order by in QueryStore. So, again, quick video just to give you a heads up on this new thing.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will turn on QueryStore and use sbquickiestore to navigate it. And again, you can get this code at my GitHub repo.

It’s code.erikdarling.com. That link will be in the video description as well. And again, that’s where I do all my support and stuff from. So if you have questions, you hit bugs, you have other problems, then file the stuff there. Don’t email me because I’ll just tell you to go to GitHub anyway.

So deal with it. Anyway, thank you for watching. Now, I’m learning to smile and smile and see you. I hope that that houffLife is happening. I hope that 노 amplifier해� dessus time, cause you might get things presente. I think, Maroon conditions on Internet says… Like a community number one or whatever.

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.

SQL Server Performance Office Hours Episode 9

---

My company (using SQL Server 2019 Standard) has an algorithm that keys addresses into a varchar(40) has a cross-reference database that assigns an identity property to each new value, allowing us to post the numeric in our datasets. Production has to search their generated string keys in this database’s table to get the integer key in return. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would your next tuning step be if this was not getting the performance you needed?

What are your thoughts on a proc with heavy dynamic SQL used to provide for flexibility – to include extra tables to join, table variables of IDs, and usually resulting in multiple query plans? Is there a best practice to handle this other than “create separate procs” (and stop using table variables)?

What options do we have for optimizing queries against tables that store JSON data in SQL Server? There are often queries that update the JSON in place or depend on some JSON value for filtering. Would indexing a computed column make sense over an indexed view? At what point do we start trying to design a normalized table structure to store this data?

Filtered index over IS (NOT) NULL – good, bad, or ugly?

how does RCSI/Sanpshot isolation work when crossing databases, if either the calling DB or target DB does not have it enabled?

To ask your questions, head over here.

Video Summary

In this video, I dive into a variety of SQL Server-related questions during my Office Hours session, covering topics like optimizing queries against JSON-stored tables, parameter sniffing and execution plans, the use of table variables in stored procedures, and snapshot isolation across databases. I share insights on how to effectively troubleshoot performance issues, discuss best practices for dynamic SQL and procedure design, and provide advice on when it might be better to normalize data storage rather than using JSON or XML columns directly. Whether you’re a seasoned DBA or just starting out, there’s plenty of valuable information here to help improve your SQL Server skills.

Full Transcript

Erik Darling here. Guess what? If you were feeling a strange vibration in your soul, if you woke up this morning and you thought, wow, today’s going to be a good day, it must be because it’s time for Office Hours. My favorite days, favorite moments in my life answering these questions. If you would like to ask a question for Office Hours, you can do so at this lovely link right here. It’s available in the video description, I promise. I make things easy for you. The other thing that I make easy for you is if you would like to join this channel as a paying member to say thank you for the extraordinary amount of work and effort that I put into these things. PowerPoint’s free. Microsoft, have you seen Microsoft subscription charges? They wanted to charge me an extra 30, some 40, 40 bucks a year to add Copilot to things. And then they had the audacity to put an asterisk and say Copilot included. And like, of course it costs 40 more dollars. So here’s a big tip for you. Here’s a money saving tip for you. If you got an email from Microsoft that was like, we’re going to start charging you more. If you go to cancel and you like click through all the yes, I’m out buttons, you can actually sign back up for like the Office 365 Classic that doesn’t have any of the AI stuff for the normal price. So screw you Microsoft. Pigs. Anyway, since I just saved you 40 bucks a year, if you want to contribute 40 bucks a year to me, you can sign up for a membership. Anyway, if you are just broke because Microsoft took all your money, Microsoft bullied you in the cafeteria. You have zero dollars left. You can do other things that support the channel. You can like, you can comment, you can subscribe. And I don’t know, I think that’s about it. I’d say you can give me a hug, but you know, I don’t need any social diseases at this point in my life. If you need help with your SQL Server, if you think, gosh, it’s time for young, good looking consultant to come fix all my problems for me, because Copilot ain’t doing it. I am available for all of these wonderful SQL server activities. And as always, my rates are reasonable. If you want some training on SQL Server, if you are just like, man, I have all this room in my brain, what am I going to do with it? You can get all of my performance tuning content for 75% off that is about 150 US dollars. And you get that for your entire the entirety of your life. So you know, do your don’t die.

Before you finish watching anyway. If you would like to catch up with me live and in person, I will be at SQL Saturday, New York City on May the 10th. And there will be a performance tuning pre-con that I will be, I will be in the room for, given by Anderias of Autor on May the 9th. And we together will give you cookies. And it’ll be nice. But with that out of the way, let’s answer some office hours questions over here. We got some we got some long ones today. Boy, howdy. Let’s see here. My company using SQL Server 2019 standard. I feel like we need like a montage cut on this one has an algorithm. It’s a fancy word for query that keys addresses into a VARCAR 40.

Okay, as a cross reference database that assigns an identity property to each new value. Okay, getting there, allowing us to post the numeric in our data sets. Sounds good. Production has to search their generated string keys in this database’s table to get the energy key column. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would be your next tuning step? What would your next tuning step be if this was not getting the performance you needed?

Well, I mean, there’s a lot of information in there. What there isn’t in there is an example of the query or an example query plan for any of that stuff. There are many ways for you to share this stuff with me. There’s a wonderful website called dbfiddle.uk. My friend Jack runs it and you can put example queries in there. You can use sample data. You can do all sorts of stuff. If you want to share query plans, there are a number of ways to do that.

Paste the plan.com, anything that you can paste the XML into, and I can answer questions like this better. But with most things performance, the question comes down to what is the server telling you and what is the query telling you? It’s difficult to say if this is a performance problem specific to the query and process that you have in place.

Like it could be a server level, like something is messed up in a muck, or it could be at the query level. And in which case, the things like the execution plan will tell you where you are spending the majority of your time. Since you are on SQL Server 2019, you have this wonderful facility where when you get the actual execution plan, it will tell you where you actually spend time in your various operators.

And then you can look and you can say, gosh, I spend a lot of time here. How can I make this better? So that’s where I would start. I don’t know where to go based on this word problem, but I don’t know exactly what I would do based on this word problem.

But I know exactly what I would look at to figure out where things are slowing down on me. You know, it could be that you need to watch the server and all this stuff is happening. And you might see that the server is overloaded in some way, either from a CPU or memory or locking perspective.

Perhaps there is some clashing between you generating these FARCAR 40s and identities and other stuff, and you trying to read from those tables. I don’t know.

But I do know where I would look. And hopefully now you know where to look too. All right. What are your thoughts on a PROC?

Procedure with heavy dynamic SQL used to provide for flexibility to include extra tables to join, table variables of IDs, and usually resulting in multiple query plans. Is there a best practice to handle this other than create separate procedures and stop using table variables?

Well, table variables really shouldn’t result in separate plans since table variables have a recompile rate of exactly zero. You should be reusing execution plans if your queries are properly parameterized and, you know, you send in two of the same query.

It should be happening. There may be something going on with table variable deferred compilation. If you’re on SQL Server 2019 plus and you’re using compat level 150 or better, you might need to look at that. But in general, I have no problem with this.

I don’t even have a problem with there being multiple execution plans for things. The plan cache has been babied and treated with kid gloves for years, and it has done nothing but abuse and mistreat us, clearing out frequently, not giving us all the information we need.

So I don’t really see a need to be kinder to the plan cache. So screw you, plan cache. If you are overloading query store because these queries are particularly busy, that might be a slightly different matter.

But in general, I don’t have a problem with this, and managing plan reuse generally comes down to proper parameterization. And, you know, if you have this, you know, people are just constantly generating queries that need to do different stuff because you are obviously, like, joining to different tables on some occasions.

You might be selecting different columns. You might have completely different where or join clauses based on this stuff. You might even throw in some exists or not exists just for, you know, happy fun time giggles. It’s one of those things where, like, I don’t mind heavy dynamic SQL because heavy dynamic SQL has been very, very good to me over the years.

And when you need to provide this type of flexibility to end users, there is no guarantee that separate procedures would get you anything more than properly parameterized dynamic SQL executed with SP execute SQL would get you.

It essentially gets you the equivalent behavior of a store procedure because SP execute SQL is a store procedure. And that store procedure is executing a query, and you will get plan reuse on that query so long as everything lines up appropriately.

So it may not be a solvable problem for you if the number of queries that you are generating is indeed vast. All right. And here’s, oh, God, another long one.

All right. What options do we have for optimizing queries against tables that store JSON in SQL Server? There are often queries that update the JSON in place. Oh, oh, come on.

Or depend on some JSON value for filter. No. When indexing a computed column makes sense over an indexed view, at what point do we start trying to design a normalized table to store this data now? There are two good times to design tables to normalize JSON or XML or any other type of blob-ish data.

When you first design the application and today. When you have to do, like, blobs like that, ideally in any database, are retrieve only. You just get them out and you return them somewhere where they have to be returned to.

You should, if you need to parse them apart, you should do that in the place that you return them to. Doing this stuff in your database is just a crappy idea. Don’t do it to yourself.

It hurts. It’s painful. I’ve seen way too many people try to do this, fail miserably. And by the time they realize how miserably they have failed, it is far too late to re-architect the number of things that would need to be re-architected in order to solve the problem. And you are stuck.

Well, XML is a particularly awful beast. But JSON, Microsoft smartened up a little bit. But the really bad part about XML is that, like, the XPath stuff isn’t valid as a computed column unless you put it in a scalar UDF. And as you all know, if you put scalar UDFs in a computed column, even if you index them, life is hell.

So the JSON stuff is a little bit more forgiving with that because you can create computed columns based on JSON and you can index them. And you can, like, do that without the presence of a scalar UDF. So that would be one way of doing it if you have, like, a minimal amount of data inside of there that you need to, like, filter on.

That doesn’t help you with the modifying of the JSON. In fact, it would probably hurt you with the modifying of the JSON because now you have to maintain the separate computed column and index structure when you modify the JSON. So if you want my personal opinion, just don’t do this, period.

Parse out your JSON, store it in a table, don’t update it directly, don’t filter on it directly in a query. It’s just bad news for performance. Let’s see here.

Ah, blessedly short one. Filtered index over is not null. Good, bad, or ugly. Perfectly fine in my estimation. Just make sure that when you create your filtered index, at bare minimum, the column in your filter expression is included somewhere in the index. It could be a key column.

It could be an included column. In my experience, the include column is just fine for it because you are probably going to be, it’s probably going to, like, with no other predicates in place, it’s just going to be a scan of the index anyway because SQL Server knows that all of the data in the index matches your requirement.

The nice part about is null and is not null is you can’t really, like, they don’t have the usual problem with, like, bit columns where you could be searching with a parameter. Is null and is not null, pretty much have to write those out as literals every time, so you don’t have to deal with that as much. So just a fine thing to do.

The only caveat’s there. Again, make sure that the filter, the column that is in your filter expression is somewhere in the index definition. And, you know, what do you call it?

There was one other one. Make sure that it is a useful filter. If your filter expression gets you, like, doesn’t eliminate, like, half of the table data or better, it might not be worth it in the long run. Like, if you still have, like, 75, 80, 90% of the table, like, covered by whatever filtered index, what are you really getting out of it?

Not much. Not much at all. And finally, question number five.

You can tell it’s question number five because there is a five right here. So this will be the last one today. How does RCSI and snapshot isolation work when crossing databases? If either the calling DB or target DB does not have it enabled?

Well, if you’re in the calling database and you try to set the isolation level to snapshot and snapshot is not enabled, you’re just going to get an error. So you can’t do, you can’t go from, like, here to here with that. I believe that’s also reciprocal.

I don’t think that you can start a transaction using snapshot isolation if you are crossing boundaries into a database that doesn’t have it enabled. RCSI is somewhat more forgiving because you don’t actually have to set anything for that. So the behavior that you should see is that regardless of where the query starts, when you start reading data from a database where it’s enabled, you should see the row versioning stuff.

But, like, for any database where it’s not enabled, your modification queries aren’t generating row versions so they can’t use it. Right? Like, it’s just impossible.

Like, there’s no row versions for them to read so they can’t possibly use that isolation level. So, good for us. We have answered all five questions, hopefully to everyone’s satisfaction. I don’t think there’s much more to say here.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I guess I’m going to try to cue up five more to answer. We’ll see how it goes.

Anyway. Cool. Thank you for watching. Thank you.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Recent Improvements to sp_HumanEventsBlockViewer

---

Video Summary

In this video, I delve into the recent enhancements to SP Human Events Block Viewer, a powerful tool designed to parse and analyze block process report XML data in SQL Server. I explain how you can now read from a target table or log the fully parsed results to another table, offering greater flexibility and utility for managing blocked processes. Additionally, I demonstrate the new parameters added to the stored procedure, such as specifying a timestamp column for filtering and logging results to a designated table with retention settings. This update not only makes SP Human Events Block Viewer more versatile but also highlights its importance in diagnosing and resolving blocking issues efficiently.

Full Transcript

Erik Darling here with Darling Data, and in today’s video we are going to talk about recent improvements to SP Human Events Block Viewer. I guess you could say everything is improved except the name. So if you are unaware, this started off as a companion script to SP Human Events. That’s why it’s called SP Human Events Block Viewer. I originally wrote it to look at the block process report XML in a more refined way than just creating a view to look at it. And then it just took off and became a world of its own and it is, I think, one of the handiest tools in my set of procedures because if you have a block process report extended event, this will parse the heck out of it and give you all sorts of great information about what went on during your blocked processes. What we added in, what I added in this one was a couple things. One is the ability to read from a target table. So if you store the block process report XML in a table somewhere, like just like the raw XML, you can point this to that table and parse it out. And you can also log my fully parsed thing to a table and you can even do both. It’s kind of cool. So let’s talk a little bit about this stuff before I get started.

Before we get into the store procedure. If you appreciate my SQL Server efforts and you want to say thank you with money, you can sign up for a membership down in the video description. If you have no money, you can like, you can comment, you can subscribe and you can even ask me questions for free for my office hours episodes. So all sorts of good things afoot there. If you are looking for a SQL Server consultant, again, Beer Gut Magazine has rated me the absolute tip top SQL Server consultant number one. One in the world outside of New Zealand and only one specific part of New Zealand. It’s not all of New Zealand. There’s like one address in New Zealand. If you would like some high quality SQL Server training, I’ve got that as well. You can get it for 75% off. That’s about 150 US dollars after you apply that discount code. Again, all the links for this stuff are down in the video description. SQL Saturday, New York City, May the 10th, 2025. It’ll be fantastic. I will be there. We can hang out. We can high five. We can talk about all my awesome SQL Server store procedures or whatever. The weather will be nice by then. So we can we can chat about that. I don’t like chatting about bad weather. It’s not fun.

With that out of the way, though, let’s talk about SP human events block viewer, the store procedure whose name always precedes itself. So there are two sets of new parameters that got added in here. There are these right here. These are if you want to read the block process report XML from a table. So target database schema table, the target column that has the XML data in it. And then there’s an optional one called timestamp column. If you if you if you’re if you’re the column you put the XML into has like an insert date.

And you want to filter on that rather than filtering on what’s in the XML. You can do that. And then the other set of columns that I have in here, zoom and cooperate are these ones to log to a table. So if you set log to table equals one, and I don’t know why this turned a different color on me, let’s just overlay that. Let’s see. Let’s see how this goes. That looks kind of weird, kind of funky, like 80s computer vibe to it. If you set this to one, you will log the results of this thing to a table.

You have to provide it with a database, a schema, and this is a default value for this. I believe the default if you leave the schema blank, it’ll default to DBO. And then you can also set a retention in number of days for how long you want to do that.

And this will delete data older than that date in whatever table you’re logging to. So two things to show you there that will be lots of fun. So with all my store procedures, there is a help parameter that will give you all of the information you need about what the script does, some examples of how to run it, and what all of the parameters do.

So all good stuff for you to learn about in there. So let’s say that I terminate my SQL Server queries correctly. And I’ve got this table called block process report.

And I’ve got the block process report XML stored in this column. And I’ve got a insert date column called insert date right there. I can run.

I’m going to run this with debug just so we can all see what happens. So you can see the magic behind the things. And we can run this. And what we’ll get back for like the main result set, the non debug result set, will be this thing in here, which will be the fully parsed out block process report that I promised you. So this is the main result set.

But if we just do this without debug, it’ll be just these things. So the main results for this are the block process report fully parsed out, any available query plans from the block process report XML. If you scroll down a little bit in any of these sections, you get these SQL handles.

And I’ll go and look in the plan cache for those SQL handles. And then down here, there is a roll up with all the stuff that you might care about that happened during the block process. During the block process, as you can see, for the one block process that’s in here, there’s the query that was blocking it was sleeping.

So that query took it decided to take a little nap. Didn’t want to didn’t want to finish running, didn’t want to complete itself and blocked another query. So you can if you’re storing the block process report XML off in a table somewhere, this is a great way to read it.

Now, if you want to log stuff to a table, there’s really only one result set worth logging to a table. And I do that. So if we run this, again, this is with debug mode on this.

So this returns all like the debug output. But if you look over in the messages tab, this will give you the dynamic SQL results as well. And so just like with if you watch the SP health parser video, one thing that I do here is make sure that we’re not putting the same data in over and over and over again.

So I find the max event time or like the oldest date or the newest date in the in this table. And then I filter based on that. So you will only see the freshest, newest things in here.

So that’s a good time. And I suppose what’s we should go look and make sure that table got created and has stuff in it. And if we select the top thousand rows out, we will see just about what we saw in the results before that XML gets fully parsed.

And then what’s really nice, actually, this is probably overkill, is that if you use SP human events block viewer to log stuff to a table, you can even point SP human events block viewer at that table and reparse the XML because it’s not already done for you or something. Anyway, I suppose that the upside of this is that you will get the findings back for everything because I don’t log the findings to a table. Well, that would be incredibly annoying and repetitive.

So anyway, that’s fun. So, yeah, SP human events block viewer name still a lot to type, but it’s doing a lot more stuff, cool stuff now. So I hope that you will actually I hope you turn on RCS and you have very little use for it.

But if you don’t if you don’t have recommitted snapshot isolation turned on, if you are still suffering under the yoke of SQL Server’s default recommitted isolation level, which is awful and terrible and innumerable ways, then this will probably be very handy for you because those those no lock hints don’t always save you. Anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. I hope you will use SP human events block viewer and maybe even start logging it to a table and maybe use it as ammunition to convince someone to turn on recommitted snapshot isolation to reduce all of these blocking problems that you currently have. So that’ll be a good time.

Won’t it? Won’t it then? Anyway, 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.

Recent Improvements to sp_HealthParser

---

Video Summary

In this video, I delve into some recent enhancements to my SQL Server performance analysis scripts, specifically focusing on the `SP Health Parser` stored procedure. This tool has been a valuable resource for parsing the System Health Extended Event in SQL Server since 2008, converting its XML output into more digestible tables. The latest updates include new events and the ability to log these events directly to tables, providing a more comprehensive view of your server’s health over time. I walk through how these changes work, demonstrating their functionality with examples and explaining the data retention policies that ensure you only capture relevant information. Whether you’re looking to analyze trends or simply gain deeper insights into your SQL Server performance, this update could be just what you need.

Full Transcript

Erik Darling here with Darling Data. Pretend that didn’t happen. Drives everyone crazy. And in today’s video, I have been very, very hard at work on my set of SQL Server performance analysis scripts, stored procedures rather, to help you help yourselves a little bit and also to help me because as as a consultant with very reasonable rates, these are all things that I thought I would find useful in the work I do. So hopefully you will find them useful in the work you do as well. So today’s video, we’re going to talk about improvements to SP Health Parser.

For those of you who are unfamiliar with it, I dropped this one sort of at the end of, towards the middle end of last year. And what this store procedure does is, you know, every SQL Server since about SQL Server 2008 or so has an extended event running on it called the System Health Extended Event. That thing is full of awful XML hell. And it’s a bad time. So I wrote a store procedure to parse it all out, put it all into helpful tables for you.

And the recent additions to it are some events that I was not collecting before and the ability to log those events to tables. So we’re going to talk through that stuff in this video. But before we do, we have some important stuff to talk about. If you like this channel content and you would like to sign up as a, as a say thank you with money member, link down in the video description to do that.

If you are, you know, just, you know, just a, irretrievably broke college student or something, I don’t know, maybe, I don’t know, maybe the, maybe, maybe the stock market stuff has you a little, little, little weaselly about four bucks a month. You can do other stuff to help my channel survive, thrive and whatever else, whatever else, whatever these YouTube channels do, like, comment, subscribe. And if you want to ask me a question, a question or more than one question, you can ask a thousand questions if you want, that I will answer on an office hours episode.

You can do so at the link up here, which is also down in your video description. If you need SQL Server consulting help, perhaps the output of these scripts is just not enough to help you figure out exactly who, what, where, when, and why did you, did your SQL Server dirty. I am available. You can hire me. I will make your SQL Server faster in exchange for money.

And as always, my rates are reasonable. If you would like some training content to help you get better at SQL Server, maybe without, you know, live and in-person Zoom call with me, you can get all 24 hours of my training, beginning, beginner, intermediate, and advanced. 75% off. It’s about 150 US dollars.

And you get that for life. Again, fully assembled link down in the old video description. SQL Saturday, New York City, 2025. That is this year. That is, oh boy, closer, closer by the day, isn’t she?

Uh, Saturday, May 10th, taking place at the Microsoft offices in Times Square. Uh, so be there or be square or be in Times Square. Uh, it’ll work itself out.

Anyway, let’s talk about the improvements to SP Health Parser. Now, um, this thing does a lot of work. I don’t want you to be too scared and taken aback by the code.

Um, the stuff that I want to show you in here is around, well, I’m going to show you a little bit of the code and then we’re going to talk through like, um, uh, other stuff. Like how it works. I’ll show you it working, basically.

Uh, so what this does is, um, it’ll go through and I think I skipped down a little bit too far. Let’s get back up a little bit higher here. So, uh, if you decide to log to a table, um, then there are some changes that I make.

Like I don’t log the blocking stuff to the table because I have, there are other facilities for doing that. If you want to log deadlocks to a table, SP Blitzlock does that. If you want to log, uh, the block process report to a table, we’re going to talk about that in the next video or two.

Uh, because I added, I added this to human events block viewer as well. So it’ll, uh, create these tables. Some of these are the new ones.

So, um, there’s one, two, three, four, five, six, seven, eight, nine, 10, 11, 11 or 12 tables that will get created. And all of the stuff will get logged to the table in here. Um, the new ones are, I believe, uh, memory broker, memory node out of memory, scheduler issues, and severe errors.

Uh, these are all the new stuff that I’m collecting. There are, there’s stuff in the system health extended event for all those. And I just decided to get busy with the XML on that.

Uh, if you are logging stuff to a table, um, I do a few things. Um, if you specify a schema that doesn’t exist, I will create that schema for you. If the tables that you want to log to do not already exist, then I will create all those tables for you.

That is what this lovely mess of dynamic SQL does, uh, all in here. So, uh, that’s a lot of fun, right? Like, oh, look at all this wonderful dynamic, very repetitive dynamic SQL.

Uh, part of what the, this thing does is it has a data retention policy on it. So, um, you, if you want to keep 30 days or seven days or two weeks of data, uh, this will help, help you, uh, achieve that. And it’ll start every runoff by deleting from the tables where, um, those, where the, the times are older than those dates or whatever, however you want to put it.

Uh, once that’s done, uh, you know, your temp tables get created. And let me actually skip down a little bit further. Let me make sure I don’t like cursor down to where I should.

Uh, so what this will do is if you decide to log to a table, it changes the select query slightly. And then it does a insert, uh, to the table. But another thing that it does, um, there’s a insert SQL thing that gets built up based on the table definition.

But, um, one thing that this thing does is it makes sure that you’re not, you’re only putting data in that’s new. So there’s this MD SQL part in here. And what this will do is find the max date currently in your extended event and currently in your logging table and filter out whatever data we collect to only get data from that point on.

So you’re not just constantly logging new crap in there. Cause that would just be a nightmare. Uh, and then, you know, we do the insert into the table.

And if we don’t do the insert, then we, um, then I just like would return the result out to you. So, uh, what we’re going to do is stop looking at code and come over here. This window has SP health parser stuff in it.

Uh, and I’ve set up this DBA database that has no tables in it. I just hit F5. Oh, oh no. This thing tried to, it’s complicated.

My computer is stupid. Uh, so with all of my store procedures, um, there is a help parameter and that help parameter will tell you all sorts of good information about, um, what the store procedure does and all of the parameters that are available to it and what those parameters do, their default value, stuff like that. Uh, so what we’re going to do is I’m going to run this in debug mode, just so we get a bunch of stuff back to look at together.

Uh, and I don’t want you to be scared at all because, oh, that should have been like seven or something, but it doesn’t matter because we’re only running this thing once. Uh, this does take a second to run because it does a lot of XML parsing. Um, I don’t know if we got to a point in here where the XML starts to get parsed, but, uh, boy, is there some heavy duty XML parsing.

Um, this isn’t my fault. I wish that there was another way to do it, but Microsoft stores all this stuff in XML. And so, uh, as your, your humble SQL Server servant, I am forced to parse that XML to make you happy.

But, um, anyway, this thing is successfully run. Uh, we have a bunch of debug output. This, uh, this would not normally get returned to us, but, uh, because I ran this in debug, we, uh, we get a bunch of other things back.

Uh, over in the messages pane, excuse me, over in the messages pane with debug enabled, of course, uh, it’ll tell you that we created tables and, uh, it’ll show you the, uh, inserts that we did. Anything that happens in the dynamic SQL, it’ll show in here. And if we get down a little bit lower, we should start seeing the, uh, insert queries.

Uh, this is the data. This is the dynamic SQL to find the max date, uh, that’s currently in whatever table we’re currently about to insert into. And then pass that in as a parameter down in here, right?

That’s this thing. So we do our insert and we, uh, get data and then we can look at data. So just to make things a little bit easy, let’s just grab, uh, one of these tables.

Let’s refresh this. And now we see all of the tables that got created in here. Um, I don’t know which one is going to actually have stuff in it.

Um, suppose we could try looking at weights by count. That usually has something in it. And here we go.

Here’s what the table returns. Uh, we have the collection time. Uh, we have the event time rounded. So the event time rounded, uh, by default, this will bucket by the hour. So all the weights that happened in an hour, it’ll sum up and give you the average wait time and the max wait time.

The max wait time column is a little tough to deal with because it’s the max wait time that’s been recorded like since. Actually, I don’t know since when it’s either since like startup or since the extended event has stuff in it, but this column gets very repetitive and it doesn’t always lead you to exactly where, um, you know, the weight spiked up to have, have a max wait time of two seconds for async network IO. But anyway, uh, this works pretty well.

Um, one thing to note about, uh, SP, uh, health parser is that not all, uh, events are going to have data associated with them. So kind of like coming back up here to where the tables get created. Um, some, like some of these, if you don’t have problems, nothing ends up here.

So if some of these tables are empty, that’s a good thing. Like for example, for example, like if like memory node OOM, that’s out of memory. If you don’t have memory nodes that, that like end up with out of memory conditions, there’s nothing, there’s no XML in there.

There’s not XML that says nothing happened, right? There’s just no XML. So nothing will end up in there.

So if some of these tables are empty for you, that is why. Anyway, uh, that is a quick overview of the improvements made to SP health parser. Um, I hope that you will find yourself, uh, enjoying these improvements, maybe logging stuff to tables, maybe trending these things over time.

You can do all sorts of fun stuff, like put them in an Excel file or I don’t know, use DuckDB or whatever, whatever crazy things you people do to analyze data. And you can, uh, start figuring out SQL Server performance issues. Um, you could also like do this stuff and hire someone like me to go through the data and figure it out for you, which is also a pretty good plan.

But if I do say so myself, anyway, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video where we’re going to talk about improvements to SP human events block viewer.

Anyway, goodbye.

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.

Introducing sp_IndexCleanup!

---

Video Summary

In this video, I’m Erik Darling from Darling Data, and today we’re unveiling a brand new stored procedure to help you streamline your SQL Server management. SP_index_cleanup is designed to identify unused and duplicative indexes, which can be major culprits in performance issues like buffer pool contention and lock escalation. With this tool, you’ll get recommendations for tuning queries and indexes, fixing blocking and deadlocking problems, all while making your databases more efficient. The process involves removing unused indexes, deduplicating leftovers, and adding page compression to make your data smaller on disk and in memory. This not only helps reduce transaction log throughput issues but also eases the burden of managing too many indexes.

The script works through a series of well-defined rules to identify exact duplicates and superset/subset relationships between indexes, ensuring that you’re making informed decisions about which indexes to keep or remove. It then scripts out all necessary changes for you, including merging included columns into create statements, disabling unused indexes, and compressing any indexes without page compression. This iterative process helps ensure that your database is optimized for performance while minimizing the time spent on repetitive tasks. Whether you’re dealing with a single table or an entire database, SP_index_cleanup provides detailed reports to guide your decisions, making it easier than ever to get better at SQL Server management.

Full Transcript

Erik Darling here with Darling Data and today we have an exciting announcement. We are welcoming a new member to the family and I don’t mean I’m hiring someone because who the hell would want to work for me. What I mean is that we are introducing a brand new stored procedure to help you with your SQL Server nonsense. It’s been a while since I’ve written a brand new one of these. I think sometime towards the end of 2024 I spit out SP Health Parser and SP Log Hunter, which are valued members of the family, but as a wise woman once said, you’re getting too big to cuddle. So I’ve got a new one and this one is called SP underscore index cleanup. And the goal of this stored procedure is to help you identify unused and with di solipe.

performance, make recommendations, tune queries and indexes, fix blocking and deadlocking problems, and of course help you get better at SQL Server. And like most of my time gets spent in here. And as much as I love like tuning queries and indexes and like other words, like, you know, tuning queries so they go faster and creating indexes to help queries go faster, part of this analysis is looking at unused and duplicative indexes because unused and duplicative indexes cause a lot of problems. I recorded a few videos about the problems they cause around like buffer pool contention, lock escalation and like trend, like the more indexes you have, the more transaction log stuff you have to do. So having like too many indexes, especially of the like unhealthy kind, it’s like, like bad cholesterol or something. Having too many indexes of the unhealthy kind impacts SQL Server in a number of areas. So most of the databases that I look at have way more data than memory, have only ever had indexes added, it seems to be like an append only operation, then have lots of bad deadlocking problems and cleaning up unused and duplicative indexes can help reduce these things, right? So like, like you have fewer indexes, you have fewer objects competing for buffer pool space, you have fewer indexes. So you have fewer lock escalations.

And you know, if you have transaction log throughput trouble, or like you have an AG, we’re like, you know, sending all these index changes across to other nodes is a pain, getting rid of these sort of like unhealthy indexes is very useful. So the clean cleanup process typically consists of removing unused indexes, deduplicating leftovers and adding page compression.

Right? And adding page compression is great because it makes your data smaller on disk and in memory. So you make better use of the memory that you have. So the point of this script is to really clean up all the indexes you have, make them as small as possible. And then you have a very clear sense of like, you know, do you need more memory? Now you know, right? Like, like now you can figure it out.

Now, you don’t want to double work things. So whenever I’m going through this process, right? My job, like the first thing I do is find unused indexes, right? And I’m listed all the unused indexes. And then I grab all the duplicative indexes, either like exact key column matches or indexes that lead with the same key column. And then I have to go and look and see if any of the unused indexes are in there because I don’t want to double work anything. So I’m like, Oh, I can delete this one. I can do this one. And then after you finally do that, you can look at the list of indexes that you have left.

The problem is now that you’ve, now that you like have this list of duplicative indexes that you can, you can, you can analyze, you have to look at key columns, key column order. You have to look at included columns. You have to look at if they have filters on them. You have to look at if they’re unique or not. You have to compare like column sort orders, they ascending, descending. And it’s just like a lot of just like, Oh, we have to move this down. Oh, this include list goes way off the SMS screen. Let’s bring that down. Oh, this one looks like it has the same number. Oh, this one has 30 includes this one has 31 includes what’s different. It’s it’s just it’s not fun. And after all that, you still have to like edit all these create statements to merge the included columns, which means like you have to make a, like you have the create index statement and then you’d like do the drop existing blah, blah, blah, make sure all the included columns are accounted for. Uh, like for all the create statements for the indexes, you’re going to get rid of, you have to like alter table, like alter index, blah, blah, disable. And then like, like, you know, fixing all of the other stuff that goes wrong with like index creation, you have like fill factors that are really low. Uh, can you use online, you use, are you using a version of SQL Server where you can create indexes online or not?

Uh, do you need to like, you know, drop the, like do drop existing? Do you need to add compression? Like all this stuff that you have to think of every time you do this, it gets very, very annoying. And then because index tuning is a very iterative process and I realize I’m standing in the way of some of these words. So I’m just gonna, I’m gonna duck down for a moment and oh, the green screen effect. We’re having a good time with this, aren’t we? Uh, so then you get to do it all over again because index tuning is a highly iterative process, right?

Like you go through, you make this first set of changes, you see how things look with SQL Server, and then you, you check in and then you make sure that like, there’s no new crap. And then you can just do all these things until, until time, time immemorial or something. Um, but even after doing all that, you still don’t have a full list of indexes that are not compressed that you probably should compress because you have too much data and not enough memory, especially if you’re on standard edition, this is a big deal. Now, I absolutely adore SP Blitz index. I, I still use it. Uh, I still, well, I mean, I haven’t had anything to contribute to it in a while. I feel like I’ve contributed all, maybe all that I can to that. Uh, but, and it’s great at analyzing things. It is fantastic at doing the analysis and flagging things that are like worth you looking at. So like unused indexes, what’s duplicative based on either all of the leading key columns and a lot more other stuff.

I’ve written some checks in there that are very, very useful to this day. Uh, SP Blitz index, of course, originally written by Kendra Little now, like, I guess, happily maintained by the, the, the greater open source community of SQL Server. But, uh, like adding all this code to that would have been a nightmare. Um, so there, there is also a mode with Blitz index where we’ll inventory all the indexes in your database and give you scripted create and drop statements. But like, if you want to add compression, modifying those statements is not the most fun in the world. Uh, and it doesn’t do any of this work for you. Now, me being a consultant, um, I want to spend as little time on these sort of tedious, repetitive tasks as possible. So I wrote SP index cleanup. Um, so it will identify unused indexes. It’ll warn you if your server uptime is not very long because it’s a bet. It’s not a, you can’t make a very confident decision about unused indexes. If your server hasn’t been up for a terribly long time, uh, it will compare index and included columns. It will take into account like uniqueness and filters and all that other good stuff. And it will find compressible indexes.

So anything without page compression already, you will find those and mark those and say, Hey, you should probably compress this. Uh, and it works off a set of well-defined rules for unused exact duplicate key columns, uh, superset subset key columns, uh, unique versus non unique keys, including just like unique constraints. Um, one thing this script does that I’m very happy with is if you have, uh, unique constraints on your table and you also have a nonclustered index that is on those same columns and has like some includes, then it will tell you to get rid of the constraint and make the non clustered index unique. So you can kill two birds with one stone there. Uh, it will evaluate column sort directions. So if like just say you’re on the user’s table and you have something on reputation, ascending and something on reputation descending, it won’t call those dupes. And it will also merge include columns into a handy create script for you with all the options, like the way that I want them, right? When I do this stuff. Uh, so like I said, it scripts out all the actions for you. It’ll merge any missing included columns into keeper indexes. It’ll disable unused and exact dupes or subset indexes, and it’ll compress any indexes without compression. Uh, it’ll give you some things to review.

I mean, all of this is for review. It doesn’t automatically do all this stuff. It just gives you the script to do it. Um, so like stuff that you might have to review, uh, like as part of like the overall results are indexes that are again, like have like the same key columns, just in different orders. Again, like columns, ABC column, the CBA or BAC or something. Uh, you can look at the usage patterns there and figure out if that’s like, like you want one or the other or both or whatever, uh, indexes that we’re keeping. I leave in the results so you can validate any changes there.

Like one thing it’ll flag is like this index is a subset or of this index or this index, it’s a superset of this index. So you can like go and like grab those definitions and compare them. Uh, it’ll give you all the proposed scripted changes and it will give you a summary report about all of the actions that you can potentially take at like overall database and table level and like the potential gains from executing on each one of those actions. Uh, I don’t do any double working. Um, if indexes are unused, they’re not part of the D-dupe analysis. So, you know, again, like server uptime is pretty important for this. You want to make sure that like, you know, your server has been up for like a day or two or something like that. You want to make sure that you have at least a couple of weeks of uptime to make sure that like you have a good amount of like index usage information in there. Uh, any indexes that get merged will already have compression, uh, scripted into them. So you don’t need, so you won’t get like a separate compress this index because you already compressed it when you did the merge thing. Uh, and compression, so compression recommendations don’t include indexes being merged or indexes being disabled for any reason.

Uh, and so let’s talk a little bit about usage. Um, there are things you can filter on. You can filter to a specific database if you want, and you can also filter to a specific table if you want to focus on one table.

Uh, there are also some things about your tables and indexes that you can filter on, like a minimum number of reads, writes, minimum number of rows in the table, and the minimum table size in gigs. Um, this is it that the size, the min size GB is a decimal, so you can put things in there if you’re interested in something that’s like 500, 500 megs or something.

Um, you can also choose to get all databases, and you can choose to include or exclude databases as part of that. So if you want to like get all databases, but you want DB1 and DB2 and not DB3 and DB4, you can tell the script that and it will do it. And so you get just the databases that you care about, because me, Erik Darling, I care about you. Uh, some sample results for this thing. I realize that my, my body is in the way of some of these, that’s okay, but this is sort of what the cleanup output looks like.

Uh, you get the type of script, so whether you’re going to merge, disable, or compress something. Uh, any additional info, note in this column we do have like the warning, there’s less than 14 days of uptime. Uh, then you get stuff like the database schema, the table name, the index name, uh, the consolidation rule that it got hit with. Uh, and then down here we have the target index name and the superseded stuff.

So this is, this is what I was talking about where like, uh, if you need to compare which indexes are, like which index won the merge war and like which index is getting merged into it and which one like is the mergee and the merger, then you can, you can sort of follow these names and do that.

Uh, you get the size of the indexes, the number of rows in the index. So filtered indexes, they’ll show some stuff different. Uh, the number of, you get like the read and write ratio of these. Of course, you know, this is just me like ginning up some test stuff. So the reads and writes aren’t very impressive looking here. And then there’s the original index definition. So like the index as it exists right now. And then there’s also the, uh, this final column, the script, this is what you would run to apply whatever changes the script is recommending. And then finally we have the reporting output, uh, where you will see, uh, stuff at the, like all the things that were analyzed.

This does require a little bit of explanation, which I’ll get to in a minute, but you have the number of objects analyzed. Uh, then you have like the database and table level numbers, uh, the database name, uh, like tables analyzed, total indexes, indexes you can remove, indexes you can merge, and then like some percentages. This first line up here is only the stuff that was analyzed. So like in my stack overflow database right now, the only table that I have a bunch of extra indexes on is the users table. So that’s why this says tables analyzed one and total index is 25. If you look at the, like, like the number of indexes in here, it says 30 and total for all this, that that’s, that’s by design.

Uh, the total index is 25. There are 25 nonclustered indexes on the users table. That’s what was analyzed. There are 26 total indexes on the users table, including the clustered index. Um, this script will not like try to tell you to get rid of nonclustered indexes if they match your clustered index or if they match a non-clustered primary key, because that would be stupid, right?

You don’t want to mess with those. Those are like, those are more specific things than just some non-clustered indexes that people have clicked, right clicked on the green text to create. Uh, you also get some size information. So the current size of, uh, again, this line here is only for the analyzed stuff.

So this top line is always going to look different from the rest of the results, but for the rest of it, you have like the, at the database level, the size of the database, the size of the tables, how much space you can save by cleaning up indexes. Um, there’s also some guesses at how much space you can save, uh, both like, uh, like min and max for, uh, the total for the database and everything.

And then you have the total rows and then like a breakdown of reads, writes, locks, slashes, all this other good stuff. And then at the table level, it’ll show you like how many like things you can save a day. Let me move over to the side a little bit here so you can see better.

Uh, how many things you can save a day by cleaning up indexes. So this is the user’s table right here. This is the user’s table right here. It’s had 499 latch weights and we could save 25 latch weights a day by cleaning up the nine removable indexes on here and performing our two merge statements. So, uh, that’s kind of how this whole thing works. Um, if you want to get it and try it out, you can go to code.erikdarling.com and you can do that there, but, uh, let’s, let’s do some live demo action on this thing. Uh, so you can kind of see how it works. And I’m going to show you just a couple of other things while we’re in here. So, um, what I’ve done is created, these are the 25 non-clustered indexes on this table. Uh, this is the script that I run to generate some reads against them. So that, against most of them, some of them I do want to be unused to test the unused rules.

And then this is the store procedure itself in all its glory. Uh, like all of my store procedures, uh, there is a help parameter. So if you run this, it’ll tell you, uh, what parameters are currently available. It’ll introduce itself like a proper, proper young gentleman. Uh, it will, uh, give you all of the parameters, uh, their data type, their description, their valid inputs, and their default values. Uh, and then if you run this just normal, and this runs pretty quick, at least on my machine, this is, these are the results that we just looked at, uh, where you can see all of this stuff that we just talked about where, um, you know, like you get the, all of the scripted output here. Uh, it’s lots of, lots of fun. And then down here you get the, uh, the reporting analysis on everything.

So there is lots of stuff in here. Um, it works. Uh, I’ve tested it. I’ve, I’ve actually done unit testing to like created like indexes that match rules and like extended rules and stuff. And, uh, everything seems to be working very well. Uh, so I’m excited to get out and try it on more real world servers. I’ve only had a chance to run it on two or three client servers to try and weed out some of the initial, like, I just wrote this script problems. Cause there are always, there always are some, and, uh, that was very helpful to be able to do that. But, uh, anyway, I look forward to you using it and giving me feedback on it. And, uh, I don’t know, I think, I think that’s just about it for this one. So, um, yeah, um, again, very excited over here in, in darling data land. Uh, so I hope you enjoyed yourselves. I hope you’ll use the script. I hope you’ll enjoy using the script. And, uh, again, if you, if you run into anything with it, uh, the, the place to go or rather the URL to go to is, uh, code.erikdarling.com. This redirects to my GitHub repo. You can, uh, you know, you can open up issues with the code. Uh, you can, you know, uh, propose contributions to the code, ask questions about it, whatever it is you need to do. That URL will lead you to GitHub where you can do all of those things and get support. Do not email me for support on this. I do not do email support at all, ever. I hate it. I will tell you to go to GitHub. Do not email me directly. Okay.

Okay. Anyway, uh, I think that’s about it here. Uh, all right. Yeah. Goodbye.

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.

All About SQL Server Stored Procedures: Temporary Object Performance

---

Video Summary

In this video, I delve into the nuances of choosing between temporary objects in SQL Server stored procedures, focusing specifically on global temporary tables and table variables. I explore how these objects behave differently from each other, particularly when it comes to performance and recompilation issues. By walking through practical examples using SQL Server 2019 with compatibility level 150, I demonstrate the limitations of table variables—how they lack statistics and can lead to suboptimal execution plans due to their inability to understand the data within them. This sets up a contrast with temporary tables, which, while still having some performance challenges, offer more flexibility in terms of optimizing queries through recompilation hints and proper indexing.

Full Transcript

Erik Darling here with Darling Data. In today’s video we are going to approach the final frontier when it comes to choosing between temporary objects in SQL Server store procedures. Now, very little practical use case for global temp tables, the double hash sign ones. We’ve talked through a lot of differences between temp tables and table variables. This video ties back well to the kind of the video before last, so maybe I recorded these in not like an optimal order, but where I talked about recompilations with temporary objects because something that we’re going to look at in this video ties directly into like, like when I talked about table variables not causing recompiles because table variables don’t have statistics. And I talked about thresholds for temp tables. And I talked about thresholds for temp tables recompiling. Well, there are some things that can happen that mess up my whole spiel that temp tables are generally better for performance when temp tables are small. So we’re going to, what I want to walk you through in this one is how temp tables are sort of like using optimized for unknown hints and table variables are sort of like using optimized for unknown hints. In other words, I want to punch you when you do that. And I talked about thresholds for temp tables recompiling.

And temp tables are sort of like parameter sniffing where I’m like, cool, we have a problem. We have an interesting problem to solve when we do hit problems with them. Okay. So I’m not saying that temp tables are these perfect angels that always work. Just most of the time when you care about performance and you’re joining your temp table off to other tables, that’s what you should be choosing because you at least have a fighting chance of fixing performance problems with temp tables. Whereas with table variables, you are just screwed. So without further ado, okay, I lied. There’s a little further ado. And the little further ado we have to get through is my usual song and dance prior to the informational portion of the video. You can sign up for a channel membership. There’s a link down in the video description. If you like this channel and you say, you want to say good job, Eric with money, you can, you can, of course, do that. If you want to say good job, but not with money, you can do the free stuff up there. And you can even still ask me questions, uh, during for, for my office hours episodes, um, all of the relevant links down in the old video description. Uh, if you need help with SQL Server, uh, there are two important things for you to know about me. One, uh, I’m the best consultant in the world outside of New Zealand. And two, my rates are reasonable. Uh, uh, the other thing that you should probably know is that I’m, this is all the, this is like the stuff that I am best at. Uh, so if you need help with any of these things, this is the face that you want to see when, when your SQL Server is having problems, uh, if you would like, uh, some content for, to learn more about SQL, so SQL Server training content at the beginner, intermediate, and even expert level, well, I’ve got, uh, about 24, 25 hours of it. And you can get all of it for about 150 USD.

When you use the 75% off code floating right there on the screen next to me, it’s quite, quite a deal. Uh, SQL Saturday, New York City, 2025 coming up May the 10th. Boy, oh boy. I just, it just keeps getting closer. Doesn’t it? Like, like the asteroid that’s going to kill us all. Uh, taking place at the Microsoft offices in lovely Times Square, Manhattan. Um, if you’re anywhere in the area, well, you should, you should probably set this Saturday aside to show up and learn more about the thing that you do, you do the other six days a week.

Just work with datas, data’s bases and whatnot and all that other good stuff. But anyway, uh, let’s talk a little bit about, uh, the performance of temporary objects. Now, uh, what I want to do is show you this stuff in the context of SQL Server 2019 operating, operating a compat level 150 to sort of like split, split, split this branch off in the right place. Um, if you’re on SQL Server 2019 or a higher and your database is in compatibility level 150 or higher, there is a new optimizer feature called table variable deferred compilation.

Uh, prior to that, prior to those numbers, like earlier than 2019 and compat levels lower than 150, uh, you don’t get that. Uh, SQL Server just like will default unless you throw a dumb trace flag at it. SQL Server will default to a one row estimate from table variables.

Uh, and you can also throw a recompile hint to queries in those cases in order to get a table level cardinality estimate, not a statistical estimate of like what data lives in the table variable, but just a table level cardinality. How many rows are in the table, not what values are in the table, uh, you can throw a recompile hint at it to get that prior to all this. With table variable deferred, uh, compilation, you get slightly different guesses, but still none of them are statistical.

There is still no statistics object. There is still no histogram. There is still just a table cardinality guess. How many rows are in the table, not what rows are in the table.

So, um, what I want to show you here is that I do have my database in compat level 150. Uh, and I do have, uh, that particular feature enabled and turned on. It is on by default. I have not undefaulted it.

Uh, so we’re going to look at everything in that context. Now I’ve already created this index here, so we don’t need to recreate that. And, um, like if we look at the results of this query and you can ignore some of this stuff over here, this is just stuff I used to build up, uh, some text that was annoying to type out.

Uh, the parent ID, uh, column has the top, uh, five plus, top six, five plus one is what, what they call it in math. Uh, not just six, five plus one. Uh, it’s also what they call it if you’re building rate arrays.

Uh, the top five plus one in here where you’ll see zero has six million rows. And then after that, everything else has just something in the low hundreds of rows. So this is going to make for some fun times for us.

Now, what I want to show you is outside of the store procedure, sort of what happens. And this is not a, this is not a sensitivity issue. This is a table variables do not get statistics and have no idea what’s going on issue.

So if we run this code and we run this query, even with an option recompile hint, uh, SQL Server the first time around will not have a problem because the five rows that were in the table variable just did not have a lot of matches in here. Even though the cardinality estimates are like wildly wrong, uh, they’re not so wrong that this plan suffered from it. Okay.

Uh, if we reverse this in the words of Missy Elliott, and we put in zero, which if you recall zero had like 6 million rows associated with it. And we rerun this query, even with a recompile hint, this will take 10 or 11 seconds. Okay.

Okay. Because SQL Server still chooses the same execution plan, still, still makes the same cardinality estimates, but it does not really do a, actually slightly different cardinality estimates because only one row comes out of this. So the five rows that were in there influenced how many rows that thought would come out of here, which was 1755.

So this does change slightly, but it’s still the same exact plan in plan shape. Right. So this thing all takes just about 10, almost 11 seconds at the very end there.

So this is kind of the root problem with table variables is that SQL Server does not know what rows are in them. It only knows at best how many rows are in them. Right.

Again, not like how much data, not what data. Right. Okay. So with that out of the way, let’s talk about how this works in the context of store procedures. For simplicity, I’m going to use a table value parameter to handle this rather than a table variable inside of the store procedure.

Uh, the behavior here is just about identical in every single way that you would possibly have behavior, uh, within the store procedure, uh, and recompile hint or not, this is all going to go, you know, probably about as you’d expect. So let’s do this. Let’s run this procedure without a recompile hint and without this thing in there.

So if we run this query and we put, uh, the five rows that we care about into the table variable, again, we get that same execution plan. SQL Server makes that 1755. Oops.

Uh, go. No, you stay pink, but you clear out, makes that 1755 row guess. But because so few rows are actually matching for the IDs that we put in there, it’s not a big deal. So if we do the same thing with, uh, parent ID zero and we run this, see, we’re going to see the same behavior.

This is going to take just about 11 seconds again, right? So this thing is spinning and spinning and spinning. And like we get the same, not, we get the same query plan again, the same slightly different guess.

The, the guess is actually going to remain, um, actually, look at that. We got a slightly different table variable deferred cardinality estimate guess there, but, uh, it still remains a bad plan choice because SQL Server is not like guessing great anywhere, right? Getting 6 million rows in these loops is much worse than getting like, you know, 11,000 rows or whatever in the, in those loops.

So let’s come back and let’s throw an option recompile hint into the mix. Because people often credit option recompile with solving all sorts of problems. And you know what? I agree.

Anyone who says, uh, option recon, like be careful with option recompile, watch out for, you’re going to have higher CPU if you use option recompile all the time. Uh, they just like to make things hard. Option recompile solves a lot of problems.

I’m not, I’m not going to lie to you about that. Uh, so let’s try this again. And with the option recompile hint again, you know, the, this five row query, uh, you know, just about all this, everything is the same in here. Nothing really changes everything fine.

But you know, the, the bigger deal is that, uh, you know, the, the, the zero plan, right? When we execute this again with zero option recompile still doesn’t do anything for us. And just to like, come back and show you like this table variable is backed by this table type, which has a clustered primary key on parent ID.

We’re still not getting any stats on this, even with the index in place. And you can validate that in the query plan. If you look at the properties and you look at optimizer stats usage, the only thing that’s going to be in here is for the post table.

Note that there is no optimizer stats usage for the table variable. And you can, you can even further sort of, um, uh, you can even further sort of validate that by trying to update statistics on the table variable. SQL Server will give you this red squiggle.

And this is not just like a parser issue. See, like SQL Server will be like, you just can’t, you can’t do that on television. Right. It seems like you get an error just trying to create the procedure. So there are no statistics on the table variable to update, to try to improve things.

This is, this is where the temp table thing has a direct comparison. All right. So let’s, let’s do this.

Let’s come down here. And let’s create or alter this procedure. This is our, uh, this is our table variable. I guess it’s not really a one row table variable, uh, varying row temple table variable with a, with a temp table where, uh, rather than rely on the tape of the table value table variable or table value parameter. In this case, we’re going to create a temp table, uh, again, with the clustered primary key on it.

And we’re going to insert the contents of the parent IDs table variable or table value parameter in, into there. Okay. And then we’re going to do our work off the temp table here.

All right. So there should be fairly, fairly obvious. Note that I have the same update stats and option recompile things available to us in here. So let’s create or alter this.

And this is, um, this is the sort of issue that I wanted to show you with when you have very small temp tables. Now, if you, if you recall the video from one prior to this, where I talked about recompiles with temp tables versus table variables. One of the things I showed you was an extended event that said, that showed us when, um, uh, things were recompiling because statistics changed with the temp table.

And that the smaller the temp table was like, like you got a lot more changes at first, but then it was small. And then like, when it was bigger, like you sort of needed like more rows going in to trigger the, the stats, the, the statistics, the statistics update change. So if your temp tables are always, well, I’m like fairly large amounts of data, like anything over like a thousand, 10,000 rows or something like that.

You’re going to see a lot of recompilations because of statistics changing with the temp tables. Like that’s just going to happen way more often because you have like way more data going in and out to, to count towards the statistics, to count towards the, like the number of modifications that would trigger the statistics update. So what we have here is a situation where we have a very small temp table, but that’s very small temp table does not have a statistics update triggered on it.

Like when a larger one would have. All right. So let’s like, if we run this first with the five rows, we get the same very fast plan, right?

SQL Server chooses things in here and, um, like we get like, okay performance from this one still. So now because I want like, you know, I’m, I’m, I’m team temp table when it comes to like performance being good. What I want to show you is when that can go wrong and how you can fix it.

Uh, so let’s run this and let’s, let’s see what happens. Where, uh, we have this execution plan now, and this is going to, uh, give us, uh, the cash and reused execution plan for the query that just ran. Right.

This is also going to take just about 11 seconds. Right. But this, at least for us is a solvable problem, right? With the table variable, there was no solvable problem. There was no, there are no statistics to update on the table variable.

And the option recompile hint did not help us discover what rows were in the table variable that we, that would change the execution plan. But we do have some hope here because temp tables do get statistics usage. Notice that now, instead of there being just one thing that opens up, we now have two things under optimizer stats usage.

We are going to have one for the temp table and one for the actual table. So if we zoom in and I move this out of the way of my giant head, uh, here, here are the statistics on the temp table that did not exist for the table variable. Right.

And here are the statistics for the base object, uh, on the, on the post table. Right. So there are statistics here that we can use. The problem is that because this temp table is so small, it cached and reused an execution plan where it still thinks the five rows that were in there before are going to come out of there, which is not a very good guess. Now you have to do two things in order to address this problem.

You don’t need the full scan on this. I’m just choosing to do a full scan because it’s a very small temp table and who cares, right? It’s not going to, not going to take any longer fully, fully scanning five rows versus fully scanning one row.

Guess what? Not a big deal, but we do also need this option recompile hint here because this is what’s going to give us a fresh plan for this query. Just the option recompile hint or just the statistics update is not enough to get us across the finish line for this one.

So let’s run this and now let’s revisit running this in the exact same order, right? We get this execution plan. SQL Server still does an okay job with this one.

Now, when we run this for parent ID, just zero SQL Server will do a different job. Notice that now we get a completely different plan shape. We no longer have a nested loops join and we no longer have the seek plus a key lookup.

We have this query here, goes parallel, takes just about 800 milliseconds. And instead of that, we have a scan down here and this solves our performance issue. Now, you may get very lucky with your temp table usage and you may get something like this that happens where, let’s say that this happens in reverse order, right?

Let’s recompile all this stuff. I’ve taken out the update stats and the option recompile hint here. If we just run this for parent ID zero first, SQL Server is not going to have any problems with this.

SQL Server chooses that same plan for down here. And then if we rerun it for the five rows up here, SQL Server is going to reuse that plan here. So there are going to be cases just like with parameter sensitivity where you get a good plan that works fine for anyone.

And there are going to be cases where you get a plan that does not work so fine depending on the distribution of data. The big difference is that with, again, with temp tables, there are at least things at your disposal that allow you to solve the problem. With table variables, there is absolutely nothing at your disposal to solve the problem aside from using temp tables, right?

You can’t update stats on the table variable because table variables don’t have stats. And you can’t use a recompile hint to get anything better because SQL Server guessing one row and not knowing the contents or SQL Server guessing five rows and not knowing the contents of those rows. Again, the recompile hint with the table variable just gets you how many, not which.

That doesn’t help. So whenever you are dealing with performance issues around table variables, one of the first things that you should be thinking is, should I change this to a temp table? And you should be thinking, yes, I should.

Because even if you run into performance issues with the temp table, you have things at your disposal that you can use to fix them. Now, is this all too much? Is this all something that Microsoft should be handling better or the SQL Server could be doing better?

Potentially. You know, it’s a very difficult thing to split correctly because you have to manage a lot of sort of expectations and behaviors that people have relied on for a very long time. Sure, there are probably some scenarios where this could be better.

Like, but, you know, figuring out a way to do this during query execution or during query planning or optimization or to make this somehow more adaptive would maybe be a little too difficult. Like, you know, one thing that is sort of interesting is, you know, SQL Server 2017 plus has like adaptive joins, but adaptive joins won’t make a call if like there’s not a covering index in place. Like you’ll never see, well, at least as of this writing, like I’ve never seen an adaptive join that was like, you know, like gave you an option between like, you know, hash and loop join, where the loop join portion was like a seek with a lookup.

It was always just like you could like seek or scan, but if there’s a, there’s going to be a lookup, the adaptive join is short circuited. You don’t get one. So is there stuff that could possibly be done to the optimizer that would make this an easier situation?

Probably, but you know, you, there’s, there’s a lot to, there’s just a lot of stuff that would make this really hard to get exactly right. But this is where the, the skills of query tuners is still very valuable and very useful to people who have performance problems. And as always, my rates are reasonable.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video where we are going to talk about, well, I might, I might take a short break from the, the, the store procedure stuff to talk about some of the open windows over here, because they are, they are quite interesting to me anyway. So I might take a short break to look at these things, but after that, we will, we will be getting towards the, the, the very wrapping up end of our store procedure series.

So we’ll see how, we’ll see how things go over the next couple of days. We’ll see how, see how my motivation lines up with reality. Anyway, 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.

SQL Server Performance Office Hours Episode 8

---

Hello friend, How many Hello friend, are there techniques to compare data returned from a sproc when making perf changes to make sure you are not messing up the results? I currently run the old sproc and the updated sproc and copy the results into a text diff app, but was wondering if there was a better way.

Hi Erik, What is the feature of mssql that you would like to see customers or dba use more ?

What are your top three books on performance tuning that you would enthusiastically recommend to someone about to embark on this journey?

Hi Erik, in AdventureWorks I have these two statements: select top 5 ProductID pid into #p from Production.Product; select * from Production.Product where ProductID in (select ProductID from #p); As you can see, the subquery is wrong, but SSMS doesn’t highlight the error, the query runs basically unfiltered. Do you know the reason?

Hi Erik, please do NOT consider my previous question. Thanks. I’m tired….

To ask your questions, head over here.

Video Summary

In this video, I dive into some SQL Server performance tuning tips and tricks during a lively Office Hours session. We tackle questions ranging from comparing data return between old and new stored procedures to improving code formatting for clarity and efficiency. Whether you’re looking to optimize your queries or just want to know more about the features of Microsoft SQL Server that could benefit your work, there’s plenty to learn here. I also share some of my top recommendations for books on performance tuning, providing a resource list for anyone embarking on this journey. So if you have any questions or need help with SQL Server, consider joining our next Office Hours session or exploring the resources available on my website, erikdarling.com.

Full Transcript

Erik Darling here with Darling Data, and I don’t know if you can get that pungent aroma in your nose. What is that delightful smell? That is the smell of Office Hours. Yeah. Alright. If you like this content, maybe you want to ask a question on this here Office Hours thing. I hesitate to call it a podcast because, God forbid, another person in tech with a podcast. Wow. No, not doing that. It’s not a thing. You can support this channel by signing up for a membership. You can like, you can comment, you can subscribe. All of the useful links to do these things are available in the video description. Gosh darn it. Look a little bit down below the handsome face. If you need help with SQL Server, maybe you watch these things and you think, gosh, that Erik Darling sure does know his SQL Server stuff. Well, you can hire me to do all sorts of SQL Server things for you. And as always, my rates are reasonable. If you want some training, I have it. I’ve got it. I would love for you to have it. I would love for you to have it so much that I give you this gigantic discount, 75% off. That brings it down to, what, $150 US dollars. You get that for the rest of your life.

Again, video description. What a nice place to be. We still have SQL Saturday, 2025. That is not the 2025th SQL Saturday. It is the year that it is taking place. The date that it is taking place is May the 10th with a performance tuning pre-con by Andreas Walter on May the 9th. And you can go to both. And you can see me at both. And I don’t know, maybe we can go smoke cigarettes outside together. With that out of the way, let’s go party here. Let’s have some office hours fun. And I’m going to have to shrink this down a little bit. It’s not really friendly to the… Oh, dear. What happened there? Oh, boy. All right. Well, you know, nothing’s perfect. Anyway, let’s get to this first question here. If Zoomit will decide to be my friend. Well, that word wrap cut off a little bit, but we’ll deal with it. Hello, friend. How many hello, friend? How many times can you type hello, friend?

Are there techniques to compare data return from a sprock? Godly. Just call it a store procedure. Why do you have sprock? Sounds ugly in the mouth. Sprockets. When making perf changes to make sure you’re not messing up the results. I currently run the old store procedure and then the updated store procedure and copy the results into a text diff app. There are easier ways of doing this, depending on the nature of the results.

If… Okay. So, one, if you have a store procedure that returns multiple result sets, we should probably talk. It’s not really a great thing. You know, like my analysis procedures do, but they’re not being consumed by an application. So, let’s hope and pray that your store procedures are just returning a single result set.

You could either use a global temp table and like keep it, like even like I understand, like you’ve made the performance changes. You’ve, you’ve been like, you’ve figured out this new version is faster than this old version. Great. What I would use is either a global temp table or a real table in the database, write the data out to it, and then use some variety of SQL, of the lovely SQL standard intersect and accept operators to see if the results are equivalent.

One thing to keep in mind is that depending on the nature of the query plan, the results may be correct, just in different orders. So, if the order of the results matters to you, you may, like if you have like an ID column or some sort of sorting element, you, you might need to make sure that, you know, like if you’re comparing the results, like one-to-one that you have that included in the query. But if you’re using intersect and accept, which are fantastic because they handle nulls.

So, if you’ve got nulls in your results and you’re like, oh gosh, how do I compare all these nulls? Intersect and accept are your friends for that. Let’s go and let’s answer the next question.

Hi, Eric. Hi. Hello. How are you? What is the feature of MS SQL? Miss SQL. Wow. Hello, Miss SQL.

Pretty lady you are. That you would like to see customers or DBA use more? Well, there are a number of things that I have in mind here. I wish that, well, it’s funny, right?

Because there’s stuff that I wish were closer to being the default in SQL Server than currently exists today. Just for example, I firmly believe that since 2005, the default for any new databases should have been the recommitted snapshot isolation level. So, I do think that, well, you know, starting with SQL Server 2022, query store is on by default.

So, that’s a good step forward. But, you know, we’ll see how this 2022, 2025 adoption goes. 20, you know, still, 2019 still leading the pack as far as I can tell.

So, those are two right off the bat. I also, like, I really wish that, so, the system health extended event does capture deadlocks. And it does capture a very limited amount of blocked process report stuff.

You can expose both of those using my store procedure, SP Health Parser, which will go in and get as much information out of those two things as you can get. But I wish that there were more dedicated stuff for the blocked process report and the deadlock XML report. In fact, I wish the blocked process report and the deadlock XML report were wrapped up into Query Store because they’re pretty important things for query performance.

Right? Like, it would just be nice if it were all centralized in one location where you could go and say, oh, this is where performance was awful. So, those are a few things that I wish people would use more.

You know, it does help to create specific extended events to capture the blocked process and XML deadlock report because it is much easier to access them via events specific to those things being captured than it is to go through all the system health extended event stuff. Because the system health extended event logs a ton of other stuff.

And, like, sometimes you lose information or it’s, like, just takes forever to parse through things and get to it all. So, those are a few things there that I think would be useful for people. Another book question.

What are your top three books on performance tuning that you would enthusiastically recommend to someone about to embark on this journey? The good news is for you that I just recorded a video. And coinciding with recording that video on books that I like, SQL Server books that I like, there is now a dedicated page on my website.

So, if you go to ericdarling, that’s eric with a K, erikdarling.com slash books, you will see a list of books that I enthusiastically recommend for SQL Server people. And, uh, this is sort of a twofer, but, you know, um, I do five at a time and these just happen to, these just happen to make the five question cutoff. Uh, so, uh, here we have our beleaguered friend bemoaning their, their exhaustion.

I, I, you know what, friend, I, I hear you. I’m, I’m tired all the time. I, I, worn out.

Uh, please do not consider my previous question. Thanks, I’m tired. Well, uh, we’re going to answer this question anyway because this, this question, like, speaks directly to a lot of things that I’ve talked about here, uh, both in the past and present and probably will again in the future. And that is around code formatting and proper aliasing and all that other good stuff.

They’re saying that they have two statements where they select the top five product ID. Notice that product ID is aliased as pid here without an as. Nuh, nuh, nuh, nuh, nuh.

Always put an ad. Like, like, first off, like, table aliases should be as. Column aliases, you know, it’s a lot more clear when you say pid equals something than pid as. Like, like, you have to read too far over this way to get to the as or too far down.

It helps to have the column names all lined up. And then, uh, the second thing that they have is select star from production dot product where product ID and select product ID from P. Well, there’s no product ID column in P because you name product ID pid in P.

So what’s, what you’re, what you’re not doing down here is aliasing your, your pound sign temp table. And so SQL Server is resolving this product ID column to the product ID out here. And, and that’s where things are going awry.

So please, for the love of God, write your queries clearly. Alias things properly. The sooner this, the, the, the better you are at writing your queries, the less confusing this stuff becomes. You can, you can, you can become a smarter person overnight just by taking the time to be a bit more verbose in your T-SQL.

Stop taking shortcuts. Stop leaving words out. Stop putting things in funny places.

Stop putting commas over here. Commas belong at the end. Uh, column aliases belong as column, as a column name equals expression. Stuff like that.

And, you know, when you do, when you do things like this, when we are sloppy with our code in these ways, we hit all sorts of strange, unexpected bugs. This can, this does not just go for, um, like the, the, the mere writing of the query. This goes, this goes to all sorts of things.

A number of strange performance, uh, oddities that I have encountered in, in silent, in bugs that have been silent for years that, um, have cropped up just because people were lazy with things like this. Or, uh, lazy in, in using the correct data types for things, uh, is, is pretty big. So, uh, when you write your queries, make sure, make sure, you know, you and SQL Server are able to understand them.

And make sure, like, when you write your queries, think if, if the dumbest person, person in the world came and looked at this query, would they be able to figure out what it’s doing? If Erik Darling came and looked at this query, would he be able to understand what it’s doing? What would Erik Darling say about this query if he saw it?

Well, these are questions that you should ask. Anyway, me and Bats, I’m going to sign off. Uh, I do have some more office hours questions to get through, but since I, I do the five at a time because Bats loses patience with me, we’re going to, we’re going to, we’re going to stick those in a new video. But, uh, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. I hope that you will format your queries properly. And I will see you in the next video. 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.

ll About SQL Server Stored Procedures: Table Variables for Logging

---

Video Summary

In this video, I delve into the nuances of using table variables in SQL Server stored procedures, particularly focusing on their role in logging errors and handling rollbacks. I explore how table variables can be useful for tracking progress within transactions, especially when you need to maintain a record even if an error occurs or the transaction is rolled back. However, I also highlight that while they are handy for certain scenarios, table variables might not always be the best choice for logging data, especially in complex operations involving large datasets. The video includes practical examples and discussions on when it’s appropriate to use table variables versus other methods, ensuring you make informed decisions about their implementation.

Full Transcript

Erik Darling here with Darling Data. And in today’s exciting, outstanding, completely AI-free video, we’re going to talk about store procedures, still. Carry on talking about SQL Server store procedures. But we’re going to talk specifically about using table variables in the context of logging things about your store procedure in the face of errors and rollbacks. Because for some reason that I cannot explain, every time you are talking about the performance differences between temp tables and table variables, and you’re like saying, hey, if we change this table variable to a temp table, we can make this procedure go faster, someone will decide to chime in with the time in with the time in with the time in with, but table variables, they survive errors and rollbacks. And you’re like, so what? Except with more words, so instead of a long so, inject some colorful language into the so what? Because completely irrelevant to the topic at hand. We’re talking about, when we’re talking about performance, we care about performance, we don’t care about errors and rollbacks. It’s like we’re looking at a reporting store procedure that doesn’t even do any, there’s not a transaction in here. There’s not an error, there’s not a rollback, there’s not a commit. There’s nothing. Why? Why do you bring this up? Why? Why did it, why do you decide to inject the conversation with this meaningless knowledge? You just, you just need to act like you learned something at some point? I don’t understand your point of view on this. Anyway, before we do that, let’s talk about you and me and the birds and the bees. So if you like this channel content just enough to spend four bucks a month, on it, you can use the link in the video description to join the channel as a member. If you do not, if you have not quite reached, not quite reached consensus or formed an opinion on the channel, you can do other stuff. In the meantime, you can like, you can comment, you can subscribe. And if you feel so inclined, you can even ask me questions at this link, which is also done in the video description, that I will answer on my office hours episodes.

When I answer five of your questions at a time and we have fun. If you need help with SQL Server, this is an unruly beast that needs taming that needs performancing. You can, of course, pay me money to take care of them. Health checks, performance analysis, hands on query index server tuning, you name it, responding to performance emergencies and training your developers so that you do not run into as many performance emergencies. is all the name of my game and as always, my rates are reasonable. Promise. Take a look around. If you would like to get some training from me in lieu of perhaps other things, if you would like some real high value content, you can get all 24 hours of my training for 75% off.

That is around 150 US dollars and that comes to you for the rest of your life. Link to do all that stuff again down in the video description. Upcoming events, we still have SQL Saturday, New York City taking place in the Microsoft offices in lovely Times Square in Manhattan.

It would be a great time. You can come, you can take, we can take selfies. I don’t know, we can do whatever cool fun stuff people are still allowed to do at conferences these days. Barring any, of course, code of conduct breaches. We don’t want to do that.

We want to have a nice family friendly time at SQL Saturday. With that out of the way though, let’s talk about table variables and logging stuff. So here is pseudocode.

And you know what? You just reminded me that I need to fix a small typo in here before I keep talking. So we’re going to do that and we’re going to pretend that didn’t happen and then we’re going to look at the rest of this stuff. So let’s say that this is our pseudocode and like reasonably intelligent people, we are going to, within the context of our store procedure, set no count in exact abort on.

Then we are going to begin to try. Every day I begin to try. Sometimes things happen along the way that interrupt that. And then we are going to end try and, well, you know, there are a lot of reasons to end trying, I’ll tell you that.

But then in between all that, we have a begin transaction and a commit transaction. Now, of course, you don’t want to really need to do this for a single query. If you have a select or an update or a delete or an insert, you don’t really need this, right?

Because SQL Server is going to be working in auto commit mode where that query will happen within its own transaction anyway. But if you have a group or if you have a flock or a murder of transactions, of queries rather, that you need to put in a single transaction because they all need to complete or not complete as one group. Like Wemmings, they either need to like make it to the top of the hill or fly off the cliff together.

Then you would want to do this. If within this transaction, you want to figure out where along the way you have done things and how long they took and how many rows are affected and things like that, then you can log that stuff to a table variable. And if you hit an error or in like, you know, you like, you know, you hit an error and all this stuff rolls back, then you can still put that data from that table variable into a logging table that you can review later.

You don’t just have to return it out to like whatever client is running down in the commit transaction or rather down after the commit transaction in the begin catch block. We will, of course, do this. Say if trend count is greater than zero, we’re going to roll stuff back and then we’ll insert into our logging table whatever data we have logged in our table variable that has survived the rollback. Because remember, we don’t roll back things that got inserted or updated or deleted from table variables here.

That table variable will be alive until we do this. We can put that into our logging table in the catch block and then review that actual logging table later. You can put all sorts of like good information in here, you know, proc ID, error number, error line, error message, all that other stuff.

There’s lots of things that you can put in there that will make life somewhat easier for you when troubleshooting a problem with a procedure. One thing that I’ve not really come up with a good way to manage is like logging the data that was in there. Like if it’s a small amount of data, it’s not a big deal.

But if you’re doing this for like ETL and you need to move millions of rows around table variables, table variables are just going to hurt more than they help. So probably don’t do that. The second way that you could do this is if you have a store procedure that does something like this where, and again, you don’t need to put like, like wrapping single queries and this stuff is not really useful.

But if you have like groups again, like just like the last one where every query in the procedure had to go and like, you know, ride or die. If you have groups of queries, maybe not like the whole list of queries, but like, you know, groups of queries where along the way, like in all of these do stuff blocks need to ride or die together. Let’s say there’s a, there’s like two or more queries in here that need to complete and, you know, do that stuff.

And you need, and you want to log progress for each query in here, then a table variable would still be your friend. Because if anything happens within any of these down here in the catch block, we can still do this. If, and this is a big, if you are only logging stuff around each transaction, meaning like, let’s say that like before each begin transaction and after each commit transaction, you’re like set, you know, current step.

Equals, you know, transaction one. And then after the commit, you’re like set current transaction equals step two. Then the table variable becomes less useful because it’s not logging anything within the begin transaction commit.

So there’s like the way of thinking about this is anything that’s happening within a transaction could be useful to log to a table variable. If you’re going to do something with it, otherwise it’s probably not. Otherwise you could probably either don’t need a table variable or you just don’t need to be logging stuff, I guess.

This is the easy way, easy way of saying that. But the key thing here is that if you’re not, if you’re not logging, if you’re not doing stuff within each of those transactions, then you can still do the logging down here if you want. Right.

Like that’s easy enough. You just pull out whatever data came along with that. But, you know, for this part, if you are logging stuff about each step between the begin transaction commit, then you would want to put the stuff into the logging table from the logging table variable. And I am not sad to say that this is the very end of where table variables are useful for logging errors, rollbacks and data about things.

At least in my experience, practically speaking, you could probably overly engineer other, you know, solutions to do things like this. But the table variable will not be necessarily useful or even required, will not be a necessity for doing that. That would be your choice to use a table variable when you just didn’t need to.

You could have just used regular variables and you could have just logged very simple, easy information about the error you hit, the procedure you hit the error in, all that other stuff where you didn’t need to put that in the table variable. You just chose to do that for some weird reason, because you are a white knight for table variables and you, you just need to let them, you just need to make them shine. Right? You need to force them to shine like a, like a boy band, just manufacture them being good at anything.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will not manufacture use cases for table variables. I hope that you will only use them when they are necessary, required and pertinent to the solution at hand.

And yeah, in the next video, we will talk about the thing that really matters when you are choosing temporary. And that is, of course, performance. We’re going to go over some material that I covered in a video somewhat recently that makes sense in this context as well. So I will see you then. And until then, I hope, I hope you are smiling.

Hope you are a happy camper, just like me.

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.