How I Set Up Debugging In SQL Server Stored Procedures

How I Set Up Debugging In SQL Server Stored Procedures

---

Video Summary

In this video, I delve into the intricacies of setting up debugging for SQL Server stored procedures, sharing insights from my extensive experience writing complex and dynamic code. As a developer who frequently deals with intricate logic and dynamic SQL in my store procedures—like SP Quickie Store, where we dynamically query various databases based on their configurations—I understand the importance of robust debugging tools. I walk through how to include essential parameters for debugging logic and performance, as well as conditional execution of dynamic SQL. By demonstrating these techniques, I aim to equip you with best practices that can help maintain and troubleshoot your own stored procedures, ensuring they are reliable and efficient, even when faced with complex scenarios or unexpected conditions.

Full Transcript

Erik Darling here with Darling Data, recording the best video you’re ever going to watch in your life. At least, probably the best video about how to set up store procedures for debugging in SQL Server. And this is a particular joy of mine because, as you know, I write a lot of store procedures and the store procedures that I write in SQL Server. So, the site tend to involve a lot of pretty complicated dynamic SQL, especially around figuring out, you know, like, where the, like, contextually where the, where the, the code is running, you know, whether, like, which database it’s in, whether we’re in, you know, managed instance or regular SQL. There’s a lot of, tends to be a lot of looping involved to get things from different places. And a good example is, you know, you know, SP Quickie Store, where we, you know, there, you have the option to get all of the databases that have query store enabled on them. So, we have to figure out which database we’re in and all that other stuff. And, you know, which version and addition of SQL Server, we got standard, we got enterprise, do certain columns exist, you know, like, Microsoft will introduce columns, and even DMVs and cumulative updates, we have to figure out if certain columns are there and either select or, or, like, ignore them.

If they’re not there. So, there’s, there’s a lot of stuff that has to be done in these. And, I find it very useful to have appropriate debugging in them. Now, you may also find it useful to have appropriate debugging in store procedures that you write, maybe they do somewhat similar things to what mine do, at least, you know, conceptually, no one can ever, never touch the majesty that are, that is my store procedure collection. And, or you might just have rather complicated code in your normal store procedures where it makes sense or where it would make sense to have some debugging options available either. So, you know, so you can troubleshoot things should they ever go wrong or if, heaven forbid, you, you get hit by the lottery, and you have to, you know, go on to greener pastures, probably a golf course, and you leave your code behind that someone else may have to troubleshoot or figure out what’s going wrong someday.

So, this store procedure that I’m going to show you here has some pretty good examples of what I include in the debugging process for all of my stored procedures. It may not be the exact same thing each and every time. My debugging process has evolved over the years, even since I started writing my very own stored procedures you can find over at code.ericdarlingdata.com. That’s the short, that’s the short, short, short example of how you get to my GitHub repo.

But I usually start with three parameters for debugging. One is to debug logic, one is to debug performance, and the other is deciding whether or not I am going to execute certain dynamic SQL or not, which is important because depending on what your store procedure is doing, you could end up making changes to a server or attempting to do something that you know is going to fail. There could be side effects of the query that you may not want. So, if you’re writing the type of stored procedure that generates a command to run like a backup database or run check DB or something, or if you are writing stored procedure that might insert, update, or delete data, or change a setting in SQL Server, then these are all things that you may not want to execute. You just want to check the syntax of them. You just want to make sure that everything is cool there.

Granted, you could include this in normal queries, right? Like a non-dynamic SQL, where, you know, say something like, oh, select star from table where execute SQL equals one. But you probably don’t want to do that in production level code. You probably only want to do that in, like, you know, stuff like I’m going to show here, like sort of the analysis queries and things like that. Because that also just adds, like, another weird potential performance oddity to your production code that you don’t want.

So, there is all that. So, starting all the, sort of most of my procedures, set no count on. Generally, you only want to see row counts from things happening in intermediate steps in a stored procedure.

When you’re debugging, you don’t want that potentially going back to a client or something somewhere. So, you don’t really, you generally want to set no count on for everything. Set exact abort on, you know, 99% of the time you want this.

