Like most DBAs I frequently use sp_who2 to monitor activity at any given moment. Many times however I would identify a SPID that had a high CPU utilization or came from an unkown host and I would wish that I could see the SQL running under that SPID without having to fire up SQL Profiler.
I finally did some research and found that the DBCC INPUTBUFFER(SPID) would return what I was looking for. The input buffer doesn't always contain SQL, but for the SPIDs I have questions about it usually does. I coded a stored procedure, usp_SQL_In_sp_who2, to include the contents of the Input buffer and the length of the input buffer which I find useful in identifying at-a-glance monster queries.
SQL Server DBA