First post, long-time lurker & admirer of many of the contributors on this forum Just a point on sp_whoisactive being a procedure...

I work in on a contract basis in financial services (as I'm sure do others on this site/forum), it's a very locked down environment due to SOX, regulatory requirements, financial meltdown, paranoia, global warming, butterfly wings beating too fast/slow, etc.

I'm termed/titled as a DEV DBA/SQL Architect(?)/DB Speeder-Upper(??)/Snr SQL Dev/General DB DogsBody who also handles escalations from production support & tries to (sometimes gently) advise the offshore based DBA's on managing a range of database applications which I'm notionally responsible for.

Sp_whoisactive is a fanastic tool and all credit Mr Machanic for writing & equally as importantly in maintaining it. In no environment do I have access to the master DB to install in it - I'm not part of the official DBA production support group/organisation - nor do I want to be for multiple political/geographical reasons. I don't want to keep installing Sp_whoisactive in multipe user DB's as they get refreshed from Prod on a regular basis and on Prod systems I have R/O & DMV access only.

Changing sp_whoisactive from a procedure to a script that can run anywhere (assuming I have the underlying privileges) and therefore don't need to create it in a database took aprrox 30 minutes a few months back. This has paid handsome dividends in the knowledge gained from it's use. Sometimes, sometimes it seems too 'heavy' & doesn't return quickly enough to capture fleeting production issues or on some occaisions it may not return at all :w00t:. So I tend to run a set of lightweight queries along-side it referencing; sys.dm_exec_requests join sys.dm_exec_sessions cross apply sys.dm_exec_sql_text (as mentioned by Gail and others on this thread) with a cross apply on sys.dm_exec_query_plan thrown in.

A topic that there are as many variations of answers for as there are situations to which those answers may apply.

That’s my .02c.

All the best

Michael