99% of the time, if you have a stored procedure fail at some point, you want everything that’s happened before that point to roll back. And you want to stop the stored procedure from executing right then and there. There are other times when you may not want that to happen.

Like, there are certain things where you’re just like, you know, if one query fails, no problem. Carry on. Just keep going. Make progress. Finish things up. Finish things strong. So, most of the time, you’re going to want that on, especially for production level code.

But, you know, for the type of stuff I write, it doesn’t matter all that much. I just try to set a good example for all you lovely folks out there who consume my scripts and my video training and blog posts and all the other countless ways that I devote myself to you. So, like most stored procedures, we’re going to declare some variables in here.

You know, a lot of the stored procedures that I write, I have to declare a whole bunch of variables to hold values because I don’t want to repeatedly calculate things. Or sometimes I just want to be able to easily figure out what those values are. We’ll talk about that more later. But in this one, I’m just going to have some big value here to simulate a long dynamic SQL string.

We’re going to have some other stuff for a loop. We’re going to have some other things going on that are just lovely, good fun. And I guess the last one in the list is kind of the most important one because that’s the one I’m using as an example of why you want to do this sort of debugging.

Because at the very end of the stored procedure, I’m going to have a couple of select lists that are going to sort of select all of the parameter values that were passed into the stored procedure. Because sometimes you may manipulate parameter values when they get passed in and you want to see what the final result is. And other times, and this is going to be a second select list that does the same thing with all the local variables because local variables are often set dynamically in a stored procedure depending again, where you’re running, what database you’re in, version edition of SQL Server, stuff like that.

So I’m going to show you an example of that. The very first thing that happens in the stored procedure after that stuff gets declared is we’re going to set the addition variable right there to the server property of my local instance called addition. And this is going to be way different depending on where you execute it.

You know, it could be, you know, developer edition, standard edition, enterprise edition, managed instance, Azure SQL DB, Synapse, Edge, all those other crazy things that Microsoft keeps adding in hopes that you will spend money on their awful cloud products. Now, if you want to know exactly where you are in the stored procedure, one step that I like to run, this is especially useful in loops, but it can be useful if you’re just trying to figure out at what point your SQL Server hits some code.

Right? Like, especially if you have like if logic, branching logic in there, you want to say, okay, well, you know, just to use an example from the Stack Overflow database, like someone passed in the parameter post type ID equals one. So I’m going to hit this part of the code that goes and looks for questions in the post table or someone put in post type ID equals two.

I want to go and search for queries that look for answers in the post table. So if you have a sort of if branching logic in there as part of the debugging process, I like to have, especially if that like that, those code that those if branching paths are explored based on, you know, like, like local variables that you set dynamically based on some condition in the server. Or, you know, like, what time of day is it?

Is it Wednesday? You might do all sorts of different things in there. So I always like to include steps that tell me which queries about to start running. Now, sometimes it is important to know how many rows ended up resulting from some processes, particularly useful for putting data into temporary objects, temp tables, table variables and such, because you want to you want to know how many rows end up in there.

Because if you’re like running through your stored procedures like why does this return any results? You’re like, oh, zero rows went into that temp table. What did I do wrong?

What happened? What happened? Then you want to know earlier on for your own debugging purposes. No. Raise error.

You know, I have some I have some grifes and beefs and stuff with raise error. But if you wanted to simulate what you can do with raise error with print, the alternative is pretty ugly. There are the sort of normal set of substitution, wildcardy things in there with the percent signs in them.

You know, for numbers, you can use I, you can use U, you can use D. But for big ints, you need to use percent I64D. And since row count big can potentially return a big int, then we want to make sure we set it to a compatible data type.

So the row count parameter up here, row count big parameter up here is set to a bigger integer. And then in the debug logic, rather than just say print row count big, you know, I like to add in all this to let me know there were this many rows in sys.databases. Granted, if I ever had a big int number of databases and sys.databases, be honest with you, I’d probably jump out a window in front of a bus on fire.

There were like holding a bucket of knives like there would be there would be a lot more going on than just, you know, me. Running a stored procedure that looks at SQL Server. Anyway, another very important step for debugging.

Let’s say that you actually end up with rows in your temp table. You want to know what’s in there because you could even, you could end up with rows in the temp table and then join that off to another table and say, well, I still got no results back. What happened?

