Category: sp_QuickieStore

Just Visiting

---

I made some updates to sp_QuickieStore recently, which you can download here.

The main updates to the code are to:

• Add support for SQL Server 2022 views
• Add the ability to search by query hash, plan hash, and SQL handle

The SQL Server 2022 stuff isn’t important just yet, but… Hey, maybe someday.

The new search functionality is really important though, at least for how I use sp_QuickieStore much of the time. Often, you’ll find hashes and handles in other parts of the database:

• Plan cache
• Deadlock XML
• Blocked process report
• Query plans

There’s still no good way for you to search Query Store by anything. Not plan or query id, not query text, not object names. Nothing.

Don’t worry, I’m here for you.

Some other minor updates were to:

• Improve the help section
• Improve code comments throughout
• Remove the filter to only show successful executions (sometimes you need to find queries that timed out or something)
• If you filter on any hash or handle, I’ll display that in the final output so they’re easy to identify
• Replace TRY_CONVERT with TRY_CAST, which throws errors in fewer circumstances

That’s about all the stuff you need to know about. Aside from that, all my changes were slight logical errors or plumbing to implement the new features.

Thanks for reading!

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.

Things Are Getting Better

---

Thanks for reading!

Video Summary

In this video, I share my latest improvements to the SP_to_the_Underline_QuickieStore stored procedure for performance troubleshooting in SQL Server. After realizing that navigating through the initial version was cumbersome and required jumping around between tables and XML data, I decided to streamline the process. By formatting query information into XML and displaying it directly after fetching query plans, users can now easily identify slow queries without having to hunt through multiple lines of code or confusing table structures. This update makes troubleshooting much more efficient, allowing for quicker identification and resolution of performance issues in your databases.

Full Transcript

Erik Darling here with Erik Darling Data. And it would figure that as soon as I thought I had closed the book on the initial round of coding for SP to the underscore, what’s it called? Quickie store? Something like that? At least it rhymes so I can remember. I had these like, like, like epiphanies last night. He’s like, Joan of Arc visited me and hit me with a sword and said, do better. Because I was going through the video yesterday about, like, the performance troubleshooting bits.

I was like, ah, it’s a little clunky because you’ve got to look down at this table and see what was slow and then go up here and figure out where it was slow. And I was like, man, that sucks. Like, if I had to do that, I wouldn’t want to do it. I’d be demoralized. I wouldn’t want to deal with that. It’s stupid. It’s dumb.

Dumb. So what did I do to make things better? Well, I think, I think I made things better anyway. The good news is you no longer have to jump around on your screen in order to figure out what was slow and then hunt and peck through a million lines of XML to figure out what was slow.

And I’ll show you how I did that. Wonderful magic trick. So if you’re a troubleshooting performance and you use a troubleshoot performance parameter, you’re going to get some different, a different layout of your information back.

All right. So looking down here, we still create a temp table to hold data about what we did, right? We still have a temp table that holds the current table data, the start time, end time, and then the formatted runtime in milliseconds. So if we have something that runs for over a thousand milliseconds or one second, then we will put some commas in to make numbers a little bit more readable.

And then if we go in a little bit further, this is the change I made that I think makes things a lot better for everybody. If we go down here, what I’m doing instead of dumping stuff into a table and making you deal with it and jump around is I am formatting the information about the query in the runtime into XML. And I’m going to display that to you right after we get the query plans for whatever executed.

So you can see what I’m doing in here is I am hitting that. So what you’ll see in a minute, but it’s going to be after we update the troubleshoot performance table to get information. I’m going to pull data from there based on what the current table situation is. So we’ll get the runtime, the current table, the length of the SQL, and then the statement text of the SQL that just ran.

If you go down a little bit further, just to show you where this happens, and this will happen for every single block too. So we’re going to execute that SQL to generate that XML and just select it. Right. So that’s what I did.

And I think we’re working out pretty well so far. But if we run SP to the underscore quickie store and we see what comes back, it’s a lot cleaner to deal with the performance, any potential performance issues. Granted, there aren’t any here because, again, I keep my query store tight and light.

But in your query store implementations where you have hundreds of thousands of rows, maybe this might be a lot might look a lot different performance wise. So it’s still not perfect because I still can’t separate the insert query from the dynamic SQL select in some cases. So in some instances, you’re going to see the XML for the query that ran.

Right. It’s going to be this. And then right below that, you’re going to see this current query line. And if you click on current query, we’re going to see the milliseconds runtime.

