LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon