Retrouver l’utilisation CPU d’une machine sous SQL Server 2005 et 2008

Avec SQL Server 2005, Microsoft a introduit les vues et fonctions de gestion dynamique qui facilitent grandement l’administration des serveurs.
Voyons comment utiliser celles-ci pour suivre la consommation de CPU d’une machine

Les requête suivantes fournissent la consommation de processeurs d’une machine en pourcentage, en séparant la consommation de CPU par le processus SQL Server de l’instance de la consommation CPU d’autre processus.

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

------------------------------------------------------------------------------------------
--Nicolas SOUQUET - 01/09/2009 - Consommation de CPU (valable jusqu'à SQL Server 2005 SP1)
------------------------------------------------------------------------------------------
DECLARE @cpu_ticks BIGINT
SELECT @cpu_ticks = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
FROM sys.dm_os_sys_info;

WITH
CTE_HeureDeb_HeureFin As
(
SELECT MIN(DATEPART(hour, DATEADD(ms, -1 * (@cpu_ticks - timestamp), GETDATE()))) HDeb,
MAX(DATEPART(hour, DATEADD(ms, -1 * (@cpu_ticks - timestamp), GETDATE()))) HFin
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
),
CTE_Heures AS
(
SELECT DATEADD(hour, CTE_HeureDeb_HeureFin.HDeb, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) HeureDeb,
DATEADD(minute, 15, DATEADD(hour, CTE_HeureDeb_HeureFin.HDeb, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))) HeureFin
FROM CTE_HeureDeb_HeureFin
UNION ALL
SELECT DATEADD(minute, 15, HeureDeb),
DATEADD(minute, 15, HeureFin)
FROM CTE_Heures, CTE_HeureDeb_HeureFin
WHERE HeureDeb < DATEADD(hour, CTE_HeureDeb_HeureFin.HFin, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
)
SELECT CTE_Heures.HeureFin,
AVG(SQLProcessUtilization) SQL,
AVG(OtherProcessUtilization) Other
FROM (
SELECT DATEADD(ms, -1 * (@cpu_ticks - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization OtherProcessUtilization
FROM (
SELECT record.value('(./Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') SystemIdle,
record.value('(./Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') SQLProcessUtilization,
timestamp
FROM (
SELECT timestamp, CONVERT(xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) SCHEDULER
) DATA
) FINAL
JOIN CTE_Heures
ON FINAL.EventTime BETWEEN CTE_Heures.HeureDeb
AND CTE_Heures.HeureFin
GROUP BY CTE_Heures.HeureFin

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

--------------------------------------------------------------------------------------------
--Nicolas SOUQUET - 01/09/2009 - Consommation de CPU (valable pour SQL Server 2005 post-SP1)
--------------------------------------------------------------------------------------------
DECLARE @cpu_ticks BIGINT
SELECT @cpu_ticks = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
FROM sys.dm_os_sys_info;

WITH
CTE_HeureDeb_HeureFin As
(
SELECT MIN(DATEPART(hour, DATEADD(ms, -1 * (@cpu_ticks - timestamp), GETDATE()))) HDeb,
MAX(DATEPART(hour, DATEADD(ms, -1 * (@cpu_ticks - timestamp), GETDATE()))) HFin
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
),
CTE_Heures AS
(
SELECT DATEADD(hour, CTE_HeureDeb_HeureFin.HDeb, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) HeureDeb,
DATEADD(minute, 15, DATEADD(hour, CTE_HeureDeb_HeureFin.HDeb, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))) HeureFin
FROM CTE_HeureDeb_HeureFin
UNION ALL
SELECT DATEADD(minute, 15, HeureDeb),
DATEADD(minute, 15, HeureFin)
FROM CTE_Heures, CTE_HeureDeb_HeureFin
WHERE HeureDeb < DATEADD(hour, CTE_HeureDeb_HeureFin.HFin, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
)
SELECT CTE_Heures.HeureFin,
AVG(SQLProcessUtilization) SQL,
AVG(OtherProcessUtilization) Other
FROM (
SELECT DATEADD(ms, -1 * (@cpu_ticks - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization OtherProcessUtilization
FROM (
SELECT record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') SQLProcessUtilization,
timestamp
FROM (
SELECT timestamp, CONVERT(xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) SCHEDULER
) DATA
) FINAL
JOIN CTE_Heures
ON FINAL.EventTime BETWEEN CTE_Heures.HeureDeb
AND CTE_Heures.HeureFin
GROUP BY CTE_Heures.HeureFin

Il y a en fait une petite erreur d’orthographe qui a été corrigée avec le SP2 de SQL Server 2005, au niveau du XML que contient la colonne record : SchedluerMonitorEvent a été corrigé par SchedulerMonitorEvent

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

-----------------------------------------------------------------------------------
--Nicolas SOUQUET - 01/09/2009 - Consommation de CPU (valable pour SQL Server 2008)
-----------------------------------------------------------------------------------
DECLARE @cpu_ticks BIGINT

SELECT @cpu_ticks = ms_ticks
FROM sys.dm_os_sys_info;

;WITH
CTE_HeureDeb_HeureFin As
(
SELECT MIN(DATEPART(hour, DATEADD(ms, -1 * (@cpu_ticks - timestamp), GETDATE()))) HDeb,
MAX(DATEPART(hour, DATEADD(ms, -1 * (@cpu_ticks - timestamp), GETDATE()))) HFin
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
),
CTE_Heures AS
(
SELECT DATEADD(hour, CTE_HeureDeb_HeureFin.HDeb, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) HeureDeb,
DATEADD(minute, 15, DATEADD(hour, CTE_HeureDeb_HeureFin.HDeb, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))) HeureFin
FROM CTE_HeureDeb_HeureFin
UNION ALL
SELECT DATEADD(minute, 15, HeureDeb),
DATEADD(minute, 15, HeureFin)
FROM CTE_Heures, CTE_HeureDeb_HeureFin
WHERE HeureDeb < DATEADD(hour, CTE_HeureDeb_HeureFin.HFin, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
)
SELECT CTE_Heures.HeureFin,
AVG(SQLProcessUtilization) SQL,
AVG(OtherProcessUtilization) Other
FROM (
SELECT DATEADD(ms, -1 * (@cpu_ticks - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization OtherProcessUtilization
FROM (
SELECT record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') SQLProcessUtilization,
timestamp
FROM (
SELECT timestamp, CONVERT(xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) SCHEDULER
) DATA
) FINAL
JOIN CTE_Heures
ON FINAL.EventTime BETWEEN CTE_Heures.HeureDeb
AND CTE_Heures.HeureFin
GROUP BY CTE_Heures.HeureFin

Facile à mettre dans un rapport SSRS pour tracer un graphe

ElSuket

Identifiez-vous

Créer un compte

Le Blog SQL Server d'ElSüket

Nicolas Souquet – Expert SQL Server

Retrouver l’utilisation CPU d’une machine sous SQL Server 2005 et 2008

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?