SQL 11 (Code Name: “Denali”) – Debugging enhancements – Setting, Labeling, Using & Searching Conditional Breakpoints


Way back in March 2011, I attended Tech-Ed India 2011 (read my 3 part series here – Part 01, Part 02, Part 03). During Tech-Ed, 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”). In the next couple of posts, I will attempt to summarize these enhancements, and finally provide a ranking of which features I liked best and would tend to use more often once Denali goes into production.

Why so late?

Some of you might be thinking – “Why is this guy posting this stuff so late? We are already in CTP03!” Well, the answer is simple – I believe in using a feature in practice before writing about it. Debuggers are a very powerful set of tools, and it would not be fair to write about something if I could not provide a few helpful tips & pointers.

As a  background, you may read my previous posts on T-SQL debugging:

It is important to remember that the SQL Server Management Studio for SQL 11 “Denali” is based on the Visual Studio 2010 shell. Hence, the debugging features of VS2010 “magically” flow into SSMS.

Debugging in Denali – Breakpoints

Test Script for this session

We will debug the following script in this session on SQL 11 (“Denali”) CTP03. It is a very simple script that loops through for 20 iterations.

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

Creating Conditional Breakpoints & Labeling them

One of the major features that was missing in the earlier versions of SSMS was the ability to have conditional breakpoints. In SSMS for Denali, conditional break-pointing is possible. To explore it’s features, let’s start debugging by pressing the F10 key as high-lighted in the Hands-On session. I would re-iterate that debugging in SSMS is not at all different than debugging in VS2010.

To demonstrate the fact, let’s place a breakpoint near the line for “Hello World!”. We will make this a conditional breakpoint, to break only if the value of the counter hits 10.

Hit F9 to setup the breakpoint on the desired line

Note that the information in the “Breakpoint” window is non-descriptive. Let’s correct that first

image
Right-click on the breakpoint, and choose “Edit Labels”

image 
In the “Edit Labels” window, type in a descriptive detail and click “Add”

If pre-existing, you can choose one if you like

image
On clicking “OK”, notice that the breakpoint can now be identified easily amongst a pile of breakpoints image
Now, right-click on the breakpoint and go to “Condition” image
Add the required expression as a condition and click “OK” image
Notice that in both the “Breakpoints” window &  editor window, the breakpoint symbol now has a little "plus (+) sign in it image
Let’s create a “Hit Count” based breakpoint for the second line image
For the test, let’s set this breakpoint to hit at intervals in multiples of 4 – at 4, 8, 12,16 & 20.

Hit OK once done to set the breakpoint

image
The Breakpoints window would look like the one in the screenshot image

Hit “Alt + F5” to run through the sample code. The first breakpoint hit will be “Hello Nakul!” when the hit count is 4. To verify whether the hit is correct or not, you can either use the “Locals” window or the “Immediate” window (Go to Debug –> Windows –> Immediate or press Ctrl+Alt+I):

image

Hit Alt+F5 again. The next breakpoint to be hit will again be “Hello Nakul!”. Hit Alt + F5 again, and we will hit the “Hello World!” breakpoint and so on.

Searching for specific breakpoints – using Labels to your advantage

Go to Debug->Windows->Breakpoints or press Ctrl+Alt+B to launch the Breakpoints window.

While we only have 2 breakpoints in this session and it is quite easy to remember them, in production code, there might be tens of breakpoints and it might be difficult to remember each one. What’s more, out of all breakpoints, you might want to work with only the breakpoints that are related to a particular operation – an update to a given table, for example.

In the breakpoints window, take the time out to label each breakpoint carefully. You may want to use your labels such that each label identifies a particular group of operations in question – for example, a label can be – “Updating Production”, “Updating Sales Orders” and so on.

Notice the addition of a “Search” box in the Breakpoint window. The “Search” box allows us to search on a specific column or on all visible columns of the breakpoint window.

image

Choosing “All Visible” in the “In Column” drop-down, let’s type in “World” in the “Search” box and hit Enter. Notice that SSMS will take the other breakpoints off the list. Clearing the search results will bring them back.

image

Concluding for today…

I believe that we have just barely scratched the surface of the debugging features available in SQL 11 (“DenaIi”). In my next post, I will cover how to Export & Import Breakpoints – a boon in collaborative development environment. After that, I will cover “Data Points”, which is in itself a very exciting development to the debugger.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

6 thoughts on “SQL 11 (Code Name: “Denali”) – Debugging enhancements – Setting, Labeling, Using & Searching Conditional Breakpoints

  1. Jens Gyldenkærne Clausen

    Great article – but it looks like there is a minor error in the illustration regarding the hit count breakpoint. The value of **`@iCounter`** is 3 after the 4th run (if the hit count and the counter variable should be aligned, the initial value should be set to 1 – or the increment in the loop could be placed before the breakpoint).

    Anyway, I’m looking forward to learn more about Denali’s debugging features. By the way – a note on which of these features – if any – can be used against 2008-servers would be greatly appreciated. I seem to be able to run the script from this article using the Denali SSMS with a 2008 connection and still have it work fine.

    Like

    Reply
  2. Nakul Vachhrajani

    The breakpoint is set during the first execution, so that is not considered. Thus, the 1st breakpoint is hit when @iCounter = 1, and not when @iCounter = 0.

    Like

    Reply
  3. Jens Gyldenkærne Clausen

    I may be missing something obvious – but when I follow the procedure you’ve laid out in this article, my Denali CTP 3 instance breaks at the following values for @iCounter: 3, 7, 10, 11, 15, 19 (where if I understand you correctly, it should break at 4, 8, 10, 12, 16, 20).

    You can see a screencast of a debug run here: http://screencast.com/t/d6Pn2XmkvQ

    Like

    Reply
  4. Nakul Vachhrajani

    As you mentioned – the devil was in the detail. The difference between the steps I mentioned and the steps followed is that in your case, the breakpoints are set before the debugging session begins. In the steps mentioned by me, I am already in the first iteration of the debugging session when I set the conditional breakpoint.

    The way I have mentioned it is:

    1. Debug session starts
    2. User debugs to the point of interest (Iteration 0, Hit count = 0, @iCounter = 0)
    3. A conditional breakpoint is now set
    4. User continues the debugging and reaches the top of the loop
    5. User continues to debug. The pointers go something like this:
    Iteration 1, Hit count = 1, @iCounter = 1,
    Iteration 2, Hit count = 2, @iCounter = 2,
    Iteration 3, Hit count = 3, @iCounter = 3 and
    Iteration 4, Hit count = 4, @iCounter = 4

    In the screencast that has been captured, the order is:

    1. Breakpoints are set
    2. User debugs the session
    3. Breakpoint is encountered, making the sequence (Iteration = 0, Hit Count = 1, @iCounter = 0)

    Thus, when you encounter Hit Count = 4, the @iCounter = 3, as you mentioned.

    Like

    Reply
  5. 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