CPU & Memory usage

  • sqlnaive

    SSCoach

    Points: 17435

    Hello, Is it possible to get the CPU Usage and Memory usage for the SQL server in following way using a query:

    ---------------------------------------------------

    TotalCPU CPUUsage TotalMem MemUsage

    ---------------------------------------------------

  • MarkusB

    SSC-Dedicated

    Points: 37369

    The short answer would be YES, it is possible. All the necessary information can be queried from various DMV's like sys.dm_os_sys_memory, sys.dm_os_sys_info, sys.dm_os_performance_counters or sys.dm_os_ring_buffers.

    I don't have a nice ready-to-use script available for you but here are some starters:

    -- CPU usage by current SQL Instance over the last hour

    DECLARE @ts_now BIGINT

    SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, (cpu_ticks / ms_ticks))

    FROM sys.dm_os_sys_info

    SELECT top (60)

    DATEADD(ms, -1 * (@ts_now - [TIMESTAMP]), GETDATE()) AS MonitorTime,

    SQLProcessUtilization,

    SystemIdle,

    100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization

    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

    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

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

    ) AS y

    ORDER BY record_id DESC

    -- Memory Configuration on the Server Operating System

    SELECT physical_memory_in_bytes/1024 as [Physical Memory_MB],

    virtual_memory_in_bytes/1024 as [Virtual Memory MB]

    FROM sys.dm_os_sys_info

    -- Or alternatively

    SELECT [total_physical_memory_kb]/1024 as [Physical Memory_MB],

    [available_physical_memory_kb]/1024 as [Available_Memory_MB]

    FROM sys.dm_os_sys_memory

    -- Total Memory used by SQL Server instance from Perf Mon

    SELECT cntr_value/1024 as Mem_MB

    FROM sys.dm_os_performance_counters

    WHERE counter_name = 'Total Server Memory (KB)'

    [font="Verdana"]Markus Bohse[/font]

  • sqlnaive

    SSCoach

    Points: 17435

    Thanks. Is there something same for memory utilization monitoring ? I tried googling and didn't found anything.

  • anthony.green

    SSC Guru

    Points: 112354

    the bottom part of the script which marcus provided does the memory utilisation checking

  • calvo

    SSChampion

    Points: 12930

    Glenn Berry[/url] has a great script for performance numbers. Check it out!

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • MarkusB

    SSC-Dedicated

    Points: 37369

    sqlnaive (3/13/2012)


    Thanks. Is there something same for memory utilization monitoring ? I tried googling and didn't found anything.

    Did you have a look at the DMV I told you? It's all in there.

    For even more detailed info check DBCC MEMORYSTATUS

    Here's a good link on how to use it: http://blogs.msdn.com/b/psssql/archive/2009/05/15/how-it-works-dbcc-memorystatus-locked-pages-allocated-and-singlepageallocator-values.aspx

    [font="Verdana"]Markus Bohse[/font]

  • dave hants

    Default port

    Points: 1485

    Hi MarkusB,

    I'm running your script for the cpu ticks, it's great thanks.

    However it returns only null values in the output (the timestamp is fine....) like this:

    2012-03-21 14:18:54.977 NULL NULL NULL

    2012-03-21 14:17:54.977 NULL NULL NULL

    2012-03-21 14:16:54.980 NULL NULL NULL

    If I look manually at the xml records then they typically show something like 96% idle, for example like this:

    <Record id="3210" type="RING_BUFFER_SCHEDULER_MONITOR" time="192698846">

    <SchedluerMonitorEvent>

    <SystemHealth>

    <ProcessUtilization>0</ProcessUtilization>

    <SystemIdle>96</SystemIdle>

    <UserModeTime>312500</UserModeTime>

    <KernelModeTime>1718750</KernelModeTime>

    <PageFaults>137</PageFaults>

    <WorkingSetDelta>40960</WorkingSetDelta>

    <MemoryUtilization>100</MemoryUtilization>

    </SystemHealth>

    </SchedluerMonitorEvent>

    </Record>

    But the values don't seem to be getting through to the select statement above.

    I'm running on SQL Server 2005.

    Is there something silly I'm missing??

    Any tips appreciated.

    Regards, Dave.

  • dave hants

    Default port

    Points: 1485

    Ok got it!

    Looks like the xml records have a typo on our system -

    <SchedluerMonitorEvent>

    should be

    <SchedulerMonitorEvent>

    !!!

    Hope this helps anyone else in the same boat.....

    Rgds, Dave

Viewing 8 posts - 1 through 8 (of 8 total)

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