SQL 11 (Code Name: “Denali”) – Debugging enhancements – Breakpoints – “When Hit” do something


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 image_thumb2
The “When breakpoint is hit” window comes up

Notice that we have customized the message (details below) and also chosen to “Continue execution

image_thumb4
When you hit “OK”, notice that the shape of the breakpoint indicator changes image_thumb6
Now, when you debug the script, notice that execution does not stop on breakpoint #2 image_thumb10
When the debugging ends (or even as it is progressing), analyze the “Output” window image_thumb12 

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.

Advertisements

6 thoughts on “SQL 11 (Code Name: “Denali”) – Debugging enhancements – Breakpoints – “When Hit” do something

  1. Hima

    Lets give Warm Welcome to Denali! This is a great feature and that gves better support for debugging and trouble shooting.
    Thanks for the nice article and sharing good stuff on the new feature.

    Like

    Reply
  2. Nakul Vachhrajani

    Thank-you, Hima for your feedback.

    I am indeed very excited about Denali (I think that just like SQL Server 2005, Denali is going to be a ground-breaking release that would change the way we work with SQL Server), and can’t wait to use it’s rich enhancements at work.

    Like

    Reply
  3. Hima

    Very true! Infact I am super exicted about it and thanks for the good article. Keep posting on more updates about Denali

    Like

    Reply
  4. Hardik Doshi

    Nakul, thanks for nice summary of enhancement in TSQL debugging.

    Denali contains lots of Eye catching features.

    Like

    Reply
  5. Nakul Vachhrajani

    Thank-you, Hardik, for taking the time out to read the posts! I am really pleased that you like them.

    Like

    Reply
  6. Pingback: #0382 – SQL Server – SSMS – Debugging a T-SQL DML Trigger | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s