Identify Blocked SQL Processes Quickly: Listing 2: SQL SERVER: Call the dba_GetSQLForSpid Function

Make a call to the database function dba_GetSQLForSpid to get the underlying SQL, which can show you why performance is slow. The function accepts one parameter: the SQL Server process ID (@spid) of a running process.

CREATE Function [dbo].[dba_GetSQLForSpid]
(
   @spid SMALLINT
)
RETURNS NVARCHAR(4000)

/*-------------------------------------------------

Purpose:   Returns the SQL text for a given spid.

---------------------------------------------------

Parameters:   @spid   - SQL Server process ID.
Returns:   @SqlText - SQL text for a given spid.
Revision History:
      01/12/2006   Ian_Stirk@yahoo.com Initial version
Example Usage:
   SELECT dbo.dba_GetSQLForSpid(51)
   SELECT dbo.dba_GetSQLForSpid(spid) AS [SQL text]
      , * FROM sys.sysprocesses WITH (NOLOCK) 

--------------------------------------------------*/

BEGIN
   DECLARE @SqlHandle BINARY(20)
   DECLARE @SqlText NVARCHAR(4000)
   -- Get sql_handle for the given spid.
   SELECT @SqlHandle = sql_handle 
      FROM sys.sysprocesses WITH (nolock) WHERE 
      spid = @spid
   -- Get the SQL text for the given sql_handle.
   SELECT @SqlText = [text] FROM 
      sys.dm_exec_sql_text(@SqlHandle)
   RETURN @SqlText

END
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.