During Tech-Ed (read my 3 part series here – Part 01, Part 02, Part 03), I learnt about some of the cool new T-SQL debugging enhancements in the next public release of Microsoft SQL Server, SQL 11 (Code Named: “Denali”). I am currently summarizing these enhancements, and will finally provide a ranking of which features I liked best and would tend to use more often once Denali goes into production.
We are currently looking at debugger enhancements related to breakpoints. In the recent posts, we have seen how to setup conditional breakpoints, label them & search for them. We also saw how to export breakpoints from one SSMS session & import them into another SSMS session to stop & continue debugging. Today, we will look at the final debugging enhancement related to breakpoints – “When Hit” do something.
Let’s say that after considerable debugging, you are almost certain that a particular piece of code isn’t the real culprit. You do not want to break anymore when it executes, but yet want to keep an eye on it. The most common solution earlier was to have the expression added to the watch window. But, the watch window only displays the current value. What if you need a log of the various values along with information like the thread/process id? The only option was to introduce custom logging – which may not always be convenient or possible (as it might disturb the arrangement of the existing breakpoints).
“When Hit” do something
Let’s continue our debugging example from last week. For your reference, the script being used can be found below. We had placed conditional debug points at each PRINT statement in the script – one was conditional, while the other was a hit-count based breakpoint.
DECLARE @iCounter INT = 0
WHILE (@iCounter <= 20)
BEGIN
PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))
PRINT 'Hello Nakul! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))
SET @iCounter += 1
END
GO
Now, let us see how we can ask SSMS to do something when a particular breakpoint is hit.
| Right-click the breakpoint in question, and choose the “When Hit” option |  |
| The “When breakpoint is hit” window comes up
Notice that we have customized the message (details below) and also chosen to “Continue execution” |
 |
| When you hit “OK”, notice that the shape of the breakpoint indicator changes |  |
| Now, when you debug the script, notice that execution does not stop on breakpoint #2 |  |
| When the debugging ends (or even as it is progressing), analyze the “Output” window |  |
As you can see, the “When breakpoint is hit” settings cause the custom message with the appropriate debug values to come up in the Output window. That way, while the execution does not stop at a particular set of breakpoints, you can still keep an eye on what’s going on at those points.
(For those who are wondering why the values for @iCounter are coming up as 3, 7, 11… when we set breakpoint to hit in multiples of 4, the simple reason is that we started the loop at @iCounter = 0)
Message options
Notice that the message in the “When breakpoint is hit” window can be customized. One can use the following keywords:
- $ADDRESS = Current instruction
- $CALLER = Previous Function name – useful when debugging nested SPs and triggers
- $CALLSTACK = Call stack
- $FUNCTION – Current Function Name
- $PID, $PNAME = Process Id and Name
- $TID, $TNAME = Thread Id and Name
Or, have the value of a given expression printed by placing the expression within curly braces “{}”, as we have done in this example – “The value of @iCounter is {@iCounter}.”
Conclusion
Overall, debugging in SQL 11 (“Denali”) is a very welcome, drastic improvement over the prior releases. Enhancements to breakpoints, especially “When hit do something” are some which I see myself using a lot once SQL 11 is in production.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 