sp_QuickieStore Debugging

Posted on by Erik Darling

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.

Related Posts