New Features in SQL Server 2008 Management Studio – Part 3

The article titled New Features in SQL Server 2008 Management Studio – Part 1 discussed new features such as Activity Monitor, Intellisense Support and Region Outlining that are available in SQL Server 2008 Management Studio. The second article New Features in SQL Server 2008 Management Studio – Part 2 discussed more new interesting features such as Object Explorer Details, Object Search, View Object Dependencies, Multi-Server Query Support and Missing Index Hints in Graphical Execution Plan. This is the third and final article within this series. This article explains the Transact SQL Debugger features which is reintroduced in SQL Server 2008. This feature is a helpful for database developers and administrators who are interested in debugging the TSQL code.

Transact SQL Debugger
Database developers and administrators may recall that Transact SQL Debugger was a feature available in SQL Server 2000. However this feature was removed in the SQL Server 2005 release. For a user to debug TSQL code written in SQL Server 2005, the only option available was using Visual Studio for debugging the TSQL code. In SQL Server 2008, Transact SQL Debugger is integrated with SQL Server Management Studio thereby making it easier for database developers and administrators to debug Transact SQL Code.

Akin to Visual Studio, the Transact SQL Debugger has more or less the same functionality like step through Transact SQL statements line by line, using breakpoints, step into, step over, step out from stored procedure, function, triggers etc.

Below is a small example of attempting to debug the ShowPersonAddress inbuilt stored procedure available within the AdventureWorks database. Below is the stored procedure code which can be obtained by running the below TSQL.

USE AdventureWorks
GO
SP_HELPTEXT 'ShowPersonAddress'
GO

Stored Procedure Code
CREATE PROCEDURE dbo.ShowPersonAddress(@StateProvinceId INT = NULL)
AS
-- If @SalesOrderId IS NULL then get the last order
IF @StateProvinceId IS NULL
BEGIN
SELECT @StateProvinceId = StateProvinceId
FROM Person.StateProvince
WHERE StateProvinceCode = 'WA' -- Default is Washington
END
SELECT AddressID, AddressLine1, AddressLine2, City, StateProvinceId, PostalCode, Rowguid
FROM Person.Address
WHERE StateProvinceId = @StateProvinceId

This stored procedure accepts a parameter named @StateProvinceID. If the parameter value is not provided, it will filter the data based on the @StateProvinceID parameter from Person.Address table. If no parameter value is provided then, it will query the Person.StateProvince table and gets the result filtered for StateProvinceCode = 'WA'.

To debug the stored procedure, type the procedure name within a new query window as shown in the below snippet and add a breakpoint to the line being debugged. Then to start debugguing either press the ALT+F5 shortcut or go to the Debug Menu and choose Start Debugging option. There is an easier option to start debugging, it can be started by the pressing green arrow button located next to the execute button as hightlighted in the below snippet.



Once the Debug process starts presssing F11 will step into the Transact SQL code. Once the Yellow arrow reachs the line where there is a Breakpoint kept, press F11 to step into the stored procedure code as shown in the below snippet. SQL Server opens a new query window with the code of stored procedure.



The above snippet shows the debugger going through the stored procedure code and there are many windows like Breakpoints, Watch 1, Call Stack, Locals, Output etc open to give insight of whats happening during the debugging process. Once the debugger has finished execution the results will be dispalyed. To stop the debugging process any time, click the red square button on the SQL Editor or by pressing Shift+F5.



As mentioned above there are different debugger windows which open to provide insight of what's happening during the debugging process. Below is a description of the information these windows provide: -

Breakpoint Window: - displays information related to the breakpoint which has been selected with the TSQL code. In the above snippet it mentions that at line 3 user has kept the breakpoint.

Call Stack Window: - displays the current execution status. This also informs from which query window the execution passed to the current window. This happens the breakpoint has been kept at the stored procedure, trigger or function level. In the example the execution has passed the stored procedure window to window which has the actual stored procedure code.

Locals and Watch Windows: - display information related to TSQL Variable, parameter, Functions etc that have names starting with @@. As seen in the above image, it also displays values which are currently assigned to variables. In the example the value being assigned to the @StateProvinceID variable is 79.

Output Window: - shown at the bottom of the above snippet, it carries system level messages from the Transact SQL Debugger.

Results & Messages: - contains the results and messages after the successful exeution.

As seen in the above example, how easy it is to use the inbuilt Transact SQL Debugger feature available in SQL Server 2008 Management Studio. However ensure that the Transact SQL Debugger on Production Server is used directly. It should only be used in Test Server as debug sections usually take long time to complete and during the debugging process there are locks acquired by objects that affect the overall performance of the queries.

Configuring Transact SQL Debugger
The Transact SQL Debugger consists of two major components. The first component, “Server Side Debugger” is installed with the Database Engine of SQL Server 2008 on the server. The second component, “Client Side Debugger” is installed when installing client side tools like SQL Server 2008 Management Studio on the client machine.

There is no configuration required when wanting to run Transact SQL Debugger on the same server where both Database Engine and SQL Server 2008 Management Studio tool are installed. However, if required to run Transact SQL debugger in scenarios where both Database Engine and Management Studio tools are installed on two different servers then ensure that necessary exceptions are added to Windows Firewall. Add Port 135 and also add SQLSERVR.EXE program as an exception to Windows Firewall on the server where Database Engine is installed. Next, add Port 135 and SSMS.EXE (SQL Server Management Studio) program as an exception to Windows Firewall at the client server before starting the Transact SQL Debugger. To find more details on how to configure windows firewall to enable remote connection, refer to the previous article titled Configure Windows Firewall for SQL Server Remote Connections.

Conclusion
This article has shown how easily database developers and database administrators can benefit from Transact SQL Debugger feature which is now available within SQL Server Management Studio of SQL Server 2008.