Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: SQL Server consumes 100% CPU!!!

    The problem is that our production SQL Server ALWAYS consumes the full CPU capacity when in operation. It also does not utilize the full 4G memory alloted to it, but only consistently uses 1.7G. Now, the application using the DB does use a lot of server-side cursors and since the software developer is a 3rd-party vendor, we can only do so much to recommend application optimization. Also, we've discovered that the application modules that were identified as mostly participating in high-CPU utilization does not always consume it highly, although they are the ones mostly involved in it. So, in relation to the limited usage of the 4G-alloted memory, we have theorized that perhaps the SQL processes may be hanging & waiting for each other to complete processing whenever more memory is needed, thus contributing to the server slow down, but this is memory not CPU utilization. We have been wracking our brains for ways to prevent the server slow down due to CPU utilization.

    Any feedback will be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sounds like PeopleSoft to me.

    Look at your cache hit ratio using the NT performance monitor. See if it is very high.

    Check to see if your disk interface is approaching 100% utilization (or whatever saturation is on your hardware). This can happen even when RAM is available due to poor cache usage instruction mixes.

    Check using perfmon or task manager to ensure that SQL Server is using 100% of the CPU. The machine might be a DC or Exchange server, and sweating bullets due to workload that isn't even associated with SQL Server.

    If you have Microsoft Premier Services, get your TAM or ADC working on this. They'll probably open an incident, but it is almost always worth it... Incidents aren't that expensive, and having people chasing their tails for days really IS that expensive!

    -PatP

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Is the appliction on the same server as the SQL Server? This is generally not recommended. I don't suppose you would have the resources to move it off would you?

    What is your overall memory utilization at?
    What version of windows and SQL Server are you using? Are you on the latest service packs for each?

    Have you ran a Profiler to see what is consuming the CPU usage on the SQL Server?

    How many CPU's do you have? If you have for example four CPUs, you might try limiting your SQL Server to two of them and seeing what happens then. Particularly, are the two it's not using max'd out or only the two using SQL Server?

    What is the size of your paging file?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    May 2004
    Posts
    7
    Quote Originally Posted by Pat Phelan
    Sounds like PeopleSoft to me.

    Look at your cache hit ratio using the NT performance monitor. See if it is very high.

    Check to see if your disk interface is approaching 100% utilization (or whatever saturation is on your hardware). This can happen even when RAM is available due to poor cache usage instruction mixes.

    Check using perfmon or task manager to ensure that SQL Server is using 100% of the CPU. The machine might be a DC or Exchange server, and sweating bullets due to workload that isn't even associated with SQL Server.

    If you have Microsoft Premier Services, get your TAM or ADC working on this. They'll probably open an incident, but it is almost always worth it... Incidents aren't that expensive, and having people chasing their tails for days really IS that expensive!

    -PatP
    Pat, could you give me the best cache usage setting?

  5. #5
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Other settings to try

    As well as setting the memory available, you could look at the help on:

    Memory
    "minimum query memory"

    Processor
    "boost SQL server priority on windows",
    "maximum worker threads" - reduce if its a high number and not many processes run on you server

    These settings need to be taylored for your particular server.

    You could also consider clearing the transaction logs for the database and rebuilding the indexes.
    Is there plenty of free space on the machine's hard disks and are they defragmented?

  6. #6
    Join Date
    May 2004
    Posts
    7
    Our server is a multi-processor server and is dedicated as a SQL Server machine. It has 6GB of RAM. However, we noticed that the memory usage gets stuck at 1.7GB even if the 'max server memory' is 3GB. I was wondering if setting AWE might help.

    The application is web-based & the conundrum is that whenever a system slow-down occurs, the SQL Server CPU utilization is high.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    It would really help us if you told us what version of SQL Server and Windows you are using and if you are on the latest service pack.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    May 2004
    Posts
    7
    It's a SQL Server 2000 running on Win2K Advanced Server. I'll have to confirm the SPs patched-in but let's assume a basic installation for now.

    Many thanks.

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Is it Enterprise Edition of SQL Server or Standard. That's what I'm trying to get out of you. SQL Server Standard only supports 2gb and will show as 1.7gb. You will need SQL Server Enterprise Edition to use more than this.

    This would have a VERY LARGE affect on your performance.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by redneck
    Pat, could you give me the best cache usage setting?
    Sorry, but cache usage is a measurement, not a setting. It describes how often SQL Server is able to retrieve requested information from disk cache instead of having to do a physical disk read to get that information.

    -PatP

  11. #11
    Join Date
    May 2004
    Posts
    7
    It's an Enterprise Edition SQL Server.

    "select @@version" yields:
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

  12. #12
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to modify your boot.ini file to have the /3gb and /pae switches. You then need to enable awe memory on SQL Server. You then need to set the min and max memory for the server at 5gb.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  13. #13
    Join Date
    May 2004
    Posts
    7
    Any more config options available?

    If it were up to us, we would banish all CURSOR USAGE to the pit, but as it is, the 3rd-party vendor holds the key.

Posting Permissions

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