Procedure Helps Pinpoint Problematic Processes

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.