This stored procedure will consolidate useful information about an active SPID from sys.syspr
NOTE: Since sys.dm_exec_requests is very dynamic you may not always get results for a particular SPID but this SP will usually show results for a SPID that has been running for sometime and perhaps waiting on some resource to be allocated. You may find this SP quite handy when you are troubleshooting a "stuck" or long running query.
Usage: EXEC OPS_SP_DetailSPID @SPID = < INSERT SPID >
