CPU & Memory usage

  • 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

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

  • 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]

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

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

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

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

  • 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]

  • 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.

  • 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