locked
How to get instance wise Memory/CPU usage % from sqlserver 2008 cluster RRS feed

  • Question

  • Hi All,

    I have 3 node cluster and 11 sqlserver instances are running on that cluster. can some one share tell me how to get instance wise Memory/CPU usage %.

    Thanks,

    Robb

    Friday, March 22, 2013 9:01 AM

Answers

  • Hello Robb,

    It's always possible to use Perfmon to collect these stats. If you're looking for a T-SQL based approach:

    Find the memory usage:

    select CAST((physical_memory_in_use_kb / 1024.) AS DECIMAL (10,2)) AS [Memory_MB_Used] from sys.dm_os_process_memory

    Find the LATEST CPU utilization for THIS instance:

    select TOP 1
    	DATEADD(MILLISECOND, b.timestamp - si.ms_ticks, GETDATE()) AS [Date_Time]
    	,CAST(record AS XML).value('(/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'INT')
    FROM sys.dm_os_ring_buffers b
    	CROSS JOIN sys.dm_os_sys_info si
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
    ORDER BY timestamp DESC
    Edit: Please note that the sys.dm_os_ring_buffer scheduler monitor updates every minute - it's not a current point in time. SQL Server 2008/R2 hold 256 minutes worth of historical data for this, 2012 is more but I forget the exact number (I don't have a test 2012 instance to check).

    -Sean


    Sean Gallardy | Blog | Twitter


    Friday, March 22, 2013 1:58 PM
    Answerer

All replies

  • You can try by executing the below script in each and every instances ,then come to an conclsion about instance specific values.

    select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from
        (select top 10
            sum(qs.total_worker_time) as total_cpu_time,  
            sum(qs.execution_count) as total_execution_count,
            count(*) as  number_of_statements,  
            qs.plan_handle
        from  
            sys.dm_exec_query_stats qs
        group by qs.plan_handle
        order by sum(qs.total_worker_time) desc
        ) a
    inner join
    (SELECT plan_handle, pvt.dbid, cacheobjtype
    FROM (
        SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
        FROM sys.dm_exec_cached_plans
            OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
         /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa
    PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
    ) b on a.plan_handle = b.plan_handle
    inner join sys.databases dbs on dbid = dbs.database_id


    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, March 22, 2013 12:47 PM
  • Hello Robb,

    It's always possible to use Perfmon to collect these stats. If you're looking for a T-SQL based approach:

    Find the memory usage:

    select CAST((physical_memory_in_use_kb / 1024.) AS DECIMAL (10,2)) AS [Memory_MB_Used] from sys.dm_os_process_memory

    Find the LATEST CPU utilization for THIS instance:

    select TOP 1
    	DATEADD(MILLISECOND, b.timestamp - si.ms_ticks, GETDATE()) AS [Date_Time]
    	,CAST(record AS XML).value('(/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'INT')
    FROM sys.dm_os_ring_buffers b
    	CROSS JOIN sys.dm_os_sys_info si
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
    ORDER BY timestamp DESC
    Edit: Please note that the sys.dm_os_ring_buffer scheduler monitor updates every minute - it's not a current point in time. SQL Server 2008/R2 hold 256 minutes worth of historical data for this, 2012 is more but I forget the exact number (I don't have a test 2012 instance to check).

    -Sean


    Sean Gallardy | Blog | Twitter


    Friday, March 22, 2013 1:58 PM
    Answerer
  • As Sean Mentioned, You need to use PerfMon - 

    Yes you can also use T_SQL to achieve this but thats a lengthy and complicated task and the overhead is not worth - so i would recommend you to take the help of PerfMon counters and get the values.


    Sarabpreet Singh Anand

    SQL Server MVP Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, March 23, 2013 4:10 AM
  • Hi all,

    can't we get total memory usage % instance wise. my cluster have 11 instances and 356 database. therefore i need instance breakdown.

    Thanks,

    Robb

    Monday, March 25, 2013 8:33 AM
  • Robb,

    What I posted was PER INSTANCE. Please re-read my post and check the TSQL.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, March 25, 2013 12:17 PM
    Answerer