We’re going to see the current table. That’s the process that we’re currently executing. And then we’re going to have the full statement text for that. And the reason I did this is because I wanted it to be a little bit easier to figure out what was slow and where in the XML, like where the query plan for the slow thing is.

Now, where this still isn’t perfect is in cases where I use dynamic SQL to insert into a temp table. So in some cases like this one right here, you might see query plan, query plan, current query. And current query is going to be this insert.

So you can see in this insert, we are selecting a distinct group of plan IDs from query store plan. And we are selecting stuff that is not like this, you know, crappy maintenance stuff. But then if we look at the query plan directly above that, it’s just going to be this insert that didn’t really look like it did much of anything.

Right. We don’t even see the table that we hit in there. But if we look at the query plan just above that, that’s where we’re going to see that we hit the table that looks at where that holds the query text that we were filtering against. So in some cases, you will still have to do a little bit of noggin thinking.

And if you see a current query that took an amount of time that is alarming to you, then you would have to like, you know, skip one and then go up to the one right below the previous one. So that’s what that’s where it is so far. You know, it’s probably about as good as I’m going to get it because I can’t think of a good way to not get the query plan for the insert, not have like the double query plan.

But hopefully you won’t run into so many performance issues that you have to deal with this all that often. But this this is pretty reliable. And if we go all the way down to, you know, I think some of the larger queries, we’ll still get the full query text in there.

And so this is, you know, at least I think a pretty decent way to get you, you know, have you be able to see very quickly the runtime in milliseconds for whatever query. So if you see like some big number right here, then you know, oh, I’m just going to go take a look at that. And then you can look at what ran and you can say, oh, I guess that was slow.

And then, you know, go get the query plan for it and tell me how to performance tune things, because apparently I’m such a bad performance tuner that I have to build all this performance tuning apparatus into my store procedures so that I can troubleshoot performance on them. I guess I guess I’m just that goofy. Sorry about that.

Anyway, that is the new and improved performance troubleshooting for SP to the underscore quickie sore. And I don’t know, I hope you think it’s at least interesting, even if you never have to use it or deal with it and all that others. I don’t know.

It’s fun, isn’t it fun? Fun to do things. Have fun doing nice things for people. I don’t know. Maybe you’ll take this and build some performance troubleshooting framework for your own store procedures where where where things actually matter.

And you can say, thanks, Eric, darling. Thanks for. Thanks for that awesome idea.

All right. It’s bye. That’s enough. You know, just goofy stuff. OK.

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.

Centipede

---

Thanks for watching!

Video Summary

In this video, I delve into implementing verbose debugging for stored procedures that are intended for public use. The goal is to provide users with comprehensive information so they can either file high-quality issues on GitHub or troubleshoot and potentially fix the code themselves if needed. To achieve this, I’ve introduced a `parameter debug` feature that prints out the length of every SQL string and the actual string itself before execution, ensuring any errors are caught and easily traceable. This helps in verifying the exact SQL commands run during procedure execution, making it easier to identify issues or optimize queries. Additionally, the debugging output includes all input parameters, internal procedure states, and relevant metadata like database collation and engine version, providing a detailed snapshot of how the stored procedure operates under different conditions.

Full Transcript

It was a dark and stormy night. Yeah, so here is the video, hopefully, maybe, I don’t know, the last video in the series that I’m doing, maybe? Who knows? Only the future, only time will tell, only the future knows, but the past has not yet forgotten. Shut up. Anyway, we’re talking about, implementing debugging in store procedures. And for store procedures like this, where they are not just for me, but for the sort of general public’s consumption, I want really, really verbose debugging. I want people to get as much information back so that if they need to open up an issue on GitHub, they have a lot of information at their disposable, at their disposable, brain dead, at their disposable about what happened, where, where it happened, and so they can file higher quality issues for me to work on. Or maybe they can say, you gave me so much good information back. Gosh darn it, Eric. I love you. I want, I want to kiss you. But more importantly, I want to fix this code myself. So, great. How do we do that? Well, we have this parameter debug, which unfortunately does not take the bugs out, but it helps you find the bugs.

And the way this, and the way this works. And the way this generally works, if we scroll on down, is when you have debug enabled, the, one of the most important things that happens is that we, what I print out is the length of every SQL string, and then the SQL string prior to it executing so that if it throws an error, we will catch the last thing that threw an error, right? We have that, we have that here, and then we have that in the throw. So, this is maybe a little redundant, but I’m okay with a little redundancy here because people aren’t going to be running this in debug mode constantly. So, the reason why I do this is because, um, uh, for every SQL string that runs, I want you to make sure or be able to verify that the entire string prints here. Uh, I’ll show you an example down a little bit lower of how I handle printing one of the slightly longer strings in the store procedure, but I want you to understand if the whole string is printed on the screen, you can probably eyeball that a little bit, and then, uh, see what exactly the string was. So, that’s most of what debug does, is print out dynamic SQL strings.

