If your SQL Server 2005 machine is experiencing performance problems and you
need to quickly see all the T-SQL code
currently executing on it, you'll likely be
interested in sp_display_curr_tsql_in_sessions. This stored procedure displays all
the T-SQL statements currently being
executed along with the IDs of the processes running those statements.
As Listing 4 shows, sp_display_curr_tsql_in_sessions
loops through all the current sessions it finds in the sys.sysprocesses system
view. For each session, the stored procedure fetches the sql_handle. It uses
this handle with the fn_get_sql system function to obtain the T-SQL statement
that the session is currently running. The fn_get_sql function is similar to
the DBCC INPUTBUFFER statement in that both return the T-SQL statement that
the specified session is currently executing. However, I prefer using fn_get_sql
because it displays all the text in the T-SQL statement, whereas DBCC INPUTBUFFER
returns only the first 255 characters in the T-SQL statement. The sp_display_curr_tsql_in_sessions
stored procedure stores the session IDs and T-SQL statements in a temporary
table, which it later removes.
To execute the sp_display_curr_tsql_in_sessions stored procedure, you use the
following statement:
exec sp_display_curr_tsql_in_sessions
As you can see, it doesn't need any parameters.
I wrote sp_display_curr_tsql_in_sessions for use on SQL Server 2005. With a
modification, you can get it to work on SQL Server 2000 Service Pack 3 (SP3)
and later. (The stored procedure won't work on previous versions of SQL Server
2000 because the fn_get_sql function was introduced in SP3.) You'd need to use
varchar(8000) instead of varchar(max) with the CONVERT function because varchar(max)
is new to SQL Server 2005.
End of Article
