SQL Server: current sessions and running requests
Three DMV queries to see connected sessions, their last request and the progress of running requests.
Three DMV queries to see the connected sessions, the last statement of each one, and the requests running now with their progress.
Connected sessions and last request
This query lists the sessions with their host, authentication scheme, last request and current request. The database filters are commented out.
SELECT DB_NAME(MRS.dbid),S.host_name ,auth_scheme,C.encrypt_option ,login_name,
MRS.dbid AS DERNIERE_BD_REQUETE, MRS.text AS DERNIERE_REQUETE,
Q.dbid AS BD_REQUETE, Q.text AS REQUETE,
P.query_plan AS PLAN_REQUETE ,S.*, C.*, R.*
FROM sys.dm_exec_sessions AS S
LEFT OUTER JOIN sys.dm_exec_connections AS C
ON S.session_id = C.session_id
OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS MRS
LEFT OUTER JOIN sys.dm_exec_requests AS R
ON S.session_id = R.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS Q
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS P
--WHERE DB_NAME(MRS.dbid) LIKE '%AppDb%'
--WHERE DB_NAME(MRS.dbid) LIKE '%AppDbOther%'
--WHERE C.auth_scheme <> 'SQL'
ORDER BY s.[host_name]
Running requests with their progress
select
DB_NAME(a.database_id) [Database]
,a.session_id
,s.login_name
,s.host_name
, command
,a.Status
,wait_type
,wait_time
,wait_resource
, SUBSTRING(b.text,(a.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(b.Text)-a.statement_start_offset/2)+1
ELSE (a.statement_end_offset-a.statement_start_offset)/2+1
END) [Executing SQL],b.text
, percent_complete
, done_in_minutes = a.estimated_completion_time / 1000 / 60
, min_in_progress = DATEDIFF(MI, a.start_time, DATEADD(ms, a.estimated_completion_time, GETDATE() ))
, a.start_time
, estimated_completion_time = DATEADD(ms, a.estimated_completion_time, GETDATE() )
from sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
inner join sys.dm_exec_sessions s on a.session_id = s.session_id
Active requests excluding system sessions
SELECT * FROM sys.dm_exec_requests
SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID -- don't show this query
AND session_id > 50 -- don't show system queries
ORDER BY r.start_time
Notes
percent_completeis filled only for commands that report their progress (for example backups, restores, DBCC and index operations). For other commands, the estimate columns stay empty or at zero.session_id > 50filters out most system sessions. It is a convention, not a guarantee.