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.
TEXT AS [query],
Db_name(sysprocesses.dbid) AS dbname,
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.
Db_name(req.database_id) AS database_name,
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.