SQL Server Reference Guide

Staring in SQL Server 2008, Microsoft introduced the Transact-SQL “Debugger” feature. Technically that isn’t true — there was actually a T-SQL Debugger in the Query Analyzer tool from long ago. However, that older tool was not a standard Debugger, and was not integrated into the tool well.

I’ll begin this overview by explaining what a Debugger is, then I’ll explain where and when you might use it, and I’ll run through a practical example or two with screenshots so that you can see it in action.

Debuggers Explained

The simplest debuggers are just programs that watch code as it runs, and when the code has a problem or stops they display a message about the error and the line item where it had the issue.

But debuggers have evolved into more than that. They now allow you to set “breakpoints,” which are places in the code where the debugger will stop even if there is no problem. You can do this in the code environment itself, or when the debugger is accessed. Many debuggers allow you to set “conditional” breakpoints, so that you can say “when this variable reaches 10, break.”

Modern debuggers also show you the output of the code as it runs — showing you the current value of your variables, what line the code is on (a “Call Stack”) and so forth.

Also, many code debuggers have a “command” window — a place where you can type more code.

The Transact-SQL Debugger in SQL Server Manager is built on the very powerful Debugger in Visual Studio, but because of the architecture of SQL Server Management Studio (SSMS) as a management and development tool, it lacks many of the features in the Visual Studio version. For instance, it does not have conditional, data or address breakpoints, remote debugging, and it can’t be scripted. But all in all, it’s an invaluable tool for when you’re developing code.

Where and When to use the Debugger

There are times when you do not need to use a debugger. For instance, if you’re just returning the version of SQL Server:

SELECT @@VERSION;
GO

Then you probably don’t need to watch what the code does. It evaluates a line, it runs. If you have simple logic in the code that walks sequentially through only a few lines, you don’t need to debug that, either.

I also don’t recommend using the debugger for performance tuning. Other tools, such as the Query Plan views (graphical and text) are better suited for finding out where the most time is spent on any given operator.

Where you will want to use the debugger is in complex code that iterates through multiple variables. It’s very valuable to be able to see what the value of a given variable is during the code. For instance, when you’re iterating through a series of objects such as tables or databases where the value isn’t set, then you will probably want to set a counter to walk through those objects using a variable. But I’ve had code that “completed” before all of the objects were counted — a classic beginner’s mistake. In that case, I had increased the variable after the testing condition, and so it stopped one value short of what I needed to do. With a debugger, you can watch the variables and see exactly what the values are as the code runs.

Another place where the debugger is useful is when the code “branches” or changes what lines it runs based on a condition. Suppose you have some code that says “when I get to 10 in this variable, jump to another part of the code” or when you use CASE statements. Since the code is very dynamic, it can be difficult to track down a problem or “bug” in the software, since you’re never sure what part of the code is running. With the debugger, you can watch the progression of the code and find what is causing the issue.

I find myself using the debugger mostly with Stored Procedures. You can highlight just one Stored Procedure call and run the debugger. It will “explode” the code inside of the stored procedure, show you what it is doing, display the variables, and if the Stored Procedure calls other code, it walks through that as well. I use this part all of the time.

Debugger Walkthrough

To run the T-SQL Debugger you’ll need SQL Server 2008 Management Studio (SSMS) or higher. Once you open that tool and log in to a server, open a Query Window. Now that you’re inside, you can type a query – this is the one I’m using:

/* T-SQL Debugger */
DECLARE @a int = 1
DECLARE @b int = 0

WHILE @a < 4
BEGIN
	SELECT @a = @a +1
	SELECT @b = @b + 1
END

And then highlight that. You can start the Debugger in two places. One is from the menu bar, called “Debug.” The second is the little green arrow next to the !Execute button:

Figure 1

In my opinion, this is a terrible place to have this button. It’s right next to execute, so it’s easy to hit – and it actually looks like the old “Execute” button in Query Analyzer! You can easily move it by right-clicking in the gray area next to the icon bar and choosing “Customize” from the menu that comes up. Then just left-click and drag that icon somewhere else on the icon bar — I normally place it at the end.

