sp_what

I have been recently involved with a customer who were running a lot of parallel processes and the DBA team used to run sp_who, sp_who2 & selects from dm_exec_request to know status of those commands. Every time users fire those parallel processes, DBA Team would keep scrolling into the SSMS UI to see desired rows from their monitoring queries.

I made following procedure to make their life a little easier. hope this helps others as well.

No explanations on that this procedure does. Run it on SQL 2005 or SQL 2008 and see it for yourself.

 CREATE proc sp_what ( @session int = null, @plan bit = 0 ) 
as 
begin
    select session_id ,command ,
    blocking_session_id as blocked, wait_time, wait_resource,
    open_transaction_count as trans , percent_complete as [%], 
    cpu_time , 
    convert(varchar, total_elapsed_time /60000 ) + ':' + right('0' + convert(varchar(2), (total_elapsed_time /1000) % 60 ),2 ) as StartedSince,
    reads , 
    writes ,logical_reads, row_count, nest_level, granted_query_memory as mem ,
    object_name( s.objectid) as obj,
    SUBSTRING(s.text, statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) 
    from sys.dm_Exec_requests r cross apply sys.dm_exec_sql_text (sql_handle) s
    where command is not null and session_id <> @@spid

    and ( session_id = @session or @session is null )

    if ( @plan = 1 )
    begin
        select session_id , query_plan from sys.dm_Exec_requests r
        cross apply sys.dm_exec_query_plan (plan_handle) s
        where command is not null and session_id <> @@spid
        and ( session_id = @session or @session is null )
 
    end
end