Monitor Current queries executing on SQL Server

Thank you ! Very helpful site.

Posted by: Rick Willemain | Jan 24, 2015 11:49:26 AM

SELECT s.session_id, r.status, r.blocking_session_id 'Blk by', r.wait_type, wait_resource, r.wait_time / (1000.0)'Wait Sec', r.cpu_time,
r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000.0)'Elaps Sec', db_name(r.database_id) as DBName,'"'+Substring(st.TEXT,(r.statement_start_offset / 2)+ 1,
((CASE r.statement_end_offset WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)+'"' AS statement_text,
'"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name

(st.objectid,st.dbid)),
'')+'"' AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status, r.blocking_session_id, s.session_id

Posted by: Anjaneyulu | Sep 29, 2020 6:19:09 AM

SELECT dc.session_id,percent_complete,cpu_time,start_time,status,text,command,blocking_session_id,client_net_address,wait_type,wait_time,last_wait_type,
open_transaction_count,estimated_completion_time,reads,writes,logical_reads,row_count,
query_plan FROM sys.dm_exec_connections dc INNER JOIN sys.dm_exec_requests dr on dc.session_id=dr.session_id
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) where dc.session_id<>@@SPID order by start_time desc

Posted by: anjaneyulu | Sep 29, 2020 6:20:02 AM

---------------------------------
which step is running
-------------------------------------------------------
SET NOCOUNT ON
DECLARE @sql_handle binary(20),
@stmt_start int,
@stmt_end int,
@line nvarchar(4000),
@spid int
set @spid=77
SELECT @sql_handle = sql_handle,
@stmt_start = stmt_start/2,
@stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses(nolock)
WHERE spid = @spid
AND ecid = 0
SET @line =
(
SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)
)

Posted by: Anjaneyulu | Sep 29, 2020 6:20:39 AM