And then you want to know, okay, well, what rows ended up in the temp table because they didn’t match anything that were in another table. And so we got nothing back. So you kind of want to know what’s in there.

The thing is, if you run a stored procedure, no rows end up in a temp table and you say select star from temp table, you’re just going to get an empty line back somewhere. You have a bunch of temp tables in your stored procedure.

Well, guess what? It’s going to be kind of hard to track what’s in there. Or even if like, you know, you were to like populate and truncate a temp table in a loop. You’re like, okay, well, what point did I actually get nothing in there?

Like what happened? So again, knowing what happened and when pretty important. So what we’re going to do is in our debug logic branch, we’re going to say, if there are, there is anything in our temp table, then we are going to select data out of that temp table.

But we’re going to put another important thing in here. In the select list, we’re going to have a derived column called table name. And we’re going to set that equal to the name of the temp table that we’re selecting from.

This makes it really easy for you later on. If you have, again, a whole bunch of different temp tables in a stored procedure, makes it really easy to figure out which one of the results you’re actually selecting data out from.

If we don’t have anything in our temp table, then we’re going to select this, just one derived column and say that temp table is empty. So going back to what I was talking about with the execute dynamic SQL or execute SQL thing, let’s say that we had, let’s say that we added that as a parameter here.

And we say, let’s just say where at execute SQL equals one. Let’s say we did that. If we execute this SQL, we would expect to get results back.

Right? Makes sense. But if we had the, if we, if execute SQL was zero from our store procedure, when it gets passed in as a parameter, then this would not execute.

We wouldn’t end up with any rows in here. So you could do that and, you know, maybe for, you know, like, you know, this sort of, these sort of analysis procedures, that would make sense. I wouldn’t do that for production code though, unless it was just for testing purposes.

And I would take it out immediately afterwards because it just doesn’t, it doesn’t, doesn’t, doesn’t sit well with me there. You know, the potential performance issues of, you know, SQL Server, you know, like recompile option recompile.

Did a plan, did, did a plan get sniffed for this where execute SQL is zero? Like what? A lot of things that go wrong. So I wouldn’t do this outside of, outside of, you know, analysis scripts, stuff like that, or just, just you testing.

So the other thing that we can do in here, and another thing that’s very, very important when you’re writing dynamic SQL is being able to debug dynamic SQL. Because there is nothing worse than getting back some nebulous error message like incorrect, incorrect syntax near apostrophe.

Okay. Or incorrect syntax near from. Or incorrect syntax near some column name. And you’re just like, oh, what was it?

What, what, what was the, what was executing at the time? Could you let me know? With regular queries, it’s very, it’s a lot easier to figure out at what point you made a syntax error. Sometimes IntelliSense will even give you some happy little red squiggles to let you know.

With dynamic SQL, you are on your own. Float your own boat on that one. Either write perfect SQL the first time, or add debugging.

So what I’m going to do in here is actually something that I don’t do very often in my store procedures. Because I generally know either the normal length of what the dynamic SQL string is going to be. Or I know the possible max length of what a block of dynamic SQL will be.

I don’t tend to write things that would tack on like, you know, thousands and thousands of characters depending on something. It’s not like, well, we started off with a query that’s, you know, 10 lines long, but depending on other stuff, it could be 80,000 lines long. I don’t tend to write stuff like that.

Most of the time, I have a pretty good sense of exactly how long the, like, max length of a string would be for dynamic SQL. So I don’t typically do this. Now, there is a fella out there who, if you say his name, you summon some ancient demon.

But he wrote a store procedure called helper long print. I thought I had the link to that in there. But it appears to be an oversight on my part.

I’ll add it before I, I’ll add it after I finish this video. I promise. But if you can, you can totally use that to print dynamic SQL. The problem I have with it is that I can’t like distribute it as part of my stuff.

I don’t want to like rewrite it and like say, oh, look at this open source thing I wrote. I also don’t want to add another dependency to any of my store procedures. If someone like, like, I don’t want to have a thing where like, you know, someone’s like, oh, I tried to debug your store procedure, but I couldn’t because this other store procedure wasn’t there.

