I’m not a great fan of the debugging feature in management studio, It always strikes me that to get any use from it then you are iterating (looping) too much ie doing TSQL wrong. TSQL is not a procedural programming language and the debugger would seem to be most useful if you did develop TSQL in a procedural manner. If you could use it to peek inside a temporary table or execute a single cte in a complex statement then we are talking 🙂
What I do like to do though is use the little known, at least to the people I speak to, “Trace Query in SQL Server Profiler” option.
This is accessed by R-Clicking in a query window and will run profiler with a filter to the current windows spid. Quite neat.
This feature can be further enhanced to be even more useful. When profiler is run it will default to using the template “TSQL_SPs”. The default configuration of this template is, IMO, not very useful, so I have created my own “TSQL_SPs” template which contains those events and columns that I am interested in. That being, all errors and warnings, SP:StmtCompleted, SQL:StmtRecompile, SQL:BatchCompleted and a few others, with the Duration,CPU, Reads, Writes columns. This is simple to do, create a profiler session with the events and columns that you are concerned about and simply File->Save As->Template and select “TSQL_SPs” from the drop down. Hopefully next time you “Trace Query in SQL Server Pofiler” your template will be loaded and have the SPID filter automatically added.
To my mind, that is real TSQL debugging, how hard is this statement going to hit the metal?