Uh, if you go down a little bit further, I believe that’s here. Uh, uh, for this particular SQL string, it is, uh, longer than the 4,000 characters allowed, uh, or whatever. I don’t know. I just, sort of semi-arbitrary. Uh, I, I can never quite figure out exactly how long print strings are allowed to be. I’m not, just not that good at databases, or at least this stuff. And so, I print out, uh, a substring of the first 4,000 characters, and then a substring of the second 4,000 characters. This can get a little confusing because sometimes you will have a line break, probably where you shouldn’t see one, and it might look a little funny. But, uh, whatever. That’s pretty easy to deal with.

Okay. So, we print strings out. We print out dynamic SQL. We print out the length of the string so that you can figure out how long the string is, and you can go and, you know, see if you need to, you need to fix something with the way that strings are concatenated together, or something like that. Maybe whatever. Bah, bah, bah. When we get down to the final debug section, the stuff that I return to you is, uh, first, uh, all the parameters available for the store procedure. Right. So, uh, I identify those as procedure parameters, and everything that got passed in, uh, I will print out here, uh, you know, show you exactly how things looked, um, you know, all this stuff, uh, the version and version date, so that if you need to, uh, file an issue or whatever, like, something like that, you have that stuff available to you right there.

Uh, next, uh, declared parameters. And I think this is probably the more important section because this shows you how some of the stuff that I do internally in the procedure ran and sort of, uh, how it resolved. So, stuff like whether or not you’re on Azure, what engine version you’re using, the product version, how the database ID turned out, what the database and procedure name look like when they’re quoted, the collation of the database, the length of SQL, the parameters, all this other stuff. So, this is good, helpful information to have, uh, when things run. Now, the next thing that shows up is, uh, uh, this is, this was probably the most repetitive code that I’ve ever written in my life, but it seemed useful.

Um, and the reason it seemed useful is because, wow, it is pouring rain. Uh, the reason it seemed useful is because, uh, usually I would just say, uh, this, right? I would just say, at the end of the store procedure, if we’re in debug mode, select this stuff. The problem is, if data doesn’t end up in these tables, then all you get is an empty select back. You don’t get the table name back if there are no rows here. And that can be kind of confusing and hard to figure out exactly where things showed up or didn’t show up.

So, what I do is, I make sure that data shows up in, in these tables. Then, if it does, I select from them. And if it doesn’t, I say, that table was empty. So, you get an alternative string where it just prints out, uh, that the table was empty if it was empty. And I do that for every single, I think every single table in the store procedure. I, if I’m, I’m, I, there are so many, I may have missed one or two, but if I think I got all of them, um, at least I made a checklist and went through all of them and, uh, did all that.

So, that was nice of me. It was responsible of me. Uh, so, yeah, uh, we do, we do that. And then, uh, one thing, uh, just to show you kind of what those results end up looking like. We’ll run this, which I think we’ve seen in the last video. Uh, but what we’ll get back is, uh, sort of a regular set of output up here. Uh, we’ll get back this sort of, um, semi-helpful, uh, support. How to get help, how to troubleshoot performance, how to debug things, blah, blah, blah. Uh, and all that good stuff. Um, if you debug, the first thing you get back is procedure parameters.

So, this is all the stuff that we passed into the store procedure, right? Uh, this will show us nulls for where we had nulls and expert modes and formats and all this other stuff. And there, and then declared parameters, which will show us things that we figured out while, during the course of the store procedure running. So, we know we’re not in Azure. Uh, our engine version is three, which I think is enterprise slash developer. Uh, product version is 15. The database ID that we went after was five, and that should correlate to stack overflow.

Uh, we see the unfortunate collation of my database. Um, uh, we, you know, just a whole bunch of stuff, right? Things that we got, things that we did during here, right? All right. Useful, helpful stuff. Then a little bit lower, we’ll have, uh, the temp table stuff. So, you know, the plans that we worked on, uh, we didn’t look for, uh, plans associated with any specific store procedure. So, we, uh, don’t have that anything in that table. Uh, we did look for some specific plan IDs. So, we had them in that table. Uh, you know, we had some other empty temp tables just based on things that we didn’t search for.