But that’s not important for the operation of the tool. When you highlight the code and select Debug from either method, you’re placed in a Debugger window, which takes the place of the Query Window:

Figure 2

Notice the “Debugging query...” status bar message, and also pay attention to the panels shown at the bottom of this screen.

At the top of the screen is the icon bar. The red square stops the query, and the blue square to the far right of it stops the Debugger. If you do that, you’ll see an error message in the “Messages” tab, but it’s only informing you that you terminated the debugging session.

Just to the right of the blue square is a yellow arrow that shows the next statement, which isn’t that useful yet. Just to the right of that are the controls to move through the code. The first “Steps Into” which means run the line of code you’re on, the next is “Step Over,” which skips the line you’re on and moves to the next one, and the last is “Step Out” which leaves the code gracefully.

Just to the right of that icon is a white square with a red circle on it. This is the “Breakpoint” menu, and there are quite a few things you can do there. I’ll leave that for a moment and set a breakpoint later.

In the main Query Window screen you’ll notice a yellow arrow off to the left. That indicates where you are in the code. Just below that is the status bar, and then there are two panels that take up the bottom section of the screen.

Each of these panels have tabs. The panel on the left shows the “Locals,” or local variables. You’ll notice in my example I have two variables, @a and @b. Neither has a value (yet). As you move through the code with “Step Into,” the “Value” column will show you the current value each variable has set.

You can right-click the variable line and set the value you would like the variable to have — very handy for testing code.

I only have two variables here, but in complex code you might have dozens of them. In that case, you might only care about a few of them at any one moment. You can right-click that line again and add a “Watch,” which is just a subset of the variables you would like to, well, watch. When you do that, You can switch to the “Watch 1” (or however many you have set) and watch just those variables. It’s kind of a filter.

To the right is the “Call Stack” panel. This explains the name of the script your on. Down below, you’ll see the “Breakpoints” tab. To set a breakpoint, you only have to click in the margin there in the Query Window where the yellow arrow is, next to the line of code you want to break on. That means even if the code starts performing some automated step, it will “break,” or stop, on that line. You can have as many as you want. In this screen I’ve set one on line 15:

Figure 3

Notice also in this graphic that I have “Stepped into” the code several times, so you can see the value of the variables. Depending on the resolution you have on your screen you may be able to notice in my graphic that the @a variable value is in a red color — meaning that it was the last one to change.

The “Command” tab is interesting, especially in the SQL Server context. It is not a SELECT window, or anything like a T-0SQL command window. As an interesting experiment, open that tab and type “File” and then a period. You’ll see that the commands you have available are for the environment, not for the database.

Finally you’ll see the “Output” tab. This tab is also different than what you might expect — it has to do with the output of the system, not the output of the query. The output of the query shows up where it always does.

So I find the last two tabs less useful for debugging than the other information.

If you’re trying this experiment with my sample code, just keep clicking “Step Into” over and over until the code completes, watching all of the windows so that you get a feel for what it does. Once the code completes, you’ll be dropped back into the Query Window, although you’ll see the “Output” window at the bottom of your screen. I normally close that after I read the information, usually “0” meaning that the code completed with no errors.

With that complete, it’s time to try something a little more complex. I’ve added the following statement to my Query Window (notice the breakpoint is still set — you can set breakpoints outside of the debugger if you wish):

EXEC sp_help;
GO

This code gets help on the current object, if you don’t specify any parameters.

I’ll highlight that and run it in the Debugger:

Figure 4

With that running, I see that the sp_help stored procedure breaks out into quite a bit of code, with quite a few variables (parameters):

Figure 5

Working through Stored Procedures this way is incredibly useful — especially when they call other code. And it’s a great idea to work through the Microsoft Stored Procedures like this as well — it will help you learn how to program and how the product is put together.

InformIT Articles and Sample Chapters

If you want to delve a little deeper into the “big brother” of this tool, read this chapter on Visual Studio: Using Visual Studio .NET to Debug Applications.

Books and eBooks

And here’s a full book for that topic: Advanced .NET Debugging.

Online Resources

There’s more on this subject in its official documentation here.