dbo.sp_WhoIsActive

from: https://devjef.wordpress.com/tag/sp_whoisactive/

 the easy way out: sp_WhoIsActive by Adam Machanic (Blog | @AdamMachanic ).

如何使用篇:http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

• @get_plans = 1 – this gives you the execution plans for running queries.
• @get_locks = 1 – gives you an XML snippet you can click on to see what table, row, object, etc locks each query owns. Useful when you’re trying to figure out why one query is blocking others.
• @get_task_info = 2 – if a query has gone parallel and you’re troubleshooting CXPACKET waits, you can figure out what each task in the query is waiting on.

Sample:

 sp_WhoIsActive @get_plans =1 , @get_locks=1

Other command:

sp_who2

sp_who

select * from sys.dm_os_wait_status