Um, one thing that I, I don’t know, I didn’t really highlight this in any of the other videos, but, uh, one thing that I do, one thing that used to always frustrate the hell out of me when I was working with Query Store is that, um, uh, it would pull back query plans for, uh, create or alter index. And it would pull back query plans for create or update statistics, which I always found weird. Uh, and so I have some filters in there to automatically to screen those plans out because how the hell are you going to troubleshoot that? How are you going to get performance tune that? It’s useless to you. Who cares? Stop logging that. Dimwitted.

Uh, so after that, we get back all the stuff that we filled in. Uh, then we can see, you know, um, you know, a little bit repetitive, but, you know, we’ll see the options we had for Query Store, the query plans that we pulled, the query plans that we got from Query Store plan, the query text that we got, or sorry, the query information that we got from Query Store query, the query text that we got from Query Store text. Uh, you know, at some point I go out and try to figure out if there’s additional information in the plan cache about anything that ran. Usually isn’t because the plan cache is an unreliable memory pressured piece of crap, but whatever. Deal with it some other way. Uh, stuff from runtime stats and stuff from Query Store stats. And I think that should just, oh yeah, context settings. Last but not least, pulling up the rare context settings. So, uh, you know, pretty verbose output. It should be enough to get you going in here, uh, and, uh, help you figure out, uh, what might have gone wrong in where, if I’m doing something wrong, if I have something logically wrong in the procedure, there’s all sorts of stuff for you to, uh, help me help you, um, get things to the right place.

That is if you hit any errors, but I don’t know. I’m pretty confident that things are okay in there, which means things are going to break spectacularly, but I am at least fairly confident for now that, uh, whatever. I don’t know. Figure it out. Figure it out eventually. Anyway, uh, that was the last of the code review videos that I had lined up for now. Uh, if there’s anything that, uh, you would like to see, uh, feel free to leave a comment. If there’s anything that you are more interested, interested in learning more about, uh, I don’t know.

You could, I can record another video or answer your questions on GitHub or whatever it is, but, uh, I don’t know. That’s about it for me this time. Uh, everyone go back to enjoying whatever you’re doing. I’m going to hopefully be still on vacation and, uh, I will see you in another video sometime else. I should just go now. I feel unwell. All right. 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.

Checking It Twice

---

Thanks for watching!

Video Summary

In this video, I delve into the implementation of passing lists of IDs to my new store procedure, SP_to_the_underscore_quickie_store. I explain how you can include or ignore specific query plans and query IDs by passing in strings of IDs up to 4000 characters long. While I opted for a more manual approach using XML and dynamic SQL rather than table value parameters, this method allows for greater flexibility and reusability across different scenarios where you might want to include or exclude certain queries based on their IDs.

Full Transcript

Erik Darling back once again with another hopefully spectacular video about my new store procedure, SP to the underscore to the quickie store. And in this one I want to talk a little bit about how I implemented passing in lists of things to the store procedure. Now, I do not give you the ability to pass in the list of things to the store procedure. I do not give you the ability to pass in a list of databases because I often look at, look, querying one query store, one database worth of query store sucks enough. I only give you, pass in 10 databases and sit there for an hour. We pull data back. It sucks. It’s not the way I want you to spend your time. You can do better than that. At least I think you can anyway. There’s no universal queries. I guess the universal queries should be the plain cache, but we all know how unreliable that is. At least it’s not a good way to pass in. At least it’s pretty fast to query. Get some stuff back pretty quick from it for what it’s worth. But what I wanted you to be able to do is pass in a list of plan or query IDs to include or ignore. Now, you’ll notice that all of these parameters are in VARCAR 4000. I figured that was a reasonable limit on the length of a string for you to pass in things to include or ignore. If you truly need to include or ignore, a string of a string of IDs longer than that, let me know. It wouldn’t be too much of a big deal to make those maxes. I guess. Someone will ask. Great. Anyway, so you have these four things that you can use to include or ignore query plans here.

If we scroll down a little bit to where I actually process this stuff, the thing that I use to… Why is this being weird? There we go. There it is. For some reason, SMS doesn’t always keep good track of where things are and should be. A lot of weird skipping around in there. But what I use to split the strings… Now, of course, in the real world, you have many better options for how you get a list of strings into a stored procedure or whatever.

If it were easier, I would have used a table value parameter. I think table value parameters are great. I know that they have some of the downsides of table variables, but I’m still pretty keen on them as opposed to what people usually do, which is pass in the list of strings and then use a string splitter function in the middle of a where clause to split the list out and performance sucks and everyone’s sad. So in order to partially avoid that, what I do is I use a… See, I think I couldn’t even create a helper function to do it. I have to do everything in place here.