It’s like, it’s annoying. Right. So usually I’ll just write sort of a like generic loop to go through and print store procedure, like print dynamic SQL. I’ll do this if I’m working with code that’s foreign to me.

Like, like I said, with my own code, I have a pretty good sense of how long things will be. But if I’m working with someone else’s code, then I’ll usually write a loop like this. It’ll tell me how long the dynamic SQL string is.

Well, actually here. And then I’ll do a little loop here that just prints a sub string based on the like position of stuff in there. Now, most of the time I will tell you that, you know, raise error is a good idea.

But print, but raise error has a weird limit of like 2047 characters for the message that it outputs. And that just makes dynamic SQL printing kind of weird. I’m doing this a little bit differently here than I normally like.

Like when I write dynamic SQL, I write nicely formatted queries because I care about like being able to read the printed out dynamic SQL that I write. This is just a block of A’s and B’s. So it doesn’t make that much different here.

And the reason I’m using like weird block sizes and for things is because I just want to be able to like show you that how the squares kind of line up. If you need to do this multiple times, you have multiple dynamic strings that you might need to print out. Just remember to set the block variable back to zero when you start so that you don’t start trying to print some other dynamic SQL string either out of range or something or from like a weird starting point.

And but this is where I would more typically want to use the execute SQL or not thing. And I don’t want this to be dependent on if I’m debugging or not. Like I want to be able to run stuff and I want to be able to I want to be able to either execute a query or not, regardless of whether or not I’m debugging other portions of the store procedure.

So if execute SQL equals one, then we’ll go and execute that dynamic SQL. Otherwise, we will not execute it. Isn’t that nice?

So another very common thing that you really want to debug because I have been driven up trees, walls, volcanoes, curtains, writing loops and trying to figure out either why the loops were going on forever or failing or not making progress or something ridiculous. So I tend to write verbose debug logic into my loops. I don’t I’m not going to show you the most verbose example here because it’s probably overkill for most people.

You know, I’ll add timings and stuff like that in mind if I really want to. But in this case, I really just want to know some of the basic stuff. So what I’m going to do is set some of the local variables I declared up there.

This is going to loop over that database table that I talked about, that I should that I populated before. I want to know what what values I’m starting with, like going into the loop. I want to know where things are.

And then within the loop, I want to know which step I’m hitting. And then I want to know what the current values are for that step. I want to know when I move on in the loop to do something next.

And I want to know, like again, what step I’m at in the loop. So, you know, either, you know, fetching or incrementing or where I want to know where things are getting jammed up. Like if I were running this loop and like I just saw like for like, like really rapid, like, you know, just print out messages of fetching next or incrementing loop.

I would know I got stuck and screwed up somewhere. Then I want to know what I finished the loop with. This may seem trivial and unimportant to you, but it’s very important if you care about making sure that you did everything in the loop that you were supposed to do.

Like, like what if, you know, you saw that, you know, you’re low, like, you know, low ID and probably not going to screw that up. Maybe, maybe not. But if maybe, you know, you didn’t write your loop correctly.

So, like, you know, you only got to database like nine of 10. And, you know, you like you want to make sure, oh, I’m going to fix my loop. So I get to database 10 of 10 and then I stop.

Right. So it’s important to know what you start the loop with, what you ended the loop with. Just like, like that equally as important to me, I think, is knowing what was happening. In the loop, like knowing what step I was hitting, all that, all that other good stuff.

The next part is a little bit different. This is performance debugging. So in a lot of my store procedures, especially ones that are hitting, you know, like the either extended events, XML, you know, query, query XML, you know, hitting anything that’s hitting like DMVs, any, any part of the store procedure where, you know, I think that we, I’m, you know, I have a pretty good idea that there might be a performance issue that I might want to look at at some point.

I want to be able to debug performance. But this is much more common for me to do. And when I’m working with client stuff, because like after a few times profiling the store procedure, like most common way for me to profile a store procedure to use SP human events and to look at the query plans and the queries that are coming out of that store procedure from extended events. And then I’m going to go to the next part of the query plans because, you know, I don’t want to hit F5 with query plans enabled for everything because there’s no way to filter which query plans you see out of here.

