LLI Wiki — My Digital Knowledge Base

SQL Server : sessions connectées et requêtes en cours

Trois requêtes DMV pour voir les sessions connectées, leur dernière requête et l'avancement des requêtes en cours.

Trois requêtes DMV pour voir les sessions connectées, la dernière instruction de chacune, et les requêtes en cours avec leur avancement.

Sessions connectées et dernière requête

Cette requête liste les sessions avec leur hôte, le mode d'authentification, la dernière requête et la requête en cours. Les filtres sur la base sont en commentaire.

	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]

Requêtes en cours avec leur avancement

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

Requêtes actives hors sessions système

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

Remarques

Mastodon LLIMastodon SocialFosstodon