How to use the new function fn_get_sql()
j9988 2003-11-26 09:16:59
How to use the new function fn_get_sql() and the new columns of sysprocesses introduced in SQL Server 2000 Service Pack 3 (SP3)?
Microsoft SQL Server 2000 SP3 (SP3A) has introduced many security fixes, changes in functionality, and new features. In this article, I'm going to discuss one of the new features introduced in SP3, the fn_get_sql() function and the 3 new columns added to sysprocesses system table. The three new columns are:
Column name Data type Description
sql_handle binary(20) Contains a handle to the currently executing batch/query/stored procedure.
stmt_start int Starting offset of the current SQL statement for the specified sql_handle.
stmt_end int Ending offset of the current SQL statement for the specified sql_handle.
-1 indicates that the current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.
The new diagnostic function fn_get_sql() can be used to inspect a particular process (spid) to see which line of code is currently being executed. As I said, this is a diagnostic function, and it can be used to troubleshoot long running stored procedures and identify performance issues. This function accepts sql_handle (obtained from sysprocesses table) of a spid, as a parameter, and retrieves information about that spid. The other new columns, stmt_start and stmt_end are used to determine, which line of code (of the batch or stored procedure) is being executed (At the end of this article, I also provided links to useful SQL Server performance tuning books).
Prior to the release of SP3, we relied on the command DBCC INPUTBUFFER to see what code/query a particular process was executing. But it has some limitations. DBCC INPUTBUFFER can only display the first 255 characters of the batch or stored procedure. Also, DBCC INPUTBUFFER only displays the first command executed by the process. That is, nested stored procedure calls are not displayed. Meaning, if spid 55 executes procedure proc_1 and proc_1 calls another procedure proc_2, which in turn calls proc_3 and you execute DBCC INPUTBUFFER while proc_3 is being executed, you will see proc_1 as the output, not proc_3. fn_get_sql() overcomes all these problems, and can be used to pinpoint the line of code that is being currently executed.
For a given process, fn_get_sql() returns the following resultset (taken from SQL Server Books Online):
Column name Data type Description
dbid smallint Database ID. Is NULL for ad hoc SQL statements.
objectid Int ID of the database object. Is NULL for ad hoc SQL statements.
number smallint The number of the group, if the procedures are grouped. Is 0 for entries that are not procedures and NULL for ad hoc SQL statements.
encrypted Bit Indicates whether the object is encrypted.
0 = Not encrypted
1 = Encrypted
text Text Text of the SQL statement. Is NULL for encrypted objects.
SQL Server 2000 Books Online (btw, you need to get the updated Books Online that got released with SP3) states that some SQL statements cannot be cached, and fn_get_sql() cannot inspect such processes. That is what I found in my testing as well. But I managed to find handles for almost all stored procedure calls. For more information and comments on fn_get_sql(), read up SQL Server Books Online.
Now it is time to see the new function in action. The following stored procedure 'ShowCodeLine' returns the currently executing line of a given spid. It accepts the following two parameters:
Parameter name Data type Description
@SPID int The process identification number (SPID) of a process to be examined. You can obtain the spid of a process, by looking at master.dbo.sysprocesses directly, or by running sp_who or sp_who2. You could also get the spid of a process from Profiler output. This is a mandatory parameter.
@WAIT tinyint This procedure ShowCodeLine inspects the specified process, repeatedly in an infinite loop, until the process completes. By specifying a valid value for this parameter, you could slow down the looping by the specified number of seconds. You could specify a value (in seconds) between 0 and 60 for this parameter. Default is 0. While inspecting a long running process a value >= 1 seconds would be ideal, to avoid too many output rows.
@NoLoop bit Defaults to 0. When 0, this procedure inspects the specified spid continuously, at specified intervals, until the spid completes. When 1, the specified spid will be examined only once, and the procedure terminates.
ShowCodeLine returns a value of 0 on successful completion, and -1 in case of any failures.
This procedure can be created in any database.
WHERE ROUTINE_NAME = 'ShowCodeLine'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'PROCEDURE'
PRINT 'Procedure ShowCodeLine already exists...Dropping it and recreating'
DROP PROC dbo.ShowCodeLine
PRINT 'Creating procedure ShowCodeLine'
CREATE PROC dbo.ShowCodeLine
@WAIT tinyint = 0,
@NoLoop bit = 0
--Author: Narayana Vyas Kondreddi
--Date Created: 20031016
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
RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)
SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)
WHILE 1 = 1
SELECT @sql_handle = sql_handle,
@stmt_start = stmt_start/2,
@stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
WHERE spid = @SPID
AND ecid = 0
IF @sql_handle = 0x0
IF @handle_found = 0
RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
RAISERROR('Query/Stored procedure completed', 0, 1)
SET @handle_found = 1
SET @line =
COALESCE(NULLIF(@stmt_start, 0), 1),
(@stmt_end - @stmt_start)
RAISERROR(@line, 0, 1) WITH NOWAIT
IF @NoLoop = 1
WAITFOR DELAY @wait_str
Now, to examine a process with spid 55, run the following:
EXEC dbo.ShowCodeLine 55
To examine a process with spid 55, but every 2 seconds (instead of the default 0 seconds):
EXEC dbo.ShowCodeLine 55, 2