Optimizing T-SQL Debugging in SSMS

It is difficult to imagine database development and maintenance without debugging. Debugging tools help developers quickly identify and resolve errors in their SQL queries and procedures. When stepping through code execution, developers can also detect performance issues and validate the logic of stored procedures or functions.

There are multiple debugging tools to find errors in the code, including SQL Server Management Studio (SSMS), Visual Studio, or dbForge SQL Complete. Regarding SSMS, it earlier came with built-in debugging capabilities. However, starting with v18, they have been deprecated. Instead, developers can easily use the dbForge SQL Complete add-in for SSMS, which offers advanced debugging features.

In the article, we'll explore how to set up debugging in SSMS and walk through common T-SQL debugging scenarios to demonstrate the practical use of debugging tools, such as breakpoints, watches, and the call stack.

Overview of T-SQL debugging in SSMS

SQL Server Management Studio provides debugging capabilities to detect errors in T-SQL code at runtime. In addition, developers can ensure that SQL scripts, stored procedures, functions, and triggers have valid code and optimized performance. With the tool, they can:

  • Set breakpoints in T-SQL scripts to pause the execution at specific lines of code
  • Step through code line by line to view how each line of code executes and how variables and data structures are managed
  • Monitor the current values of variables, expressions, and query results to analyze how data changes during the execution process
  • Handle exceptions in T-SQL code by highlighting the exact line where the error occurs to troubleshoot and fix issues easily

All these operations can be performed using the following T-SQL Debugger features and tools:

  • Breakpoints to specify the location to pause the execution when the T-SQL Debugger reaches the specified line. In addition, SSMS lets you set conditions on breakpoints, such as stopping only when a variable reaches a specific value
  • Watches window to add or remove variables to monitor their values during the debugging session
  • Locals window to display all local variables currently in use, along with their values
  • Call Stack to view the sequence of calls passed during the execution of your code up to the current position
  • Immediate window to execute T-SQL statements on the fly during debugging to test code snippets or query variable values
  • Output window to view various messages and program data, such as system messages from the T-SQL Debugger
  • Results and Messages windows to view the results of the previously executed Transact-SQL statements
  • Step Into option to move to the next line of code, including stepping into called procedures or functions
  • Step Over option to execute the current line of code without stepping into any called procedures or functions
  • Step Out to complete the execution of the current procedure or function and return to the calling code
  • Continue option to resume code execution after it has been paused at a breakpoint
  • Stop Debugging option to terminate the debugging session and stop code execution immediately

Setting up the environment for T-SQL debugging

Before you start debugging, you need to configure your environment properly. First, ensure that you have the necessary permissions to debug in SSMS, such the ALTER TRACE and CONTROL SERVER permissions. Otherwise, debugging will not be possible. Also, enable debugging Firewall exceptions on the client side and check that the login is a member of the sysadmin fixed server role.

Then, launch SSMS and connect to the SQL Server instance you want to debug. To proceed, turn on the Debug toolbar in SSMS for quick access to debugging controls, such as Start, Stop Debugging, Restart, Step Into, Step Over, Step Out, Call Stack, Breakpoints, and Watches. To do this, go to the View menu and select Toolbars > Debug.

In addition, you can define query execution timeout to have enough time for debugging. To set a timeout value, navigate to Tools > Options > Query Execution > SQL Server > General, enter the value in seconds, and click OK to save the changes.

After that, click New Query to open the Query Editor window and execute the script you want to debug. Note that the window will be in the debug mode until the last statement in the Query Editor window finishes or you stop the debug mode.

Specify the timeout in SSMS

However, it should be noted that if you debug a SQL Server instance on a different machine than where SSMS is installed, the following requirements must be met:

  • The machine running SSMS and the machine hosting SQL Server must either be on the same network or connected via a VPN if they settle on different networks
  • The TCP/IP protocol should be enabled on the SQL Server instance
  • The firewall on both the client and server must allow communication over the ports used by SQL Server and the debugging process
  • The versions of SSMS and SQL Server should be compatible
  • The user account you're using for debugging must belong to the sysadmin role or have the ALTER TRACE permission
  • Windows Firewall rules must be configured to enable Transact-SQL debugging

Common debugging scenarios

Let us illustrate how you can debug your code using breakpoints, watches, or call stack to examine and step through code during execution.

Suppose you need to add a customer to the Customers table using a stored procedure. It will add the customer based on the specified email. The procedure will check if the email exists. If it does, the customer should be added. If it does not, the procedure inserts information about the customer in the table.

To configure a test environment, create the AddCustomer stored procedure: 

CREATE PROCEDURE AddCustomer
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100),
    @ResponseCode INT OUTPUT
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Check if the email already exists
        DECLARE @EmailCheck INT;
        SELECT @EmailCheck = COUNT(*) FROM Customers WHERE Email = @Email;
        
        IF @EmailCheck > 0
        BEGIN
            SET @ResponseCode = 1;
            ROLLBACK TRANSACTION;
            RETURN;
        END

        -- Insert the new customer
        INSERT INTO Customers (FirstName, LastName, Email)
        VALUES (@FirstName, @LastName, @Email);
        
        -- Log the action
        INSERT INTO CustomerLog (CustomerID, Action)
        VALUES (SCOPE_IDENTITY(), 'Customer Added');
        
        COMMIT TRANSACTION;
        SET @ResponseCode = 0;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ResponseCode = 1; -- Error occurred
    END CATCH;
END;
GO

To start debugging, you can set a breakpoint in the stored procedure that will pause code execution at the line you specify. This will allow you to inspect variables and control flow. Open the AddCustomer procedure you've created, place a cursor on the line of the code to break on, and click on the gray bar to the left of the code where to pause execution. For example, set it at the following line: 

SELECT @EmailCheck = COUNT(*) FROM Customers WHERE Email = @Email;

The red circle on the margin indicates the breakpoint.

Set breakpoint

Then, on the toolbar, click Debug or press Alt + F5. To proceed, execute the procedure using the following script, SSMS will pause at the breakpoint: 

-- Declare variable to capture the response code
DECLARE @ResponseCode INT;

-- Attempt to add a customer
EXEC AddCustomer
    @FirstName = 'John',
    @LastName = 'Doe',
    @Email = '[email protected]',
    @ResponseCode = @ResponseCode OUTPUT;

-- Check the response code
SELECT @ResponseCode AS ResponseCode;

In addition, you can add a watch to monitor the value of variables or expressions during debugging. To do this, right-click the @EmailCheck variable and select Add Watch. This will open the Watches window showing the value of @EmailCheck as you step through the procedure. For example, if the value is 0, which differs from the higher value you expect, the logic in the SELECT statement might be wrong, and you might receive incorrect results.

Add Watches

On the toolbar, you can use the following controls to step through the code:

  • Step Into (F11): Moves into the procedure line-by-line. If there is another procedure call, it will also step into that
  • Step Over (F10): Executes the current line and moves to the next without stepping into procedures
  • Step Out (Shift + F11): Executes the remaining code in the current procedure and returns to the calling procedure

Use Step Into (F11) to check how the procedure processes each line and confirm the correct flow of logic.

If your code includes multiple stored procedures, the Call Stack window can help track the sequence of procedure or function calls that led to the current point in the code. To open the Call Stack window, navigate to the Debug menu and select Windows > Call Stack. If a bug occurs, you can view the procedure or function that triggered the issue.

Call Stack

If you want to view how the variable values change in real time, go to the Locals window. For example, as you step past the SELECT @EmailCheck = COUNT(*) line, the Locals window will show the value of @EmailCheck.

In addition, you can view the value of the variable by hovering over the variable in the query. The quick info tip will be displayed.

Quick Info

As you can see, all these debugging tools help analyze, detect, and resolve errors related to logic errors, performance issues, and deadlocks in T-SQL code.

Integration of dbForge SQL Complete

Since SSMS v18 or newer versions lack native T-SQL debugging, you can use dbForge SQL Complete instead. It is a versatile add-in for SQL Server Management Studio and Visual Studio that enhances the SQL database development, improves code quality, and increases the developer's productivity. The tool adds a wide range of capabilities that improve the standard SSMS environment and assist with everything from writing and formatting code to debugging and analyzing performance, including:

  • IntelliSense code completion that provides intelligent code suggestions, automatic code completion, and context-aware hints
  • Customizable code snippets that allow quickly inserting common code structures into the T-SQL scripts
  • Code navigation features, such as Go to Definition, that makes it easier to understand the flow of complex SQL code and track variable or procedure usage in the script
  • SQL code refactoring tools that allow renaming objects, parameters, and variables in the entire script or project
  • Syntax checker that detects potential errors in real time as you write code
  • Execution history that keeps track of all executed queries and allows reviewing and debugging previously run code easily
  • Customizable SQL formatting options that help maintain consistent code style across teams for better readability and debugging, including complex scripts
  • Data viewer that lets you examine the results of your queries directly within the debugging environment
  • Quick access to object information, such as table definitions, column details, and relationship mappings that allow for faster debugging by reducing the need to manually look up schema details

To sum up, integration of dbForge SQL Complete with SSMS provides you with access to a suite of tools that strengthen the T-SQL debugging experience and also contribute to writing better and more maintainable SQL code.

Conclusion

We have examined debugging capabilities SSMS has to debug your stored procedures. These tools help you identify and fix issues that might occur in the code. Due to the absence of the debugger in newer versions of SSMS, it is the best idea to use dbForge SQL Complete. In addition to debugging tools, it provides a variety of other productivity functionalities, including T-SQL formatting and standardizing, code refactoring, code completion, and code analysis.