Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Unanswered: 100% CPU, AWE enabled SQL Server 200 Server

    Hi Guys,

    I have a SQL Server 2000 SP4 with 300 concurrent users, my Server has 12 GB RAM and 4 CPUS (Xeon 3.20 Ghz)

    I've been strugglin with this server using 100% CPU, if i check on task manager how much memory is taking sqlserver.exe stays at 100-200 MB, AWE is enabled on SQL Server and boot.ini has the /3GB /PAE flags, this are the pictures from task manager regarding memory and CPU:

    http://img114.imageshack.us/img114/5101/100cpuna9.jpg
    http://img444.imageshack.us/img444/1319/memorymx1.jpg

    I want to measure how much memory is taking and Perfmon show zeros (0) on every counter under MSSQL:memory (Connection Memory,Granted Workspace Memory , etc etc etc).

    How can a i measure this?

    The users are complaining about performance .

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    We had some simular problems. It turned out that one process in SQL Server was eating the CPU because it was doing parallel processing using all CPU's.

    The proces was long running, but nescessary. What we did was minimize the impact by allowing only one CPU per process (thus effectively disable parallel processing) in SQL Server. This way the process could still be run but other users could use the other CPU's.

  3. #3
    Join Date
    Jul 2004
    Posts
    12
    I have parallel processing, i will disable it and see what happens.

    What about memory?

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    According to your specs SQL Server (or any other process) is allowed a maximum of 3GB (the /3GB parameter in the boot.ini) of memory and it's only using a small percentage so that should't be a problem (unless ofcourse SQL Server is configured to use less than that).

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There was a hotfix for SQL Server SP4 for machines that used AWE. Have you applied that fix? (http://support.microsoft.com/kb/899761/). Looks like there are a number of hotfixes out for SP4, but those should be tested thoroughly before application.

  6. #6
    Join Date
    Jul 2004
    Posts
    12
    Quote Originally Posted by MCrowley
    There was a hotfix for SQL Server SP4 for machines that used AWE. Have you applied that fix? (http://support.microsoft.com/kb/899761/). Looks like there are a number of hotfixes out for SP4, but those should be tested thoroughly before application.
    Yes , i applied that KB.

    Since i disable parellism i'm getting better performance but i still have peeks (10 minutes running 100% CPU).

    I guess i gotta upgrade my HW

  7. #7
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by razormty
    I guess i gotta upgrade my HW
    Or tune the queries that are running! If you still hit 100% there are multiple processes doing heavy stuff (maybe the same process started by multiple users?).

Posting Permissions

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