The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
If you write code, your not going to always get it right the first time. In fact, if your like me, you may rarely get it right the first time. One of the hallmark traits of any good development environment is a mature and informative debugging environment. It’s day 8. Debugging.
At the heart and soul of debugging is understanding what values your variables contain in flight and being able to step through code line by line. Identifying why a loop is exiting incorrectly depends greatly on verifying the control elements of the loop. Knowing why a logical block of code doesn’t seem to be getting hit requires verifying that the conditions of that block are being met.
Stepping Through Code
Once you are running a query in DEBUG mode you’ll notice a new toolbar appears.
By default you are now at line one of your query. You can now step through your code line by line by hitting F11 (step into) or by hitting the related toolbar button. You can also step over blocks or step out of blocks. Hitting the blue Stop button will halt execution of the code while hitting the green Play button will run through to the end (unless a breakpoint has been specified – more on that in a minute).
MSDN details exact differences between step into, over, and out. In short, one goes line by line, one skips blocks, and one exits blocks you’ve already entered. I regularly use Step Into and/or breakpoints. I don’t use the other two very much.
While stepping through code, you can inspect the values of variables by looking at the Locals window. In here are the current values of all known variables, parameters, etc. You’ll notice that variables do not appear in the locals window until you reach the line where they are declared.
Data Tips: You can also view the value of variable by hovering it with your mouse. The value will appear in a data tip window. In fact, you can even pin the data tip to keep an eye on the value as it changes on the same line of interest. I’ll demonstrate that further tomorrow.
Hint: The Yellow line is where your step currently is in code. The Red bubble is a breakpoint.
To really interrogate the internal system you can use the Command Window to issue supported Debug (and other) commands to the debugger. Intellisense is a huge help here. One of my favorite uses of the Command Window is to use the Debug.EvaluateStatement command to pre-emptively test the results of statements or what-if scenarios within the context of a block of logic, code, or variable values.
Full details of the command window can be found on MSDN.
Often when debugging large blocks of code, especially when loops are involved, you don’t want to go step line by line – you just want to run to a point in the code and then stop. Enter Breakpoints.
Breakpoints allow you to pause execution of your T-Sql code midstream at a specific line and then inspect values of statements, parameters, variables, control structures, or the status of external dependencies etc from that point.
You set a breakpoint by simply highlighting the line of code where you would like execution to pause and left clicking in the row header or hitting F9. You’ll know a breakpoint has been added when a red circle appears in the line header.
Once you’ve set a breakpoint right click the red bubble and you’ll notice a lot of additional options. You can set conditions to only break under specific circumstances – when a variable is a specific value for example. You can identify additional actions to take when a breakpoint is hit. You can set a counter to keep track of how often it’s hit. You can even label your breakpoints (which helps when you are working with many breakpoints or when exporting them for use on another system – more on that tomorrow when we talk about team debugging). There are a lot of options here.
Breaking Triggers, Stored Procedures, and other Dependent Objects
A common question when first starting to work with the debugger is how to debug a trigger, nested proc, or other dependent object. It’s pretty straightforward. You can either set a breakpoint at the point in the parent query just prior to or on the same line as the action that will cause a trigger or call a nested proc, then use F11 to step into the dependent object code.
So, for example – if you need to debug a trigger – you could write a quick ad-hoq query that will cause the trigger and then debug that ad-hoq query stepping into your trigger when the time comes.
You can also skip stepping through line by line by setting a breakpoint in the child object. So, with the trigger example, you’d open the trigger by right clicking and selecting modify in SSMS, setting your breakpoint internally to that object and then issuing in another window a query that will trigger or call the action. Just make sure you run the parent query in DEBUG mode. Breakpoints only catch when running in DEBUG mode.
Breakpoints are stored internally to your local system and SSMS so it doesn’t matter that the window containing the trigger and breakpoint are separate from the parent statement that is causing the triggering action. The flip side of this is that setting a breakpoint doesn’t affect other users outside of your local system and instance of SSMS. (again, more on that tomorrow)
Advanced Debugging – Team Debugging – Remote Debugging
Tomorrow we’ll dig deeper into debugging with SSMS. We’ll cover exporting breakpoints, datatips etc. to debug as a team and them move onto debugging remote servers and some of the gotcha’s you may run into there.