So I use this piece of code. I’m going to be honest with you. I did not come up with it. I sort of forget where it came from. I know that I used it in SP human events to pass out list of weight stats. It may have been in one of the blitz procs at some point that I was working on. I don’t know. If you know where this thing came from, let me know and I’ll happily give credit somewhere in the liner notes. I just forget.

But this uses XML and some cross-applying of nodes in order to split a list of IDs out. Now, I am using Dynamic SQL for this because I want to be able to reuse this piece of code regardless of what people choose to include or ignore. Remember, there are four different possibilities for things that people might want to include or ignore.

There’s plan IDs to include, query IDs to include, plan IDs to ignore, query IDs to ignore. I want a reusable piece of code for all that. I don’t want to have to, you know, I don’t want to have a separate piece of whatever nonsense this is doing extra work. Right. So what I’m going to do is when I need to, when people have something that they want to include or ignore, I’m going to use that piece of Dynamic SQL to do it each time.

Right. So you can actually include plan IDs and query IDs and ignore plan IDs and query IDs all at the same time. And I’m going to show you how that works. So if include plan IDs isn’t null. So the main table that I use to drive a lot of the queries here is query store runtime stats.

That has the plan ID, that has a plan ID column. It does not have a query ID column in it. So for plan IDs, it’s actually really easy. All I have to do is split the string that I pass in of include plan IDs.

And then I can tack on my where clause. I’m going to stick that into a temp table. And then I can tack on to my where clause that I just want to use this. Right. I just want to include plan IDs that are in this table.

The same thing with ignore plan IDs. If I want to ignore plan IDs, I just pass the list of plan IDs to ignore. And then we insert into ignore plan IDs. And I say, hey, if you want to ignore plan IDs, you want to find things that don’t exist in that table.

For query IDs, it gets a little bit more complicated. Because with query IDs, they’re not in the query store runtime stats table. So I have to figure out a way to get plan IDs associated with query IDs.

And that’s where things are a little bit weird. So if I have include query IDs, what I do is I still do the exact same thing, where I split that list and put the query IDs into a temp table.

But then I have to do this sort of separate query, where I go and get plan IDs from sys.query store plan that are included in that list. So I go and search that table out to find valid plan IDs for the query IDs that you care about.

And then I do almost the exact same thing, where I tack onto the where clause. So I actually reuse include plan IDs here. So I don’t have a separate, like, weird temp table structure.

I just reuse the include plan IDs or the ignore plan ID temp tables in here. So if there are query IDs that I want to ignore, then I get their matching plan IDs. I put those into the include plan IDs table, and we use that to drive the where clause.

And then if there are query IDs that I want to ignore, I do the same thing. I find those and stick those into a temp table via the plan ID. And I just find where, say, where exists blah, blah, blah, find these.

And then I go and tack that onto the where clause down here. And that’s all well and good. Cool and great.

So that, I think that’s it as far as explaining things to you. But here’s what it looks like when you actually run and look for some query IDs. So we’re going to run this, and we’re going to say, go back to the beginning of the year and look for these query IDs.

And I’m going to have debug turned on, even though that’s jumping ahead a little bit. We’re going to talk more about the debugging stuff in the next video. But what I wanted to show you here was sort of a little bit of what happens, where the include plan IDs table gets populated based on the query IDs that get passed in.

And then up here, we can see the query IDs that I had listed to include show up here. We can see some of those query IDs had multiple execution plans. That’s fun, interesting, exciting stuff.

Looking at the query text, you might figure out why. Maybe they’re very close by, slightly different in some ways. I don’t know. We’ll have to do some more digging to figure that part out. But this is how you can use SP Quickie Store to pass in a list of IDs.

And then, you know, if like, let’s say, you know, you get very interested in one of these things, you can just take this copy. You can just copy and paste this out.

And you could, you know, just rerun this to say include plan IDs. And you could put that list in there. And you could run this.

And you would get just those plan IDs back, right? So that’s how you can do that. And that’s the entire reason why you are able to pass list sim is because I was reviewing output and I was writing documentation.

And I looked at the plan IDs and I said, ah, crap. It would be really nice if I could just copy and paste that into something and run it and get everything back. Because the original implementation was that you could look for a single query ID or a single plan ID.

And I felt that that felt a bit, that felt a bit flat as I was writing the documentation. There was no pizzazz, that there was no zing with that. And I didn’t like it.

