SQL SERVER : CPU

  • Hi , Someone can help me : I want a script that return me the consummation of CPU for last week (last 7 days ) :ermm: .

  • Have you tried the dm_os_performance_counters DMV?

    John

  • You won't be able to show that kind of a time period unless you first set up mechanism for capturing the metrics over time in order to aggregate it. Otherwise, as suggested, you can see what's currently in the sys.dm_os_performance_counters Dynamic Management Object (DMO). But, that's just the current value.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You can see the last 256 minutes from sys.dm_os_ring_buffers.

    Query courtesy of Glenn Berry's diagnostic query script:

    -- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 23)

    -- This version works with SQL Server 2008 and SQL Server 2008 R2 only

    DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

    SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

    SystemIdle AS [System Idle Process],

    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]

    FROM (

    SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,

    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')

    AS [SystemIdle],

    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',

    'int')

    AS [SQLProcessUtilization], [timestamp]

    FROM (

    SELECT [timestamp], CONVERT(xml, record) AS [record]

    FROM sys.dm_os_ring_buffers WITH (NOLOCK)

    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

    AND record LIKE N'%<SystemHealth>%') AS x

    ) AS y

    ORDER BY record_id DESC OPTION (RECOMPILE);

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thank you , I'll take a look 😉

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply