Check Currently Running Statements

When the performance dips, the most frequently asked question is which SP/ which query is consuming the most of the CPU/memory/ IO. Till SQL 2k we either need to start a Profiler or need to check sysprocesses and run DBCC inputbuffer. More often than not it would not catch the actual running queries. So, it would be really useful if we have a query which lists down the currently executing queries and other useful details from sysprocesses.

SELECT   spid,
         TEXT                       AS [query],
         Db_name(sysprocesses.dbid) AS dbname,
         cpu,
         memusage,
         status,
         loginame,
         hostname,
         lastwaittype,
         last_batch,
         cmd,
         program_name
FROM     sys.sysprocesses
         OUTER APPLY Fn_get_sql(sql_handle)
WHERE    spid > 50
ORDER BY cpu DESC

DMV dm_exec_sql_text provides an option to list blocking queries.But it lists the currently active queries and not the ones in sleeping state. sysprocesses when used with fn_get_sql(sql_handle) provides details about all the current connections.

SELECT sqltext.TEXT,
       req.session_id,
       req.status,
       req.command,
       req.cpu_time,
       req.total_elapsed_time,
       req.blocking_session_id,
       Db_name(req.database_id) AS database_name,
       req.wait_type,
       req.wait_time,
       req.last_wait_type,
       req.reads,
       req.writes,
       req.logical_reads,
       sqltext_blocking.TEXT    AS blocking_stmt
FROM   sys.dm_exec_requests req
       LEFT OUTER JOIN sys.sysprocesses blocking
         ON blocking.spid = req.blocking_session_id
       OUTER APPLY sys.Dm_exec_sql_text(req.sql_handle) AS sqltext
       OUTER APPLY sys.Dm_exec_sql_text(blocking.sql_handle) AS sqltext_blocking
WHERE  req.session_id > 50

DMVs are cool arent they..Though fn_get_sql was introduced in SQL 2k SP3 it can be used effectively only with OUTER APPLY operator. fn_get_sql is a table valued function which means it cant take a column from a table in a query as its parameter. This was fixed in 2k5 with the introduction of OUTER APPLY operator.