Results 1 to 6 of 6

Thread: SQL 100% usage.

  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: SQL 100% usage.

    I have a SQL 2000 Server, dual processer with hyperthreading. 2Gb of Ram. The machine is only being used as a SQL Server

    Normally the server runs at about 15-20% usage. I have now noticed a problem where the SQL server suddenly jumps to 100%, this can happen after a week or a couple of days. It requires a server reboot to fix the problem. Stopping the SQL server and restarting will not work.

    I don't know if this is part of the problem, but I have noticed that after a day or so the processor usage climbs by about 10%, if I stop the SQL service and restart, the processor usage drop by the 10%.

    Also I have set the memory usage to be 1.5Gb, but it takes a day or two for the SQL server to consume this amount of memory. Don't know if this has anything to do with it.

    Anyone know why this is happening ?

    Kev

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since you can stop SQL Server and have the usage continue, I would suspect that the problem lies elsewhere!

    When you check the TaskManager on the server while the CPU is at 100%, what are the top three tasks (sorted by CPU usage)? I suspect that one of them is most likely your culpret.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    4
    Its the SQLservr.exe that is using the 100% usage after stopping and re-starting the sqlrever. 99% of the time the server is fine, using very little processor time / memory etc.

    It requires a reboot to put the SQL server back to a normal level.




    Originally posted by Pat Phelan
    Since you can stop SQL Server and have the usage continue, I would suspect that the problem lies elsewhere!

    When you check the TaskManager on the server while the CPU is at 100%, what are the top three tasks (sorted by CPU usage)? I suspect that one of them is most likely your culpret.

    -PatP

  4. #4
    Join Date
    Apr 2004
    Posts
    4
    Being doing a bit of work on this.

    I ran profiler after the last problem and I had the same issue again. It seems three particular queries may be causing the problem. All three queries are fairly simple, although they do query a large amount of rows .

    I've taken a sample of three reading before and after the problem. All other queries seem to be ok. But the website is inaccessable when the problem manifests.

    I was looking at the profiler and heres what I found on the querys before the problem

    Q1 - Query used often

    CPU Reads Duration
    62 3645 63
    140 3645 190
    209 3645 140


    Q2 - Medium used Query

    CPU Reads Duration
    1751 31988 513
    1795 32000 546
    1845 31983 500

    Q3 - Rarerly used Query

    CPU Reads Duration
    844 24958 296
    703 24306 250
    734 31883 266


    After Results - This is about 2 minutes after the profiler results above

    Q1 - Query used often

    CPU Reads Duration
    1547 3645 5593
    1500 3645 5640
    1406 3645 4500


    Q2 - Medium used Query

    CPU Reads Duration
    16718 33038 27080
    16297 33038 30483
    16547 33038 53360

    Q3 - Rarerly used Query

    CPU Reads Duration
    7000 24628 21593
    7078 24958 22593
    6657 24965 17873


    I can get rid of Query 2 from the website as its something I added last night (and I've had 2 occurances of the problem today). But it still leaves me with the problemof what is happening, as I've had 2 previous problems (approx 1 week apart) prior to query 2.

    Don't know if this has any revelance but I stopped the sql server and did a reboot, the SQL server went straight back up to 100%. I then did another reboot (without stopping SQL) and the SQL server went back to normal when the machine went back up.

    Any ideas are would be appreciated.

    Kev

  5. #5
    Join Date
    Apr 2004
    Posts
    4
    Sorry, I meant to say when I stop the SQL Server, usage drop to 0% but as soon as I restart the SQL service it goes straight back to 100% usage. It takes a reboot of the server to bring SQL back to a normal level. i.e. stopping and starting the service does not fix the problem of usage.

    Originally posted by Pat Phelan
    Since you can stop SQL Server and have the usage continue, I would suspect that the problem lies elsewhere!

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    do you have any "group by", "order by", or "distinct" clauses in these queries?
    are you using an "OR" operator?

    we are going to have to see the query texts as well

Posting Permissions

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