Everything must have pizzazz and zing with me. In case you couldn’t tell. I live in a world of pizzazz and zing. Wonderful. Anyway.

That’s how I implemented passing in strings to sp underscore quickie store. I hope you enjoyed yourselves and liked it and all that usual stuff. I will see you in the next video, which is going to be about, in case the obvious foreshadowing earlier did not cool you off, going to be about how I implemented debugging in this thing.

Because there are some, I think, I think some neat things about it that if you are the type of person who writes store procedures that either do this sort of server analysis or even, you know, for your own store procedures that you want to, you know, test things in, might be useful.

So, yeah. And we’re going to talk about debugging stuff for both dynamic SQL and table contents and stuff like that. So that’s what we’ll do in the next video. For now, I’m going to go, I don’t know.

I don’t know. I’m just going to wait patiently for this thing to render and upload to YouTube and then more stuff. I don’t know.

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.

Four Hours

---

Thanks for watching!

Video Summary

In this video, I delve into setting up performance troubleshooting mechanisms within a stored procedure named `SP_quickie_store`. I introduce a bit parameter called `troubleshoot_performance` that defaults to zero to avoid unnecessary overhead. The setup includes logging queries and their execution times in a simple table with computed columns for timing details. By using dynamic SQL, the process can be easily extended if more logging is needed without altering existing code. Additionally, I demonstrate capturing query plans specifically from parts of the stored procedure where performance issues might arise, using `STATISTICS XML` to focus on relevant queries rather than overwhelming data. This approach provides a practical solution for diagnosing and improving SQL Server performance, especially when dealing with complex or frequently executed procedures.

Full Transcript

Erik Darling here with Erik Darling Data. Having a grand old time. There is some sort of thunderstorm going on outside. So hopefully if you get sick of listening to me you can just tune out and listen to the lovely white noise machine storm that is currently brewing on the East Coast. In this video we want to look at how I set up some performance troubleshooting things, what to call them, captures, trap, I don’t know, whatever, in SP underscore, underscore, underscore, quickie store. So the first thing that you might notice is a parameter that you can pass into SP quickie store called troubleshoot performance and that it is a bit which defaults to zero because you don’t want it turned on by default I don’t think. Now, one of the first things that I do is declare a couple parameters here. Oh wait actually there’s something else that I do up here that I think I might have just skipped past a little bit. Is I have a logging table for things that I want to log as queries run to capture information about them. It’s a pretty simple table. It’s an identity. The current table which we saw in the last video is where I used to figure out where I am in the world. Start time and end time is what I do.

Start time and end time and then a computed column that tells me the number of milliseconds between start time and end time and I do format that number to stick some commas in it just in case things get on the long side and we want to figure out the exact scale and whatnot of things. And then the next thing I do is I declare a couple variables to hold dynamic SQL and it’s not terribly dynamic SQL and this section really is only here to shorten the number of, shorten the code a little bit. You know, like the, I have, if you go down to where the code actually lives, I have right down here and insert that puts data into a, into that temp table, the troubleshoot performance temp table and that passes in the current table thing and then I have an update that updates the end time where the current table equals a current table. And so this is what allows me to figure out how long a statement ran for. But that’s not all that I do. If we scroll down a little bit further, what I do and all of these blocks and I just go back a little bit. So the reason that I have this part set up in dynamic SQL is because I, in just in case I want to add more columns to the table and more stuff that I want to log, I only have to, I have to make minimal changes to do that. I don’t have to find every time I would do that insert and change it.

I can just change the one insert and the one update and then I can go get, you know, I can plop that in there and it executes the new SQL and all the places that I care about. So the next play, next thing that it does is if troubleshoot performance is set to one, then I will fire off that dynamic SQL block with the current table that I’m working on, which again, that gets set right here to tell me which block of code I’m in. And then I set statistics XML on. So this is a command that I can use to capture query plans that I care about.

Like, so if I care, like, you know, like from troubleshooting performance, you know, I don’t want to just set statistics XML on for the whole procedure because it’s going to capture a bunch of nonsense. It’s going to capture a bazillion things. I don’t need that. The only things that I care about, the statements that I know that might hit issues, are ones that go and touch the query store views because those are the ones that are subject to the awful design and indexing and implementation of query storage views.

Sorry, it’s just the truth. They suck. That’s why I wrote this because every time I had to, every time I was working with a client, I’d be like, how do I do that in query store again? It would just be like this process. It’s not something that you shouldn’t have to deal with stuff like this. This stuff shouldn’t be harder than it already is.

