Article complet: Quelles sont les requêtes et tâches en cours d'exécution dans une instance SQL Server ?

Voici une requête basée sur les vues et fonctions de gestion dynamiques, introduites avec SQL Server 2005.

Elle permet de lister l'ensemble des requêtes actuellement en cours d'exécution dans une instance SQL Server, avec quelques statistiques et caractéristiques :

- le n° de session (SPID),
- le nombre de threads mis en place pour résoudre la requête,
- le nom du serveur,
- le nom du login,
- le pourcentage d'avancement d'une instruction,
- le nom de la base de données,
- le statut de la session,
- le temps écoulé depuis le début de l'exécution,
- le temps CPU consommé,
- le nombre de lectures et d'écritures,
- le type d'instruction,
- le nombre de transactions ouvertes par la session,
- le nombre de resultsets,
- le type d'attente,
- le temps d'attente pour ce type,
- le type d'attente précédent,
- la ressource attendue,
- le nom du programme / job qui exécute la requête,
- l'instruction SQL en cours d'exécution dans le lot,
- le lot SQL complet,
- le plan de requête,
- les hashs de requête et de plan,
- le nombre d'exécutions du plan,
- la date de génération du plan,
- l'adresse IP de la machine exécutant la requête.

Toute suggestion est la bienvenue !

[Suite:]

Mise à jour du 13/05/2011
------------------------------- 
-- Nicolas SOUQUET - 13/05/2011 
------------------------------- 
SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 
;WITH 
  CTE AS 
  (  
    SELECT    CASE ES.login_name 
          WHEN 'NT AUTHORITY\SYSTEM' THEN CASE  
                    WHEN ES.program_name LIKE 'SQLAgent%' THEN 'SQLAgent' 
                    ELSE ES.program_name 
                  END 
          ELSE ES.login_name 
        END AS Login 
        , ES.session_id AS SPID 
        , ER.blocking_session_id AS BlkBy 
        , ER.wait_type 
        , ER.wait_time 
        , ER.last_wait_type 
        , COUNT(*) AS Threads 
        , CAST(ER.percent_complete AS NUMERIC(5,2)) AS percent_complete 
        , DB_NAME(ER.database_id) AS DB 
        , UPPER(ER.command) AS Command 
        , UPPER(ER.status) AS Status 
        , CASE LEN(CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 3600 AS VARCHAR(4))) 
          WHEN 1 THEN '0' + CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 3600 AS VARCHAR(4)) 
          ELSE CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 3600 AS VARCHAR(4)) 
        END + ':' + 
        CASE LEN(CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 60 % 60 AS VARCHAR(2))) 
          WHEN 1 THEN '0' + CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 60 % 60 AS VARCHAR(2)) 
          ELSE CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 60 % 60 AS VARCHAR(2)) 
        END + ':' +  
        CASE LEN(CAST(DATEDIFF(second, ER.start_time, GETDATE()) % 60 AS VARCHAR(2))) 
          WHEN 1 THEN '0' + CAST(DATEDIFF(second, ER.start_time, GETDATE()) % 60 AS VARCHAR(2)) 
          ELSE CAST(DATEDIFF(second, ER.start_time, GETDATE()) % 60 AS VARCHAR(2)) 
        END AS Launched 
        , ER.cpu_time / 1000 AS CPU 
        , ER.reads AS Reads 
        , ER.writes AS Writes 
        , ER.open_transaction_count AS NbTran 
        , SUBSTRING 
        ( 
          ESQLT.text, 
          ER.statement_start_offset / 2 + 1, 
          ( 
            CASE 
              WHEN ER.statement_end_offset = - 1 THEN LEN(CAST(ESQLT.text AS nvarchar(max))) * 2  
              ELSE ER.statement_end_offset  
            END - ER.statement_start_offset 
          ) / 2 + 1 
        ) AS StatementInBatch 
        , ESQLT.text AS Batch 
        , CASE 
          WHEN ER.command = 'UPDATE STATISTIC' THEN 'Updating stats on ' + REPLACE(REPLACE(SUBSTRING(ESQLT.text, CHARINDEX('[dbo]', ESQLT.text), CHARINDEX(' ', ESQLT.text, CHARINDEX('[dbo]', ESQLT.text)) - CHARINDEX('[dbo]', ESQLT.text)), '[', ''), ']', '') 
        END AS info 
        , CASE  
          WHEN ES.program_name LIKE 'SQLAgent - TSQL JobStep%' THEN 'Job : ' + J.name 
          WHEN ES.program_name LIKE 'Microsoft SQL Server Management Studio%' THEN 'SSMS' 
          WHEN ES.program_name LIKE 'LiteSpeed for SQL Server%' THEN 'LiteSpeed' 
          WHEN ES.program_name = 'Microsoft SQL Server Analysis Services' THEN 'SSAS' 
          ELSE ES.program_name 
        END AS Program 
        , ES.host_name AS Host 
        , EC.client_net_address AS IP 
        , ER.plan_handle 
    FROM    sys.dm_exec_sessions ES (nolock) 
    INNER JOIN  sys.dm_exec_connections (nolock) EC  
          ON ES.session_id = EC.session_id 
    INNER JOIN  sys.dm_exec_requests ER (nolock) 
          ON ES.session_id = ER.session_id 
    LEFT JOIN  msdb.dbo.sysjobs J (nolock) 
          ON REPLACE(SUBSTRING(CAST(J.job_id AS char(36)), CHARINDEX('-', J.job_id, 18) + 1, LEN(J.job_id)), '-', '') = 
            RIGHT(LEFT(REPLACE(ES.program_name, 'SQLAgent - TSQL JobStep (Job 0x', ''), 32), 16) 
    CROSS APPLY  sys.dm_exec_sql_text(ER.sql_handle) ESQLT 
    WHERE    ES.session_id <> @@SPID 
    AND    ES.is_user_process = 1 
    AND    (J.name NOT LIKE 'cdc%' OR J.name IS NULL) 
    AND    ES.is_user_process = 1 
    GROUP BY  ES.session_id 
        , ER.blocking_session_id 
        , ES.host_name 
        , ES.login_name 
        , ER.percent_complete 
        , ER.database_id 
        , ER.status 
        , ER.start_time 
        , ER.cpu_time 
        , ER.reads 
        , ER.writes 
        , ER.command 
        , ER.open_transaction_count 
        , ER.statement_start_offset 
        , ER.statement_end_offset 
        , ESQLT.text 
        , ER.wait_type 
        , ER.wait_time 
        , ER.last_wait_type 
        , J.name 
        , ES.program_name 
        , EC.client_net_address 
        , ER.plan_handle 
  ) 
