SQL statements can be difficult to diagnose and debug. SQL Server does not include any default way to debug and step through a stored procedure, but Visual Studio does. Using the Server Explorer, you can step through the execution of a stored procedure or function right inside of Visual Studio. The first step is to open the Server Explorer and create a data connection to your database.

You will then see the stored procedures and functions of your database listed in the Server Explorer.

From the Server Explorer, you can right-click on a stored procedure or function and you will see a menu item named Step Into Stored Procedure,

When you select Step Into Stored Procedure, you will see the Run Stored Procedure dialog,

After specifying the values for any parameters the stored procedure has, click the OK button. Visual Studio will now execute the stored procedure and open it in the document window, stopping in the first line of execution.

You can now step through the stored procedure as it executes. You can set breakpoints just as you would in normal code—the only limitation is that you can specify only location and hit count breakpoints.

Because T-SQL is inherently different than .NET languages, the debugging experience is a little bit different. Here are some of the limitations with SQL debugging:

• You can use only location and hit count breakpoints in T-SQL stored procedures and functions.

• You cannot use Step Into to step from .NET managed code to T-SQL. You can set breakpoints in the stored procedure though, and the debugger will break when it comes across them.

• You cannot use Break while a SQL statement is already running.

• You can’t use the Set Next Statement function as you might in managed code.

Some other differences are the facts that you can’t use the memory or registers windows, as they just don’t apply to SQL. Unfortunately, SQL Print statements are not shown in the output window either.

You cannot run triggers directly, but you can set breakpoints in triggers, and if they are triggered, Visual Studio will break into their execution on those breakpoints.

sp_who2 is a well known utility that shows what spids are currently executing. However the information it shows is relatively limited. For example, it only shows the type of command executing as SELECT, DELETE etc, with no reference to the actual underlying SQL executing.

Knowing what SQL is executing can be vital in debugging why a query is taking a long time, or determining if it is being blocked. It can also be useful in showing the progress of a stored procedure i.e. what statement within the stored procedure is currently executing.

The utility makes use of Dynamic Management Views (DMVs)

The Dynamic Management View (DMV) sys.db_exec_requests shows which requests are currently executing, the information shown includes the handle to the whole SQL text of the batch or stored procedure (sql_handle), together with offsets relating to the section of SQL within the batch that is currently executing (statement_start_offset and statement_end_offset).

To determine the current section of SQL currently executing, we need to call the Dynamic Management Function (DMF) sys.dm_exec_sql_text, passing in the handle of the SQL batch that is currently executing, and then apply the relevant offsets.

We can get more information about the query by combining the sys.db_exec_requests DMV with the sys.processes system view (joined on spid/session_id). This information includes who is executing the query, the machine they are running from, and the name of the database.

The utility selects relevant fields from the sys.db_exec_requests and sys.sysprocesses views. The selected fields are described here.

Utility :

CREATE PROC [dbo].[dba_WhatSQLIsExecuting]
AS
/*——————————————————————–
Purpose: Shows what individual SQL statements are currently executing.
———————————————————————-

Example Usage:
1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting
———————————————————————*/
BEGIN
– Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

– What SQL Statements Are Currently Running?
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              — Ignore system spids.
AND session_Id NOT IN (@@SPID)     — Ignore this current statement.
ORDER BY 1, 2
END

This utility allows you to observe the progress of a stored procedure or SQL batch, additionally it can be used to identify the cause of a long running query or blocking query.

Since the utility uses existing data held in DMVs it is relatively non-intrusive and should have little affect on performance.

If the identified queries are long running or causing blocking, it might be worthwhile running them inside the Database Tuning Advisor (DTA), this might identify the cause of the slow running (e.g. a missing index).

For more information visit : http://www.sqlservercentral.com/articles/DMV/64425/