A lot of times there’s going to be a lot of, a lot of tiny little queries you don’t care about happening in here that, you know, like, why do I want to see me setting variable values or why do I want to see each iteration of a loops query plan? Like, like, like stuff that like, you know, just crashes SSMS. So usually, like after I have a pretty good sense of which queries in a store procedure are prone to running slow or prone to parameter sniffing or something else.

This is much, much more common for me to do, you know, like working with client stuff to like, you know, get things tuned is I’ll add a little, I’ll add a debug performance parameter, at least for my own use. And I will set statistics XML on if I’m, if I, if I have that enabled so that I can get a query plan back for the query that executed. Right.

And then what I’ll do at the end is a couple things. One set statistics XML off. So in this block, you want to set statistics XML on to be the last thing you do because you don’t want a query plan for any of the junk that you know, you, any other junk you might do in here. And then you don’t want to get query plans after the fact for anything you do down here.

So it should be the last thing in debug at the beginning and the first thing in debug at the end. I’ll also have some information about the query. You know, like I’ll have a description of what query ran, the end time, like when it finished, you know, you get start time up here, query MS equals zero here, because obviously it hasn’t run yet.

Nothing’s going on, but we want to know how many milliseconds it ran for down at the end. So I’ll do a quick date diff on, but in milliseconds between start time and the current time. Now the format function, really cool.

I want to say that SQL Server 2012. So if you haven’t started using it, then I don’t know what’s wrong with you. Actually might be 2016.

I can’t remember. I can’t remember all these things anymore. It’s hard to keep all of these things straight in your head, but newer versions of SQL Server, newer, 2016 is seven years ago now. I like to use this to stick commas and numbers, especially milliseconds.

It makes it very easy to figure out exactly what scale you’re working with. If you stick this as the second parameter in format, you will get nicely formatted common numbers in there. You know, because we’re debugging, if I’m going to do the same thing that I did with the database table to say, if there’s data in that temp table, show it to me.

If not, tell me it’s empty. And one thing sort of that I like to bring up for these is error handling. Now, this isn’t specifically about error handling.

I don’t want to get into an error handling conversation or debate. My error handling skills are moderate. If you need to know more about error handling, go to this link here.

A fellow named Erlen Summerskog has written biblical chapter and verse about error handling. My only thing is that you want to be very careful when you’re writing error handling to make sure that you, like if you’re writing like begin try and try begin catch and catch, that you actually do something in the catch block.

Otherwise, your store procedure may just silently swallow an error and stop running and you’ll have no idea why. So something I’ve run into a few times where there’s like, you know, oh, we just didn’t want anything to happen. But then there was like it was hard to debug an issue with the store procedure because the errors were being swallowed.

Now, this is what I was talking about at the very beginning. Right. Finally, at long last, here we are getting to the getting to the end here. One thing that I’ll do is if again, for debugging, I want to know what parameter values I had.

Right. So this will tell me what got passed in. And it’ll also tell me is again very useful if you do any manipulation of parameter values that got passed in. Like, oh, if you know, like say someone passed in a negative number and you have a check to say, was that number negative?

If so, set it positive. Something like that. So you know that, you know, that worked and that happened. And then the variable values, you know, depending on how dynamically they’re set, you know, like a lot of the ones that I did up there, they’re just, you know, set within the procedure.

But, you know, like if you’re again, you’re setting variables based on like environmental stuff, the server you’re querying, the database you’re in, things like that. It’s really helpful to know what those values were in the procedure. All right. So this is everything.

This is all the stuff in there beginning to end. And if I execute this, what did I do? Did I record a 30 minute video and then just highlight something by accident and then not highlight, not unhighlight it? So I got a syntax error. Yes, I did.

It’s just so typically me. That was supposed to be Morrissey. No, didn’t work. Never mind.

Anyway, since we are debugging, here’s the stuff we get back. D was empty. Mm hmm. Oh, what happened there? It’s not good.

Why was D empty? I said, oh, execute SQL was zero. That’s why.

So that’s one of the things you have to be careful of. Right. So if we had execute SQL equals one, we would have gotten data in that table. We’re also going to get a bunch of query errors in here because of some reason.