SELECT    C.Login 
    , C.SPID 
    , C.BlkBy 
    , C.wait_type 
    , C.wait_time 
    , C.last_wait_type 
    , C.Threads 
    , C.percent_complete AS [%] 
    , C.DB 
    , C.Command 
    , C.Status 
    , C.Launched 
    , C.CPU 
    , C.Reads 
    , C.Writes 
    , C.NbTran 
    , C.StatementInBatch 
    , C.Batch 
    , C.info 
    , C.Program 
    , C.Host 
    , C.IP 
    , C.plan_handle 
FROM    CTE AS C 
ORDER BY  C.SPID

------------------------------- 
-- Nicolas SOUQUET - 25/06/2010 
------------------------------- 
SET NOCOUNT ON 
 
;WITH 
  CTE_THREADS AS 
  ( 
    SELECT    session_id 
        , COUNT(*) AS nbThreads 
    FROM    sys.dm_exec_sessions 
    GROUP BY  session_id 
  ) 
SELECT    ES.session_id SPID 
    , CT.nbThreads 
    , ER.blocking_session_id BlkBy 
    , ES.host_name AS Host 
    , CASE ES.login_name 
      WHEN 'NT AUTHORITY\SYSTEM' THEN CASE  
                WHEN ES.program_name LIKE 'SQLAgent%' THEN 'SQLAgent' 
                ELSE ES.program_name 
              END 
      ELSE ES.login_name 
    END AS Login 
    , CAST(ER.percent_complete AS NUMERIC(5,2)) AS [%] 
    , DB_NAME(ER.database_id) AS DB 
    , UPPER(ER.status) AS Status 
    , CASE LEN(CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 3600 AS VARCHAR(4))) 
      WHEN 1 THEN '0' + CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 3600 AS VARCHAR(4)) 
      ELSE CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 3600 AS VARCHAR(4)) 
    END + ':' + 
    CASE LEN(CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 60 % 60 AS VARCHAR(2))) 
      WHEN 1 THEN '0' + CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 60 % 60 AS VARCHAR(2)) 
      ELSE CAST(DATEDIFF(second, ER.start_time, GETDATE()) / 60 % 60 AS VARCHAR(2)) 
    END + ':' +  
    CASE LEN(CAST(DATEDIFF(second, ER.start_time, GETDATE()) % 60 AS VARCHAR(2))) 
      WHEN 1 THEN '0' + CAST(DATEDIFF(second, ER.start_time, GETDATE()) % 60 AS VARCHAR(2)) 
      ELSE CAST(DATEDIFF(second, ER.start_time, GETDATE()) % 60 AS VARCHAR(2)) 
    END AS Launched 
    , ER.cpu_time / 1000 AS CPU 
    , ER.reads AS Reads 
    , ER.writes AS Writes 
    , UPPER(ER.command) AS Command 
    , ER.open_transaction_count NbTran 
    , ER.open_resultset_count AS NbRsltSet 
    , ER.wait_type 
    , ER.wait_time 
    , ER.last_wait_type 
    , ER.wait_resource 
    , CASE 
      WHEN ES.program_name LIKE 'SQLAgent - TSQL JobStep%' THEN 'Job : ' + J.name 
      WHEN ES.program_name LIKE 'Microsoft SQL Server Management Studio%' THEN 'SQL Qry' 
      ELSE ES.program_name 
    END AS Program 
    , SUBSTRING( 
          ESQLT.text, 
          ER.statement_start_offset / 2 + 1, 
          ( 
            CASE 
              WHEN ER.statement_end_offset = - 1 THEN LEN(CAST(ESQLT.text AS nvarchar(max))) * 2  
              ELSE ER.statement_end_offset  
            END - ER.statement_start_offset 
          ) / 2 
    ) AS StatementInBatch 
    , ESQLT.text AS Batch 
    , QP.query_plan 
    , ER.query_hash  
    , ER.query_plan_hash 
    , QS.execution_count 
    , QS.creation_time 
    , EC.client_net_address AS IP 
