Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: CPU and MEMORY usage

    Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfoming

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    execute sp_who2 active

    this will tell you whos is on your db and how much cpu (CPUTime) and mem (DISKIO) each user is taking


    Cheers

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Actually gives the user activity accross the server you run it on. Not just the db activity - Sorry

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    should use Performance Mointor to assess your server activity over a period of time.

    you can log this info in .csv then import into SQL Server

    have a look at :

    http://www.sql-server-performance.co...nitor_tips.asp

    cheers

  5. #5
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    On sp_who2 does the CPUTime equals to the CPU usage on the task manager and what about the Memory because i also want this memory to be the same as the one on the Task Manager

  6. #6
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Not sure about that. I think cputime that a sp_who will show the diskIO for each user and system query that occurs at the time of the SP execution. This does not take into consideration the O/S resources getting used.

    I guess you could find out how much mem is allocated to SQL by looking at the properties of the server (right click on the server in Enterprise Manger) . From this figure you can work out how much resources are getting used in terms of how much mem has been allocated to SQL Server.

    Cheers

  7. #7
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    I 100% agree with you when you say we can manually look at properties and again on the task manager properties, the thing is we want a script that will do this for us automatically every hour to check the memory and cpu usage.

  8. #8
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Use performance mointor as it is the easiest way to shedule.

    Look over the link above to see what counters you want to look at.

    i.e Memory -- Pages/Sec, Memory Manager -- Total Server Memory (KB)
    etc

    What you could do is shedule it to output a csv file on the hour then set up a dts task to import this into SQL Server for anyalsis.

    Once you know how much mem you allocated to SQL Server then you have constant that you can do calculations with.

    Cheers

  9. #9
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    Thanks, I think this will work perfect

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •