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 !

Mise à jour du 13/05/2011

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
-------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
-------------------------------
-- 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

2 réflexions au sujet de « Quelles sont les requêtes et tâches en cours d’exécution dans une instance SQL Server ? »

  1. Sous SQL 2005 SP 2 les colonnes suivantes ne sont pas reconnues:

    –, ER.query_hash
    — , ER.query_plan_hash

    En les mettant en commentaires ça roule !

    Par contre tout marche nickel sous SQL 2008 R2

    Thanks

Laisser un commentaire