[SQL Server] Consommation CPU
;WITH
DB_CPU_STAT AS
(
SELECT D.database_id
, D.name AS database_name
, SUM(total_worker_time) / 1000 AS total_CPU_time_ms
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY (
SELECT CONVERT(int, value) AS database_id
FROM sys.dm_exec_plan_attributes(QS.plan_handle)
WHERE attribute = N'dbid'
) AS DB
INNER JOIN sys.databases AS D
ON DB.database_id = D.database_id
GROUP BY D.database_id, D.name
)
SELECT database_name
, total_CPU_time_ms / 1000 AS total_CPU_time_s
, CAST(total_CPU_time_ms * 1.0 / SUM(total_CPU_time_ms) OVER() * 100.0 AS decimal(5, 2)) AS [CPU_%]
FROM DB_CPU_STAT
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
ORDER BY total_CPU_time_ms DESC
--Voici un exemple des req top 10 qui consoment plus de CPU :
select
highest_cpu.total_worker_time,
q.dbid,
q.text
from
(
select top 10
qs.plan_handle,
qs.total_worker_time
from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc
) as highest_cpu
cross apply sys.dm_exec_sql_text(plan_handle) as q
--[SGBD][SQL Server] 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.
------------------------------------------------------------------------------------------
-- 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
--------------------------------------------------------------------------------------------
--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
-----------------------------------------------------------------------------------
-- 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