|
|
SQL Server T-SQL User-Defined Function of the Week
A Quick Peak at the New System UDF fn_get_sql
Volume 1 Number 14 February 18, 2003
Since this newsletter issue was published I've written a more
extensive article, Find OUt What They're Doing with fn_get_sql,
published by Database Journal. I've also created an interesting
video to accompany the article, which you might also enjoy.
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
SQL Server 2000 Service Pack 3 (SP3)introduces a new system user-
defined function, fn_get_sql. It's meant to make it easier for
developers and diagnostic tools to retrieve the text of the SQL
begin run by currently active SQL processes. This is a technique
commonly used when diagnosing a deadlock.
Before you go to far, you should be aware that there is an
updated Books Online for SP3. It has the new documentation for
fn_get_sql. You can get it without installing SP3 at this link:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
If you're not installing SP3, be careful as you install the
updated documentation. If you let it, the installer
will overwrite your existing Books Online. However,
if you make the right selection, it can be
installed side-by-side with the old BOL.
Prior to SP3 the only way to get the SQL being used by a
SQL process is to use the DBCC INPUTBUFFER command. It takes a
SPID as its argument and returns a rowset consisting of:
Column Name Data type Description
------------ ------------- -------------------------------------
EventType nvarchar(30) Event type such as "Language Event"
or "No Event" or "RPC"
Parameters int 0 = text or 1-n=parameters
EventInfo nvarchar(255) For an RPC it contains the procedure
For a Language Event it contains the
text of the SQL being executed.
This query sort of gives you the idea:
DBCC INPUTBUFFER (@@SPID)
GO
(Results)
EventType Parameters EventInfo
-------------- ---------- ---------------------------
Language Event 0 DBCC INPUTBUFFER (@@SPID)
(End of results)
The data type of the EventInfo column is nvarchar(255). This has
proven to be an annoying limitation because it restricts the
results to the first 255 characters of any SQL Statement. While
that might be enough when the statement is executing a stored
procedure it's often insufficient when a complex SELECT or
UPDATE Is involved.
fn_get_sql overcomes the length restriction by returning the SQL
being executed as a text column. Here's the layout of its
result set:
Column Name Data type Description
------------ ------------- -------------------------------------
dbid smallint Database ID
objectid int ID of the database object. NUll for
ad hoc statements
number smallint The number of the group, if the
procedures are grouped.
encrypted bit 1=Encrypted 0=Not encrypted
text Text Text of the statement. NULL if the
object is encrypted.
fn_get_sql takes a sql_handle as its parameter. This is a change
from DBCC INPUTBUFFER. sql_handle is a BINARY(20) column in
sysprocesses and it can be retrieved using the SPID as in:
/---------- Copy from below this line --------------------------\
DECLARE @handle binary(20)
SELECT @handle = sql_handle
FROM master..sysprocesses
WHERE spid = @@SPID
SELECT [text]
FROM ::fn_get_sql(@handle)
GO
\---------- Stop copying above this line -----------------------/
(Results)
text
---------------------------------------------------------------
-- Retrieve the sql of this connection
DECLARE @handle binary(20)
SELECT @handle = sql_handle
FROM master..sysprocesses
WHERE spid = @@SPID
SELECT [text]
FROM ::fn_get_sql(@handle)
(End of results)
Don't get confused by the fact that the output is identical to
the query. It's suppose to be the same.
I was trying to figure out how to show fn_get_sql in a real world
scenario. That would involve creating a deadlock and then using
sp_who do find out which processes are blocked and then using
fn_get_sql to retrieve the text of the SQL that they were
executing. The thought of sending out code that deliberately
created a deadlock some how struck me as pretty risky. I don't
want to be responsible for hanging anyone's database, so I've
decided on to use a less than real world example. I'm working
on a technique to show the real world example without the risk.
I'll update you as soon as it's ready.
The example below uses two Query Analyzer windows to run two
scripts, A and B. The instructions for how to run them is
included in the text of the script. You should run each batch
of the Script A by itself by selecting the text of the batch up
to the GO command. Once you've changed Script B with the SPID
from Script A, you can run it all at once.
/---------- Copy from below this line --------------------------\
-- Script A
-- This is script A in a two script demonstration of how the new
-- system user-defined function fn_get_sql works. It's intended
-- to be used at the same time as its companion script B.
-- Start by dropping the Example procedure if it already exists.
if exists (select *
from information_schema.routines
where specific_name = 'usp_Example_Delayed_Query')
BEGIN
PRINT 'Dropping procedure usp_Example_Delayed_Query'
drop proc usp_Example_Delayed_Query
END
ELSE BEGIN
PRINT 'Procedure usp_Example_Delayed_Query does not exist.'
END -- endif
GO
-- Now replace the procedure.
CREATE PROC usp_Example_Delayed_Query
AS
WAITFOR DELAY '00:00:30'
SELECT a.au_lname, au_fname, t.title
FROM pubs..authors a
INNER JOIN pubs..titleauthor ta
ON a.au_id = ta.au_id
INNER JOIN pubs..titles t
ON ta.title_id = t.title_id
GO
-- Next, find out which SPID is used for this connection:
SELECT @@SPID as [my SPID]
GO
-- Stop here.
-- Get Script B opened in another Query Analyzer window.
-- put the SPID that was just selected into the place in
-- Script B where it says to <- Replace SPID here
-- When it's ready to run, but not yet running, come back here
-- and run the following query.
EXEC usp_Example_Delayed_Query
GO
-- Once this query is running, go to the other window with
-- script B and execute all of script B.
-- What you should see in the results window for script B is
-- the text of the create procedure script for
-- usp_Example_Delayed_Query
-- This is the end of Script A
\---------- Stop copying above this line -----------------------/
Now in a different Query Analyzer window copy script B.
/---------- Copy from below this line --------------------------\
-- Script B
--
-- This is script B in a two script demonstration of how the new
-- system user-defined function fn_get_sql works. It's intended
-- to be used at the same time as its companion script A.
-- Start with script A and run each batch (up to each GO) until
-- you get to the one that selects the SPID. Use the SPID from
-- script A to replace the number before <- Replace SPID here
-- Retrieve the sql that Script A is running
DECLARE @sql_handle binary(20)
SELECT @sql_handle = sql_handle
FROM master..sysprocesses
WHERE spid = 51 <- Replace 51 with your SPID here
-- When you've replaced the SPID it should be something
-- like 51
SELECT [text]
FROM ::fn_get_sql(@sql_handle)
go -- The results should show the script being run in
-- Script A
-- This is the end of Script B
\---------- Stop copying above this line -----------------------/
The result of running Script B should be the text of the
CREATE PROC script for usp_Example_Delayed_Query. That's the
SQL that Script A is running at the time that Script B is run.
I hope this all works for you. I'm never sure that I've
described how to go back and forth between the two scripts. If
you're having a problem executing it, drop me and e-mail and
I'll see if I can suggest something.
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
