All About SQL Server Stored Procedures: Debugging Logic and Performance
Video Summary
In this video, I delve into the crucial topic of debugging stored procedures in SQL Server, emphasizing the importance of adding steps that allow for easy identification and resolution of issues. Whether you’re running a procedure directly from Management Studio or through an agent job, understanding where to direct your output is key. I also share practical tips on what aspects of debugging to focus on—logic, performance, and dynamic SQL—and provide examples of how to implement these in your procedures. Additionally, the video includes links for those interested in supporting my work, such as joining a membership program or seeking professional assistance with their SQL Server needs. Whether you’re looking to improve your stored procedure hygiene or need detailed guidance on debugging complex scripts, this content is designed to be both informative and actionable.
Full Transcript
Erik Darling here with Darling Data, and today Bats and I are going to be talking about something that I think is very, very important to the effective building and maintaining of stored procedures in SQL Server, and that is, of course, debugging. The longer and more complicated your stored procedures get, and especially if they involve dynamic SQL of any real consequence, the more important it becomes to add in steps so that people can find issues, errors, and insights along the way. But equally as important is understanding the context that people doing the debugging will be executing the stored procedures in so that you target the output to the correct venue.
You know, if the debugging is going to be taking place in Management Studio, and someone’s just going to hit F5 and run it and go off on their merry way, cool. You can just print and select and raise error all you want. It’ll do just fine. But if you are going to be running this sort of in a headless way via like an agent job or some other scheduled process or like from some like, you know, or even just like an end user doing stuff, you would have to direct that output somewhere else.
You would have to put that in the table or something and, you know, it’s up to you to figure that out and what needs to be included in that output in order for it to be useful to anyone who might come along and debug it later. Typically, I find that debugging should be verbose. You should start more verbose than you think you need and pare down what you don’t need rather than having to keep adding things in and in and in later.
But before we do that, let’s talk about some very important things here. Let’s talk about how you, Wary Traveler, can support my efforts to keep producing this content. I offer memberships to the channel. You don’t have to sign up for one, but I appreciate it if you do.
If you do not feel like signing up for a membership by clicking on the link in the video description right around in here somewhere, you can do other things that help me sort of get some, you know, joy out of life. Liking, commenting, subscribing. And of course, if you would like to ask questions privately that I will answer publicly on my Office Hours episodes, you can go to that link. Well, you can go to the clickable version of that link down below and ask all your questions.
If you need more help with SQL Server than asking a question can possibly provide. Well, there are a lot of folks like that out there in the world and I work with them quite frequently. make their SQL Servers go much, much faster than they are today.
If you need a health check, performance analysis, hands-on query or index tuning or both probably. If you’re having a performance crisis or you want to get your developers trained up to avoid needing all the other stuff. Well, I am a consultant and as always, my rates are reasonable.
If you would like to get some training on SQL Server performance tuning, you can do that from me. For about 150 USD, you can get for the rest of your life, all 24, 25 hours of my performance tuning content. Again, there’s links down below where you can have a wonderful time clicking on things.
If you would like to see me live and in person, I will be attending as an organizer, SQL Saturday, New York City 2025. It’s taking place on May 10th with a performance tuning pre-con by Andreas Walter on May the 9th. And that’ll be, they’ll both be great times.
So, if you’re looking for it, looking for something to do in May, in the merry month of May. You’ve gotten sick of strolling through parks. Maybe, maybe you could show up to the Microsoft offices in Times Square and do a whole lot of learning about a whole lot of data stuff.
But let’s move on. Let’s talk about debugging. Now, this is a script that I have put up on my GitHub page. There will be a link to it down in the video description.
There are links to everything in the video description. But some important stuff to consider when it comes to debugging. There are essentially three things that you might care about debugging in the context of a store procedure, depending on what it does.
Logic, performance, and dynamic SQL. So, you know, logic would be like control flow stuff, dynamic, like printing dynamic SQL. Performance would be like getting query plans from like the queries that matter in the store procedure.
You typically don’t want to turn on query plans for everything and have just like, SSMS crash because you tried to put 5 billion query plans in the messages and the return results. And then sometimes you may want to generate but not execute dynamic SQL so that you can see what it looks like to fix syntax errors.
It’s usually a lot easier to like see it printed out, copy and paste it to like a regular window, and then figure out where something went wrong, where that, you know, incorrect syntax error, like a single apostrophe is or something like that.
A lot of the times that’s going to be the silent, sort of like silent truncations of your strings. So be very careful with that. So, you know, not really related to debugging, just sort of good store procedure hygiene, no count and exact abort.
Depending on what the store procedure is doing, you may not want exact abort on, because that will force the whole thing to stop running and just basically like return, like the whole thing will terminate rather than just a single statement terminating.
So depending on what you’re doing, that might be good or bad. No count you usually do want on though, because like that blah, blah, rows return or rows affected message is annoying. So like stuff that I usually put in there are things for debugging dynamic SQL, things for debugging loops, and, you know, other ways to sort of collect sort of like runtime, like information about like contextual environmental information.
You know, like a lot of the scripts that I write, they are for SQL Server analysis, right? So sometimes I have to do things like collect version and addition information so I know which columns and which DMVs are going to be there. So what I’m allowed to touch and like, you know, different things that I might have access to depending on that.
So, you know, there’s, there are some important debugging things that you might want to get so that when you go and look at, you know, your final results, you understand why there was a problem with something. But, yeah, that gets that.
And then down here, you know, stuff like debug logic, right? So when you want to debug logic, one thing that you should, one thing that you’re probably going to want to do is have some message print that says the thing you’re going into before you go do that thing. Because like if this were dynamic SQL, you could print out the dynamic SQL, right?
That would be fine. You could, you could see what it got to. But if you’re just running a store procedure and you don’t, you like, you want to figure out which step you’re in and like which, what you’re like, what query you’re about to run. And then like, you know, when you’re finished with that query or something, then, you know, just a simple message like this, just informational only.
And again, like if you’re running this in SSMS and you’re fine with this, like reading through the results, because that’s what most of my work is. Like, I’m not like orchestrating things from other places. Like this might be totally fine.
If not, you would want to log this to a table somewhere. If you were doing this all in a big transaction, logging it to a table would be impossible because if you rolled everything, rolled something back, you would like if you’re like, okay. You can put stuff in a regular logging table and like see the steps from along the way.
When you are dealing with things like loops or you want to figure out if results get weird because of either like too many or too few rows happening with something. You would want to like, you know, you could like log the number of rows that came out of like they got that happened in this here, which is sort of like a silent, like set no count off. Right. Because you’re not, it doesn’t get immediately returned to you.
It gets assigned to this, this variable. And then if you’re, if you’re doing the debug logic stuff in here, then you can print, you could print that as a message and say, hey, this is how many rows you got from sys.databases from that query. Right. So you would say in the output, it would be like selecting from sys.databases.
This is how many rows are in there. Cool. And then if you were going to select from that table, this is what I like to do so that I know, like if a result was empty from something. Uh, because I don’t, I don’t always do the row count thing sometimes.
And I’m like, like, especially for the analysis procedures, it’s like, there’s not a lot of point. Uh, but so like what I usually do is towards the end of the procedure, I’ll put in something like this with just like all the temp tables that I use so that I can see which ones were empty. Right. So like if there’s something in the temp table, then we’re going to select the table name with, uh, like the right table name here.
We’re going to select all the results from that. But if there’s nothing in that table, like it would just say like, it would just be like an empty result set with like, you know, whatever is in the D pound sign D temp table. Sometimes you might want to say something like, Hey, that table is empty. Right.
So, you know, exactly where, where along the line things either went wrong or you might have to change stuff. Um, dynamic SQL, like I said before is sort of fickle. Right. So, um, like, it’ll like, it, like it, like it, like it, like it, like it, like it, like it, like it, like it, like it, like it, like it, like it’ll, it’ll truncate for just bizarre reasons. Right. Like, like, even if you have all your, like Unicode and prefixes in there, uh, you just like concatenate this one slightly smaller string onto like a much larger string.
That’s already in a, already in an VAR car max and should just get it blopped right on and like, just maintain the maxness of it. It doesn’t always work. So like you, like sometimes it’s a, like, it’s a very good idea to print out your dynamic SQL and one, one sort of like, I mean, just quick and dirty way of, you know, if like print has a limit to how many characters it can spit out and show you. So if your, if the length of your dynamic SQL block is, you know, like maybe like 8,000, like 4,000, 8,000, 8,000 bytes or something, uh, you might need to have a loop that go, that iterates over your, um, your string, your dynamic SQL string and prints it out in manageable chunks so that you can see the whole thing.
Uh, but, uh, and then other stuff that is neat to do, if you are using loops, uh, you, it, I always find it very, very useful, uh, aside from having debug logic to like, tell me when I’m going into the loop, when I’ve done certain things within a loop, when I finished the loop, uh, to let me know, like, which, like where I am progress wise. Right. And raise error is way better for, than, than print for this because raise error, you can put this no wait command on there.
And that, and like, like print, you’re, you’re stuck waiting for like buffers to fill up before print will like, like it’s forced to print a message with, with raise error, with no wait, you can skip a lot of that. So, you know, like you could, like, you could put this all into one raise error. I like to have it in three.
So it’s kind of like easy to read through, but you know, you are starting the loop with a low ID of this number, the high ID of this number. And like the total entries are there. So like, you know, then I have my cursor and, you know, entering my cursor loop and, uh, you know, some information about like, like where I am in the cursor.
Uh, I always, I always find like the, the fetching next and the incrementing things to be very, very useful because it helps me catch logical bugs where I maybe didn’t do things in the right place. Uh, and then of course, when you’re finished, uh, it’s great to print finished messages here so that, you know, you’re done with the loop and you can move on from there. Um, as far as like performance debugging goes, um, what I’ll usually do is something like this, right?
Where, uh, like I’ll like, like select a little bit of information about what’s happening. And then, uh, I will use set statistics XML on, right? So if we’re debugging performance, I want to get some information here.
I want to turn on query plans for a specific query. I don’t want to have query plans for absolutely everything in the procedure. I want to, when I get query plans for that stuff, then I’m going to get some finish information, some finishing information rather. Uh, we’re going to look at like say that this query finished, this was the end time.
And this was the difference in time there. Uh, we’re going to turn off query plans at the beginning of that so that we don’t like have any duplicate, like, well, like it’s only get a query plan for anything else basically. So there’s all sorts of good stuff you can do to get performance information too, right?
So like that’s often a very critical step is being able to like, when you have a performance problem, like focusing on the, focusing on the queries where, you know, like performance is, uh, either like important or an issue generally so that you don’t have to, you know, sift through 10 billion. Like variable assignment and like tiny little query plans that don’t add up to anything. It just gets annoying and frustrating.
And sometimes query plans get so long, like, like from store procedures that do a lot of stuff that like you try to scroll down to the bottom and there’s just this like big empty space in SSMS. And it’s like, okay, what happened to the query plan? Can’t render it.
Then guess what? You have to open up like the plan is XML and edit out a bunch of stuff and like delete a whole bunch of like the statement stuff and then reopen it. It’s a nightmare.
So don’t, don’t, don’t do that. And then, um, you know, of course, you know, error handling, try catch stuff, all very important when it comes to debugging. Uh, but like one thing that I find invaluable when it comes to debugging things is at the end of the procedure, I like to have a full accounting of what parameter values, like the way the parameter values got passed in.
And especially if I have to, if I end up doing any parameter value manipulation, I want to know what change in there. And I also want to know what variable assignments happened along the way so that I can, I can get some sense of like, you know, like if anything in here is unexpected is to like the flow of logic and the procedure, I want to know what values I ended up with. So there’s all sorts of good stuff you can do with a procedure like this, um, or with debugging, uh, to give you some sense of like, you know, like where you are in the procedure, stuff like that.
Uh, like how you’re making progress, again, printing dynamic SQL, things like that. Uh, a link to this script will be available in the video description because it’s useful for people to see these kinds of examples so that they can incorporate them in their own store procedures when they’re doing things. But, uh, you know, debugging is a very, very important step in, uh, building, uh, maintainable and, you know, like triageable procedures, right?
Because if it’s just a big, long procedure with some crappy comments in it and you start all of a sudden having problems and you don’t, like it’s either erroring out or miserably slow, you’re going to have a hard time figuring a lot of stuff out, right? And, you know, let’s face it, you know, like code is a lot like data. It never tends to shrink, right?
Like you never, never end up working with less data or working with fewer lines of code. Everything just keeps getting bigger and messier and more complicated and harder to use. So you should really, um, put as much debugging as you can into, uh, into things from the beginning.
So you’re not stuck or you, a, you’re not like caught flat footed when you start having a problem. B, it takes a long time to sort of like work all the debugging stuff in and not introduce more errors with the debugging stuff. So, uh, yeah.
Anyway, I hope you enjoyed yourselves. I hope you learned something. Uh, and, uh, I will see you in the next video in the series about store procedures where, um, I forget what, I forget what the topic is, but I, I, I promise it’s, it’ll be equally as exhilarating. So 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. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.