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

-------------------------------
-- 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 ? »

Le 30 août 2010 à 17 h 45 min, zinzineti a dit :

Un exemple comparable se trouve ici :

http://blog.developpez.com/zinzineti/p9243/sql-server-2005/obtenir-les-requetes-en-cours-d-executio-2008/#more9243

Connectez-vous pour répondre
Le 30 août 2010 à 17 h 29 min, zinzineti a dit :

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

Connectez-vous pour répondre

Laisser un commentaire Annuler la réponse.

Vous devez être connecté(e) pour rédiger un commentaire.

Identifiez-vous

Créer un compte

Le Blog SQL Server d'ElSüket

Nicolas Souquet – Expert SQL Server

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

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

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?