Like, it’s hard enough dealing with SQL Server performance, a million different things that go wrong. Finding out what went wrong shouldn’t be as hard as it is. So, this will set statistics XML on, and then after the code runs, right, we see the SP execute C will define, see if the query store exists.

Then if troubleshoot, again, troubleshoot performance one being set to on, the first thing I do is turn query plans off. Now, the reason I do this in here is because I don’t care about the query plan for inserting into the temp table. All I care about is the query plan for inserting into the query store exists thing, right?

And then, so this is troubleshoot performance set to one, then I set statistics XML back off, and then I run the update to update the current time and place and all the other good, fun things that are in there. Now, I can’t remember if I had recreated this since I added that, but what I’ll show you what it looks like real quick.

So, if you hit troubleshoot performance, then you will get back. This doesn’t perform terribly on my computer because my computer doesn’t have a lot of query store data in it, but other people are going to have a tougher time.

So, first thing you get back is this set of things that will show you the query plans for the queries that executed in the parts of the store procedure that we might care about performance in. There are kind of a lot of them, and it’s a little annoying that since we’re executing dynamic SQL to do an insert, for a lot of these things, what you’re going to see is this insert query from a parameter scan, and I can’t really do a lot about that.

You know, like we see this is the query that ran, that we pulled data from, but then like the insert that we did to pull the results of it, that is a separate execution plan. So, it’s a little annoying.

It is a little sort of, you know, a little bit more data than I’d want to return from the query plans, but there’s not a lot I can do about that. Then once you get past the end of the query plans, something that is a little bit more helpful that will at least help you figure out which plans you should focus on is the results of that table that show how long each one of the steps ran for.

Now, of course, my query store is not in terrible shape, so, you know, things turn out okay here for me. If I can just make this a little bit bigger so you can see what’s going on here. I have all of the steps that my procedure hit when it ran.

Again, this might look different for you if you use different parameters, search for different things, exclude different things. But this is what it looks like. We see the start time and end time.

We see how long each one took. Then we can kind of say, okay, well, you know, this is the sixth one down. It, you know, took 20 milliseconds. Oh, boy, you better call the police. And then we can, you know, kind of get a feel for where in this we should start looking. One, two, three, four, five, six.

You know, it’s not going to be exact because we’re going to have to deal with the separate insert plans, but we can at least start to figure out where things might have taken 20 milliseconds and start focusing on that. So, anyway, that was kind of a neat thing that I thought, neat extra for this.

I do anticipate people having weird problems with query store performance searching some of this stuff, you know, especially if your query store is very active or very large, then you’re most likely going to hit some oddities trying to query it.

It’s been my experience dealing with query store data, even using the GUI. Like, this isn’t supposed to be a replacement for the GUI. This is an alternative to the GUI because even when I use the GUI to access some of the reports, whether it’s regressed queries, like, you know, top resource consumers, stuff like that, it’s very, very, very slow sometimes.

So, anyway, that’s my story there. That’s how I implemented performance troubleshooting into this. And, you know, if you run into performance issues, you get the query plans back, you know, maybe you see something that I messed up, something I could do better, you know, feel free to let me know on GitHub.

That’s where I do all my, you know, troubleshooting, stuff like that. So, feel free to let me know there. Be happy to take any feedback from the general public. Otherwise, it’s just me kind of feeling lonely working on this stuff.

Anyway, that’s my video about implementing performance troubleshooting. In the next one, we will look at how I implemented passing lists of strings into the stored procedure in a safe way. So, that’s what we’ll cover next.

And the final video in this series will be how I implemented debugging. So, crazy, fun, sexy times for you and me ahead. Choo-choo.

Yeah. That carpet’s on fire. All right. I’m going to go now. Thank you for watching. I hope you enjoyed yourselves and learned things. And I will see you over in the next video.

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.

Red Text

---

Thanks for watching!

Video Summary

In this video, I share how I implemented error handling in the SP_QuickieStore stored procedure, which is part of a series aimed at keeping things light and fun while on vacation. I dive into using `BEGIN TRY` blocks to handle errors gracefully, explaining my approach with a live demo that includes dynamic SQL for checking query store existence. I also highlight the importance of the `CURRENT_TABLE` variable, which helps in pinpointing exactly where issues occur within the procedure. The video concludes by showcasing how error messages are raised and displayed, even when dealing with dynamic SQL blocks that exceed the 2,000 character limit for `RAISERROR`. Stay tuned for the next installment, where I explore performance troubleshooting techniques using query store data, which promises to be both interesting and practical.

