Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Exclamation Unanswered: sql service at 100% cpu most of the time

    Hi,
    I have a clustered SQL 2000.
    for some reason, and I can't find why, the SQL service is very busy, mostly at 100% cpu.
    sometimes when I check on the databases, I see that the files ( data, log), have free space, but when I look with Enterprise manager, view -> taskpad, I see "available space" 0Mb.

    Please advice...

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    To eliminate the remote possibility of the slammer virus, make sure you have the latest service pack installed. To see the actual size run "dbcc updateusage ('dbname')" and see if the size changes
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Hi Ray,
    No virus on the servers ( Norton AV corp , updated ).
    Still 100% cpu.
    any more ideas?

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Norton AV will not stop slammer Ensure you have the latest SP

    Well, here goes:

    How many users are hitting the server?
    What type of locks are being generated?
    ANy reindexing jobs being done?
    What type of maint plan do you have?
    24x7 it is at 100% or certain times?
    What jobs are running?
    What does SQL profiler show you?
    Have you checked pagesplits,deadlocks,blocking,filegrowth?

    Not enough info to give you any meat yet. Reply if you can, or if you want to read some good pages check these out:

    http://www.sql-server-performance.com/q&a2.asp

    http://www.microsoft.com/technet/com...0/tnt1-91.mspx

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

    http://www.sql-server-performance.co...aintenance.asp
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    Thanks for the links.
    I'll be sure to check on them and continue analysys.

    Just for general knowlage :
    This database is used in a voice mail system. It holdes all users profiles as one database, and billing issues as another.

    As a voice mail system, you can understand there is a lot of traffic towards the database, and a lot of Inserts and Updates.

    There is a maintenance plan for full/log backups and Optimization.

    There are also jobs for the system needs as cleaning old enteries and so.

    I'll post more details later after reviewing according to your recomandations.

    Thanks.

  6. #6
    Join Date
    Mar 2004
    Posts
    6

    Exclamation

    Hi again,

    more details :

    The profiler shows regular traffic for our type of system.

    Can not run perfomance monitor on the server cause of CPU usage.

    how can i check pagesplits,deadlocks,blocking?

    The server is like 95% of the time at 100% CPU usage.

    PLEASE HELP.

  7. #7
    Join Date
    Mar 2004
    Location
    New Jersey
    Posts
    9
    do a frame by frame examination of all inbound and outbound packets to the vip using latest version of netmon

  8. #8
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    I thought netops guys were not allowed on this forum! Just kidding
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, I have always had my doubts about Enterprise Manager and its results. 0MB indeed.

    Anyway, what sort of machine are you talking about here? A single 486 would very likely be at 100% cpu with any workload. A quad-Xeon system would be a little harder to push up to that limit.

    Also what is the version of SQL Server? run select @@version in Query Analyzer, and post the output, if you are not sure. Anything lower than 8.00.760 is likely vulnerable to the SQL Slammer virus.

    Third, in Query Analyzer run sp_who2 to see if you have more than two or three processes that all say "RUNNABLE". This can indicate long running queries.

    Lastly, how do you know SQL Server is running this server to the line? Was that taken from Task Manager locally on the system?

  10. #10
    Join Date
    Mar 2004
    Posts
    6
    The servers are cluster package DL380 G2 by Compaq.

    each server has 1 pIII 1.3Ghz processor , 2Gb memory.

    Anyway, by an article I read, If CPU Queue length is larger then 2, then the problem is CPU resources.

    What I saw on other servers we have was avg of 0.5 queue length.

    On those specific servers, I have 18 !!! avg queue length.

    So I gess it's a CPU problem ( need to add one ).

    Thanks for now.
    I'll post the result of adding a CPU.

  11. #11
    Join Date
    Mar 2004
    Posts
    6

    conclusion

    Hi all,
    Adding the CPU just reduced the problem.
    It was a code error which created A LOT of inserts to the database.

    Thanks for all help.

Posting Permissions

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