Overview of T-SQL and CLR debugging in SQL Server 2005

First published on MSDN on Apr 28, 2006

SQL Server 2005 ships with a new debugging engine that supports debugging of any T-SQL and CLR code running in the server, including batches, stored procedures, user defined functions/aggregates/triggers, etc.  You can use Visual Studio 2005 to debug against SQL Server 2005 or SQL Server 2000, but you can not use Visual Studio 2003 or earlier to debug against SQL Server 2005 because the debugging engine is not compatible.

There are some improvements in debugging in SQL Server 2005:

. It’s much easier to setup than debugging in SQL Server 2000.  You can enable debugging SQL Server 2005 from Visual Studio 2005 by following these simple steps (please note that Remote Debugging Monitor mentioned in the steps is not required for T-SQL debugging).

. Integration of T-SQL debugging and CLR debugging.  You can step in/out from T-SQL code to CLR code, or vise versa.  You will get mixed call stack consisting of T-SQL frames and CLR frames, and inspect T-SQL and CLR variables on any frame.

. Full functionality of CLR debugging.

. Isolation of database connections in T-SQL debugging.  When you break in T-SQL code in one connection, other connections are not suspended and can continue to run normally until they require resource locked by the connection being debugged.  In another word, the impact of debugging a connection to other connections on the server is similar to have a long-running connection.

The easiest way to develop and debug CLR code running in SQL Server is to use a C# or VB SQL Server project in Visual Studio.  When you deploy a SQL Server project, Visual Studio deploys the CLR assembly and its symbol file (.pdb) and source code files to the database so that the assembly is ready for execution and debugging.

When you have a CLR assembly built in other ways, you can also use CREATE ASSEMBLY statement to deploy it to the database.  In this case you need to use ALTER ASSEMBLY ADD FILE to attach the symbol file (.pdb) and source code files to the assembly, and then you can use Visual Studio to debug the assembly.  If you forget to add a symbol file or source file, step-in or breakpoint in the corresponding CLR code will be skipped by Visual Studio.

In Visual Studio there are 3 ways that you can start debugging of T-SQL or CLR code in the database:

. Direct Database Debugging (DDD).  You can open Server Explorer and add a Data Connection to a SQL Server database.  Then you can browse to any T-SQL or CLR object in the database, such as a stored procedure or function, right click on it, and select Step Into to begin DDD.

. Debugging from a SQL Server project.  When you start debugging from a C# or VB SQL Server project (e.g. by pressing F5), the assembly will be built and deployed, and the default test script will be run in debugging mode.  Breakpoints in the default test script or in any T-SQL or CLR code called by the default test script will be hit.  You can also right click on any test script in the project and select Debug Script.

. Application debugging.  You can use Visual Studio to attach to any client application that opens a database connection to SQL Server, e.g. SQL Management Studio, and then you can debug T-SQL or CLR code executed on this connection.  When you attach to the client process, you need to make sure the debugging type includes T-SQL code, along with any other desired types such as native code and/or managed code.  There are some notable limitations in application debugging:

. You have to attach to the client process before the database connection is opened.  Any code run on the connections opened before attaching will be ignored.

. A Data Connection to the server being debugged must present in the Server Explorer.  Connections made by the attached client to databases not listed in Data Connections in Server Explorer will not be debugged.

. You can not step into a T-SQL or CLR store procedure from client code (managed or native).  Usually you need to set a breakpoint in the T-SQL or CLR code that you want to debug.  To do this browse to the desired object in Server Explorer, double click on the object to open its source code, and set a breakpoint.

The following are some imitations of T-SQL and CLR debugging in SQL Server 2005:

. T-SQL debugging is on the statement level.  You can not step into the execution of a select statement.

. Visual Studio 2005 is needed to debug T-SQL.  Microsoft SQL Server Management Studio doesn’t support debugging.  Some third party tools that can be used to debug SQL Server 2005 may be released in the near future (or may have already been released).

. Break into CLR code in one connection freezes all connections that are running CLR code.  CLR debugging doesn’t have connection isolation that is available in T-SQL debugging, because of limitations in CLR debugging architecture.

. Debugging doesn’t work with parallel compiled plan execution.  When a batch or stored procedure being debugged is compiled to a parallel plan, debugging may not work.  In this case you need to disable parallel plan generation to debug it.