The DMV for Day 24 is sys.dm_exec_requests, which is described by BOL as:
Returns information about each request that is executing within SQL Server.
This DMV is useful for getting a quick snapshot of currently executing requests on your instance of SQL Server. This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.
-- Look at currently executing requests, status and wait type SELECT r.session_id, r.[status], r.wait_type, r.scheduler_id, SUBSTRING(qt.[text],r.statement_start_offset/2, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) AS [statement_executing], DB_NAME(qt.[dbid]) AS [DatabaseName], OBJECT_NAME(qt.objectid) AS [ObjectName], r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.plan_handle FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt WHERE r.session_id > 50 ORDER BY r.scheduler_id, r.[status], r.session_id;
I like to periodically run this query multiple times against an instance to get a “feel” for what queries and stored procedures are regularly encountering which types of waits, and which ones are expensive in different ways. Unless you have a particularly long running query, the output will be different each time you run this query on a busy server.