Capture CPU Utilization in TSQL

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • SQLJunkie
    #1

    Capture CPU Utilization in TSQL

    Happy New Year everyone!

    I would like to capture CPU Utilization % using TSQL. I know this can
    be done using PerfMon but I would like to run TSQL command (maybe once
    every 5 minutes) and see what is the CPU Utilization at that instant so
    that I can insert the value in a table and run reports based on the
    data.

    I have spent a good amount of time scouring google groups but this is
    all I have found:
    SELECT
    (CAST(@@CPU_BUS Y AS float)
    * @@TIMETICKS
    / 10000.00
    / CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
    CPUBusyPct
    FROM
    master..SysProc esses AS SP2
    WHERE
    SP2.Cmd = 'LAZY WRITER'

    Problem is this gives me total amount of time CPU in %) has been busy
    since the server last started. What I want is the % for the instant -
    the same number we see in Task Manager and PerfMon.

    Any help would be appreciated.

    Thanks

    Tags: None
    • Erland Sommarskog
      #2
      Re: Capture CPU Utilization in TSQL

      SQLJunkie (vsinha73@gmail .com) writes:
      I have spent a good amount of time scouring google groups but this is
      all I have found:
      SELECT
      (CAST(@@CPU_BUS Y AS float)
      * @@TIMETICKS
      / 10000.00
      / CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
      CPUBusyPct
      FROM
      master..SysProc esses AS SP2
      WHERE
      SP2.Cmd = 'LAZY WRITER'
      >
      Problem is this gives me total amount of time CPU in %) has been busy
      since the server last started. What I want is the % for the instant -
      the same number we see in Task Manager and PerfMon.
      Performance counters are in sysperfinfo on SQL 2000 and
      sys.dm_os_perfo rmance_counters on SQL 2005, but I could find the item
      you are looking for in these views.

      But I saw in Books Online for SQL 2005 that these values are cumultative. To
      get the present value, sample with some interval. I guess you could to
      the same: query @@CPU_BUSY twice with a second or so in between.



      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • SQLJunkie
          #3
          Re: Capture CPU Utilization in TSQL

          Thanks for your quick response Erland. I ran the following script but I
          don't think this is the correct value. But I cannot find anything
          meaningful???

          DECLARE
          @CPUBusy1 bigint
          , @CPUBusy2 bigint
          , @TimeTicks1 bigint
          , @TimeTicks2 bigint

          SELECT
          @CPUBusy1 = @@CPU_BUSY
          , @TimeTicks1 = @@TIMETICKS

          WAITFOR DELAY '0:00:01'

          SELECT
          @CPUBusy2 = @@CPU_BUSY
          , @TimeTicks2 = @@TIMETICKS

          SELECT
          @CPUBusy1 AS CPUBusy1
          , @CPUBusy2 AS CPUBusy2
          , @CPUBusy2 - @CPUBusy1 AS CPUDiff
          , @TimeTicks1 AS TimeTicks1
          , @TimeTicks2 AS TimeTicks2
          , @TimeTicks2 - @TimeTicks1 AS TimeTicksDiff

          Thanks for your time and help!


          Vishal


          Erland Sommarskog wrote:
          SQLJunkie (vsinha73@gmail .com) writes:
          I have spent a good amount of time scouring google groups but this is
          all I have found:
          SELECT
          (CAST(@@CPU_BUS Y AS float)
          * @@TIMETICKS
          / 10000.00
          / CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
          CPUBusyPct
          FROM
          master..SysProc esses AS SP2
          WHERE
          SP2.Cmd = 'LAZY WRITER'

          Problem is this gives me total amount of time CPU in %) has been busy
          since the server last started. What I want is the % for the instant -
          the same number we see in Task Manager and PerfMon.
          >
          Performance counters are in sysperfinfo on SQL 2000 and
          sys.dm_os_perfo rmance_counters on SQL 2005, but I could find the item
          you are looking for in these views.
          >
          But I saw in Books Online for SQL 2005 that these values are cumultative. To
          get the present value, sample with some interval. I guess you could to
          the same: query @@CPU_BUSY twice with a second or so in between.
          >
          >
          >
          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >
          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            • SQLJunkie
              #4
              Re: Capture CPU Utilization in TSQL

              Thanks everyone for reading this and your responses. I was able to find
              the correct solution in another Google post:

              DECLARE
              @CPU_BUSY int
              , @IDLE int

              SELECT
              @CPU_BUSY = @@CPU_BUSY
              , @IDLE = @@IDLE

              WAITFOR DELAY '000:00:01'

              SELECT
              (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *
              1.00) *100 AS CPUBusyPct


              This solution was posted by Gert-Jan Strik on Tues, Jan 14 2003 6:55
              PM.
              Here is the URL for the thread:
              http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/a6bc2eb3cee9c18d/cf67728586773e8b?lnk=st&q=%40%40CPU_BUSY&rnum=8&hl=en#cf67728586773e8b


              Thanks again,

              Vishal


              SQLJunkie wrote:
              Thanks for your quick response Erland. I ran the following script but I
              don't think this is the correct value. But I cannot find anything
              meaningful???
              >
              DECLARE
              @CPUBusy1 bigint
              , @CPUBusy2 bigint
              , @TimeTicks1 bigint
              , @TimeTicks2 bigint
              >
              SELECT
              @CPUBusy1 = @@CPU_BUSY
              , @TimeTicks1 = @@TIMETICKS
              >
              WAITFOR DELAY '0:00:01'
              >
              SELECT
              @CPUBusy2 = @@CPU_BUSY
              , @TimeTicks2 = @@TIMETICKS
              >
              SELECT
              @CPUBusy1 AS CPUBusy1
              , @CPUBusy2 AS CPUBusy2
              , @CPUBusy2 - @CPUBusy1 AS CPUDiff
              , @TimeTicks1 AS TimeTicks1
              , @TimeTicks2 AS TimeTicks2
              , @TimeTicks2 - @TimeTicks1 AS TimeTicksDiff
              >
              Thanks for your time and help!
              >
              >
              Vishal
              >
              >
              Erland Sommarskog wrote:
              SQLJunkie (vsinha73@gmail .com) writes:
              I have spent a good amount of time scouring google groups but this is
              all I have found:
              SELECT
              (CAST(@@CPU_BUS Y AS float)
              * @@TIMETICKS
              / 10000.00
              / CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
              CPUBusyPct
              FROM
              master..SysProc esses AS SP2
              WHERE
              SP2.Cmd = 'LAZY WRITER'
              >
              Problem is this gives me total amount of time CPU in %) has been busy
              since the server last started. What I want is the % for the instant -
              the same number we see in Task Manager and PerfMon.
              Performance counters are in sysperfinfo on SQL 2000 and
              sys.dm_os_perfo rmance_counters on SQL 2005, but I could find the item
              you are looking for in these views.

              But I saw in Books Online for SQL 2005 that these values are cumultative. To
              get the present value, sample with some interval. I guess you could to
              the same: query @@CPU_BUSY twice with a second or so in between.



              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinf...ons/books.mspx

                Comment

                Previous template Next
                Working...