View execution plan for a sproc in the wild

Got stored procedure problems?  I feel sorry for you son.

Here's a quick query to pull the cached execution plan out of memory.

I italicized cached because it's not going to stay there forever.

I used this query to find the execution plan of a stored proc that had been running in a SQL job.  Instead of pulling down the sproc and trying to estimate the query plan, I chose to see what the cached plan looked like.

Open_transaction_count in sys.dm_exec_sessions

This news may be a few months old, but it is worth noting that there is now a column called open_transaction_count in the sys.dm_exec_sessions in SQL Server 2012.

This should put away any need for you to use sys.sysprocesses for session information.