IF EXISTS
(
	SELECT 1
	FROM INFORMATION_SCHEMA.ROUTINES
	WHERE ROUTINE_NAME = 'ShowCodeLine'
	AND ROUTINE_SCHEMA = 'dbo'
	AND ROUTINE_TYPE = 'PROCEDURE'
)
BEGIN
	PRINT 'Procedure ShowCodeLine already exists...Dropping it and recreating'
	DROP PROC dbo.ShowCodeLine
END
ELSE
BEGIN
	PRINT 'Creating procedure ShowCodeLine'
END
GO

CREATE PROC dbo.ShowCodeLine
(
	@SPID smallint,
	@WAIT tinyint = 0,
	@NoLoop bit = 0
)
AS
BEGIN
	
	--Author: Narayana Vyas Kondreddi
	--Date Created: 20031016
	--Source: http://vyaskn.tripod.com
	
 
	SET NOCOUNT ON
 
	DECLARE @sql_handle binary(20), @handle_found bit
	DECLARE @stmt_start int, @stmt_end int
	DECLARE @line nvarchar(4000), @wait_str varchar(8)
 
	SET @handle_found = 0
 
	IF @WAIT NOT BETWEEN 0 AND 60
	BEGIN
		RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)
		RETURN -1
	END
	ELSE
	BEGIN
		SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)
	END
 
	WHILE 1 = 1
	BEGIN
		SELECT	@sql_handle = sql_handle,
			@stmt_start = stmt_start/2,
			@stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
			FROM master.dbo.sysprocesses
			WHERE	spid = @SPID
				AND ecid = 0
  
		IF @sql_handle = 0x0
		
		/*
			--If you are running this on SQL Server 2005, then change the above line to the following:

			IF(@sql_handle = 0x0) OR (@stmt_start = 0 AND @stmt_end = 0)

			--Without this change, this procedure might go into an infinite loop and needs to be killed
			--In SQL Server 2000, as soon as a batch completed, 
			--the sql_handle column in sysprocesses becomes 0, but that's not the case in SQL Server 2005
		*/
		
		BEGIN
			IF @handle_found = 0
			BEGIN
				RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
				RETURN -1
			END
			ELSE
			BEGIN
				RAISERROR('Query/Stored procedure completed', 0, 1)
				RETURN 0
			END
		END
		ELSE
		BEGIN
			SET @handle_found = 1
		END
 
		SET @line = 
		(
			SELECT 
				SUBSTRING(	text,
						COALESCE(NULLIF(@stmt_start, 0), 1),
						CASE @stmt_end 
							WHEN -1 
								THEN DATALENGTH(text) 
							ELSE 
								(@stmt_end - @stmt_start) 
    						END
					) 
   			FROM ::fn_get_sql(@sql_handle)
  		)
 
		RAISERROR(@line, 0, 1) WITH NOWAIT
 
		IF @NoLoop = 1
		BEGIN
			RETURN 0
		END
 
		WAITFOR DELAY @wait_str
 
	END
 
END
GO

Now, to examine a process with spid 55, run the following:

EXEC dbo.ShowCodeLine 55
GO

To examine a process with spid 55, but every 2 seconds (instead of the default 0 seconds):

EXEC dbo.ShowCodeLine 55, 2
GO

In the stored procedure, notice that I used RAISERROR...WITH NOWAIT, to display the output. I did this, so that the output can be seen in Query Analyzer instantly (because of the NOWAIT parameter). But RAISERROR has a limitation, in that it only displays up to 400 characters. If you need to display more than 400 characters, replace the RAISERROR call with a SELECT, to display @wait_str. But the output of SELECT statement will not be displayed until the procedure completes or the output buffer is full. Also, if your Query Analyzer output mode is set to 'Grid' instead of 'Text', then remember to look at the 'Messages' tab of Query Analyzer's results pane, for output.

Here is a quick way to test the procedure 'ShowCodeLine':

1. Open two query windows in your Query Analyzer (QA) and make sure, both the windows are connect to the same SQL Server instance.

2. In the first window, create the following stored procedure:

CREATE PROC dbo.TestProc
AS
BEGIN
WAITFOR DELAY '00:00:01' --Waits for 1 second
WAITFOR DELAY '00:00:02' --Waits for 2 seconds
WAITFOR DELAY '00:00:03' --Waits for 3 seconds
WAITFOR DELAY '00:00:04' --Waits for 4 seconds
WAITFOR DELAY '00:00:05' --Waits for 5 seconds
END
GO

Because of the WAITFOR commands, the above stored procedure runs for at least 15 seconds. That is a good enough duration for us to examine this procedure using ShowCodeLine.

3. Now record the spid of this first Query Analyzer connection using the system variable @@SPID:

SELECT @@SPID
GO

Note down the resulting spid number from the above command, as we will need to pass it to ShowCodeLine, in the second Query Analyzer window. For this example's sake, assume that the output was 625.

4. In the same Query Analyzer window (first one), run the procedure TestProc:

EXEC dbo.TestProc
GO

5. Quickly switch to the second Query Analyzer window and execute ShowCodeLine, by passing the previously recorded spid:

EXEC dbo.ShowCodeLine 625
GO

In the 'Messages' tab of the second Query Analyzer window's results pane, you will see the code lines from the procedure TestProc being displayed as they are executed.

Note: When you try to examine the spid of an extended stored procedure, using ShowCodeLine, you will only see the name of the extended stored procedure in the output.

As you just observed, you could use ShowCodeLine to see what line of code, a long running stored procedure is currently executing. Also, when a particular stored procedure is blocked, you could use ShowCodeLine to see which line of that stored procedure is blocked. This is especially useful for long batches or lengthy stored procedures. This procedure can be used in conjunction with SQL Profiler for effective troubleshooting and performance tuning of queries.

Before we end, here is a list of SQL Server performance tuning books. Hope you find them as useful as I did.

Book Title	Review available?
The SQL Server 2000 Performance Optimization and Tuning Handbook	No
Microsoft SQL Server 2000 Performance Tuning Technical Reference	Yes. Click here to read
SQL Performance Tuning	No
The Guru's Guide to Stored Procedures, XML and HTML	No
Inside Microsoft SQL Server 2000	Yes. Click here to read
Microsoft SQL Server 2000 Resource Kit	Yes. Click here to read