Full Transcript

Erik Darling here with Erik Darling Data and have another lazy bones video while I am on vacation slash location with BeerGut Magazine to talk about how I implemented error handling in SP underscore QuickieStore. This will be the second video in the series that I am going to hopefully have five of because I don’t feel like writing anything. I don’t want anything that anyone will actually comment on. So, what will end up happening? Just kidding. No one ever comments. So, no error handling would be complete or even good without a good old fashioned begin try. So this is where our story begins. It’s begin try. And I’ve done a couple few things along the way to make things a little bit easier on me. One of the things that I did was I have a little declared variable here. Declared variable here. Declared variable. Called current table. And I use this to store what action I’m currently doing in the store procedure. It makes things a little bit easier. So, if someone decides to debug this, they hit an issue, then it makes it a little bit easier for them to find an issue. And I’ll show you kind of exactly what I mean by that. I’ll do a live demo. This whole thing. And then, what I use current table for is, is stuff like this. So, whenever I start a dynamic SQL block, then I like to figure out, okay, what am I doing? I’m checking for query store existence. And then the dynamic SQL that I generate is down here.

Right? So, I know what I’m doing. And I know what the SQL is doing. I know what the intent of the SQL is. Then I can figure out if that’s, like, if this is, like, what’s going on when I hit the error handling part of things. So, let’s go all the way down to the end of the procedure. And let’s look at what happens here. Now, of course, no begin try would be complete without end try. And no end try would be complete without a begin and end catch. Of course, SQL Server would throw an error if we didn’t do anything useful.

So, I did. I tried to do something useful. Hmm. I know. First time for everything. So, what happens in the begin catch block? Well, when I was first doing this, and I admit I am not the world’s foremost practitioner in error handling. I’ve tried and had some successes and failures with it in the past.

But what I initially started doing was, oh, well, I’m going to capture, you know, the error message, error line, proc thing. You know, I read around with Erlen. Every time I have to do error handling, I go read the Erlen Summerskog articles, articles, plural, about error handling and end up, like, re-remembering learning something new every single time.

So, what I do in the catch block is, like what was just talking about, how I use that current table thing to locate where in the procedure I am. One of the first things I do is I throw an error and I use raise error and I use a string token to insert whatever text comes from that current table variable first, right? The next thing I do is I raise the offending query in raise error.

Now, I realize that there are times when this is going to get cut off, right? Like, raise error has like a 2,000 or something character limit. I think at least a couple of few of the dynamic SQL blocks in there are longer than that.

So, I realize that some of them are going to cut off. The majority of them will fit in here. If you need to see the whole thing, well, then you need to, you know, use the debug parameter, and we’ll talk about that in a later video. But then the next thing I found, something that kind of surprised me a little bit, or I don’t know if it’s, I don’t know if this is, this is how ridiculous it is.

I don’t even know if this is the first time that it surprised me. But capturing all like the error number, error line, error severity, all those like functions that get built in to help you figure out where errors are happening, didn’t throw errors reliably when dynamic SQL was involved, right?

And that really messed me up. I was like, wait a minute, how am I going to figure this out? And it turns out throw does that. The only thing you need to know about throw is that whatever happens before a throw needs to be semicoloned at the beginning.

So, let’s go way up here. And since this is guaranteed to happen really, really early on in the procedure, let’s just go and make this part fail, right? Because, you know, why not, right?

And let’s go run spquickiestore and see what happens. And this is what it looks like when the query fails for some reason, right? We get the error message up here. We see that the error, we got an error while checking for query store existence.

And that’s exactly where I removed the Y from this view so that we couldn’t find it. And then we see the offending query, right? Which is this thing, reset transaction isolation or read uncommitted.

And then we try to look for a thing that doesn’t exist here. So, that breaks completely. And then the throw, which I do at the very end, will tell us that we have an invalid object name, right?

And we can see the sysstut query store options is obviously not a thing. And we cannot query that. And so, that is how the error handling works in here.

And, you know, I just don’t have a whole lot more to say about it than that. And damn it, I’m on vacation. So, I’m going to wrap this one up.

And in the next one, we’re going to talk about something that I think is pretty neat. I don’t know how many people are actually going to use it, but I thought it was kind of a cool thing. I think I’m going to use it in other store procedures, I think. But it’s how I implemented some performance troubleshooting stuff in this proc because I always run into issues trying to get fast data from query stores.

In the next video, we’ll talk about that. Thank you for watching. Good and bye.

Bye.

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.