Post reply

SQL SERVER : CPU

  • March 22, 2013 at 5:18 am

    #272476

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

  • March 22, 2013 at 5:32 am

    #1599723

    Have you tried the dm_os_performance_counters DMV?

    John

  • March 22, 2013 at 6:39 am

    #1599745

    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

  • March 22, 2013 at 10:26 am

    #1599866

    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

  • March 22, 2013 at 10:29 am

    #1599871

    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