Too long. Maximum length is 128. So let’s go back a little bit. Again, this is the kind of fun stuff that I do when I’m messing around with different ways to debug things.

And this is one of the reasons why I know a lot is because I screw up a lot. So let’s go back up to where I populated that temp table and let’s get rid of this because like I said before, this is a mistake. A big mistake.

You shouldn’t do it. Let’s go back down here and let’s say execute SQL equals zero and we’ll run this whole thing again. And now we’re back to normal.

And the only way to learn is to screw up. Sometimes you do it 29 minutes in. So these are the contents of the D pound sign D table, the database IDs that I put in there. In this section, we can see the query plan for that stupid query that I stuck in there that just hits the ring buffer extended event.

We see how long it ran for. And then we see the results of that. All right.

So because we’re checking to see what the target results are. This here is where I swallow that divide by zero error in the code. So the example that I gave up here is just, you know, select X equals one zero.

If you’re not raising errors for this sort of thing or if you just have your silently swallowing errors, this is the kind of thing that’ll happen. You’ll just get back this blank line instead of getting the proper divide by zero error. And then down at the end, I have my parameter values, right?

So you can see exactly what things got passed in as. And then I have the variable values. You know what? Select is actually kind of appropriate at this point. And then we can see how long that dynamic SQL was.

We can see the block and block size stuff. The row count, low ID, high ID, total, current, loop, the addition, right? So again, this is the kind of thing that’s much more useful to see at the end of a store procedure. So you can know what certain values got set to.

Like if you’re making decisions based on like what columns, DMVs you hit based on like standard enterprise management and SQL DB, things like that. It’s very good to know what this got set to in case you need to change your branching logic to deal with some problem. Now over in the messages tab, we get some of the, you know, feedback messages that I put in the raise error, right?

Selecting from sys.databases, there were seven total rows. There were seven rows in sys.databases. The total length of the dynamic SQL was 8,011 characters.

And there’s a lot of screaming in there, at least until we get to the bees. There’s a good feedback for the cursor loop, right? Setting cursor loop.

The thing that we got in there, right? Starting loop with one is the low ID, seven is the high ID, seven total entries. And then you just, as the loop is making progress, we see what we’re currently doing, all that other good stuff, right?

Anyway, so like this, not quite as interesting because the database IDs are sequential. But if you were in a situation where you were iterating over IDs that were non sequential, it would be good to know that like, you know, ID 357 is like, you know, 357 out of, you know, 400. But, you know, you may not, you might, you might only be on loop 200 because the IDs are non sequential, right?

So good stuff to know in there. And then there’s also, of course, the end stuff. Catching next, incrementing loop, finish loop, all that good stuff.

So, these are the things that I consider. These are the things that I usually add to my store procedures to help me debug logic and performance issues in them. If you’re writing big, long, complicated store procedures, it would behoove you to do the same for your own sanity because you can end up with some very, very confusing circumstances and situations where you have no idea why results were wrong or not there or you hit some error and you’re not sure what, where, where things, where things, you know, sort of.

You sort of bit the dust. And it’s great to have this sort of feedback for yourself. I think that it’s a good idea to set up your store procedures with debugging in mind, especially as they get longer and more complicated, relatively simple store procedures.

You probably don’t need a whole lot in there, if anything, especially if there’s no intermediate objects, variables, temporary objects, things like that. But for store procedures that do loops or like loop over stuff or generate dynamic SQL, you always want to have some debugging potential in there because you never know. Anyway, thank you for watching.

I hope you learned something. I hope you enjoyed yourselves. I hope people who made it to the end enjoyed watching me screw up a little bit. A bit of a deviation from my standard Wallace video performances.

But anyway, this is my first video back from vacation, so you’ll have to cut me some slack. Tanned, rested and ready does not describe me. More like exhausted, hung over and basically working off sheer willpower at this point.

Anyway, if you liked the video, go ahead and give it a thumbs up. If you didn’t beat it, if you really liked the video, you could hit the little ding dong bell and subscribe to my channel. I do like new subscribers.

And if you do that, you’ll get a notification when I record my next amazing video. So again, thank you for watching and I’ll see you next time.

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.