SQL Server Performance

Get CPU & Memory Usage from a SQL Query

Discussion in 'T-SQL Performance Tuning for Developers' started by xiii29, Jan 26, 2005.

  1. xiii29 New Member

    Hi,

    I'm able to get the list of process running on a SQL Server but I'm wondering if there is a way to get the CPU & Memory Usage on the server by SQL Server ...

    The goal is to determine whether I can do some large request or not ...

    Thanks !
  2. satya Moderator

    One way is to use PERFMON (SYSMON) to get the values for the above counters alongwith others for further assessment.

    To investigate potential memory bottleneck, you can use this query:

    SELECT cntr_value/1024 as 'MBs used'
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Memory Manager' and
    counter_name = 'Total Server Memory (KB)'

    For other counters


    SELECT 'Procedure
    Cache Allocated',
    CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
    * 8192)/1024)/1024)
    as 'MBs'
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Buffer Manager' and
    counter_name = 'Procedure cache pages'
    UNION
    SELECT 'Buffer Cache database pages',
    CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
    * 8192)/1024)/1024)
    as 'MBs'
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Buffer Manager' and
    counter_name = 'Database pages'
    UNION
    SELECT 'Free pages',
    CONVERT(int,((CONVERT(numeric(10,2), cntr_value)
    * 8192)/1024)/1024)
    as 'MBs'
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Buffer Manager' and
    counter_name = 'Free pages'


    HTH


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. xiii29 New Member

    Thanks !

    To be honest, i'm not really sure to understant what the result give me

    'Total Server Memory (KB)' is the memory that is allowed to the server

    'ProcedureCache Allocated' is what is really using

    'Buffer Cache database pages' ??

    'Free pages' : memory that is able to use ...

  4. satya Moderator

    THen ascertain the values collected from PERFMON(SYSMON) for CPU, memory, physical disk, SQL memory and there is a section alone about collecting the PERFMON in this website.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page