FROM    sys.dm_exec_sessions ES (nolock) 
INNER JOIN  sys.dm_exec_connections (nolock) EC  
      ON ES.session_id = EC.session_id 
INNER JOIN  sys.dm_exec_requests ER (nolock) 
      ON ES.session_id = ER.session_id 
INNER JOIN  CTE_THREADS AS CT 
      ON ES.session_id = CT.session_id 
LEFT JOIN  msdb.dbo.sysjobs J (nolock) 
      ON REPLACE(SUBSTRING(CAST(J.job_id AS CHAR(36)), CHARINDEX('-', J.job_id, 18) + 1, LEN(J.job_id)), '-', '') = 
        RIGHT(LEFT(REPLACE(ES.program_name, 'SQLAgent - TSQL JobStep (Job 0x', ''), 32), 16) 
CROSS APPLY  sys.dm_exec_sql_text(ER.sql_handle) ESQLT 
OUTER APPLY  sys.dm_exec_query_plan (ER.plan_handle) AS QP 
LEFT JOIN  sys.dm_exec_query_stats AS QS 
      ON ER.plan_handle = QS.plan_handle 
WHERE    ES.session_id <> @@SPID 
AND    (J.name IS NULL OR J.name NOT LIKE 'cdc.%') 
ORDER BY  ES.session_id

ElSüket