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


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 Initial version
Example Usage:
   SELECT dbo.dba_GetSQLForSpid(51)
   SELECT dbo.dba_GetSQLForSpid(spid) AS [SQL text]
      , * FROM sys.sysprocesses WITH (NOLOCK) 


   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 
   RETURN @SqlText

comments powered by Disqus
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.