Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Location
    Port Macquarie Australia
    Posts
    3

    Unanswered: SQL suddenly drops from 50%plus CPU usage to 1% CPU usage and stays there for 20 mins

    The problem occurs periodically for varying lengths of times.
    The SQL server 7 server handles a lot of transaction from web usage and some VB apps on another server so the computer has to only handle SQL server 7. When the problem starts it knocks most transactions out with timeouts etc only allowing some smaller ones through. The web site continues to work on the pages that don't do much but is very slow as the SQL server will not function above 1-5% CPU. This has been going on for over a week. The slow downs occur across all databases and all tables and last for up to an hour or more but generally 30 minutes or so.

    Cannot seem to find any particular app as being the source of the problem and believe it has something to do with Server 7 itself.

    Have difficulty defining the problem in few words to run Google searches as well.

    The problem site has members only areas and online games so the pages have a few SQL statements each.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: SQL suddenly drops from 50%plus CPU usage to 1% CPU usage and stays there for 20 mins

    The issue might be as simple as some other process periodically "hogging" all system resources e.g. (an inefficient virus scanning process), or it may be far more complex and subtle.

    ==> You may want to gather more useful data. for example;

    qualitatively: Try finding out what may have changed in the environment in general. Has this always been so, about when did this start, what was changed around the time the symptom first appeared, what else runs on the machine, etc.?

    quantitatively: Try gathering some more specific detailed performance data, as well. Your qualitative investigation may (or may not) suggest some specifics to measure. In either case, try configuring performance monitor to understand what is or is not going on at the time (use intervals short enough to capture many points durring the time the issue occurs). At a minimum, monitor some relevant CPU counters (total CPU usage, Sql Server, etc.). You may also want to monitor access, disk IO, and some other basic counters to make sure you are not completly overlooking some larger issue(s).

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    You have said that the sql process runs at 50% cpu usage - not another process - correct ? If so, try profiler and use the filter tab and select cpu and use "greater than or equal to" to specify a threshhold value.

  4. #4
    Join Date
    Nov 2002
    Posts
    14

    Re: SQL suddenly drops from 50%plus CPU usage to 1% CPU usage and stays there for 20 mins

    Most likely a client connection is holding locked resources. Then SQL Server is waiting instead of working. That's why the CPU goes down.

    I assume you use ASP. If a request that takes too long to complete, IIS returns a Time Out error back to the browser but the request processing context remains. Now it is orphan and it keeps holding its resources.

    The only solution I know is to recycle the process of that web site. If it is running InProcess, you have to recycle the whole IIS, otherwise only the DllHost.exe for the web site. Sometimes even this doesn't work properly.
    Zlatko Michailov
    Z-SQL

  5. #5
    Join Date
    Nov 2002
    Posts
    14

    Re: SQL suddenly drops from 50%plus CPU usage to 1% CPU usage and stays there for 20 mins

    Forgot something. To see who has locked what, call this:

    exec sp_who2 -- or sp_who
    exec sp_lock
    Zlatko Michailov
    Z-SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Port Macquarie Australia
    Posts
    3

    thanks for your assistance

    Thanks people for responding.

    Yes the server runs ms SQL 7.0 and isn't used for anything else.

    In fact even doing that with two processors it does often get well above 50% CPU usage so when it drops down to 1% the web pages do get very sluggish and many timeouts.

    I tried sp_who and sp_who2 and got a 100 entries most of which were sleeping or runnable some background tasks from Master.

    Then tried sp_lock got a list of about 25 mostly mode shared some one Intent exclusive and about five Scheme Stability.

    Once it freed up however, I ran sp_lock again i had similar list of locks and about double again exclusive locks and several intent exclusive locks.

    The sp_who listed mosty sleeping some background and only 1 or 2 runnable however.

  7. #7
    Join Date
    Nov 2002
    Location
    Port Macquarie Australia
    Posts
    3

    Re: SQL suddenly drops from 50%plus CPU usage to 1% CPU usage and stays there for 20 mins

    Your right about IIS and ASP so if that is the problem, I now have to come up with a solution.

    Not quite comprehending your meaning with the recycling statement if you could clarify I would be most gratefull.

    Thanks everyone.

  8. #8
    Join Date
    Nov 2002
    Posts
    14

    Re: SQL suddenly drops from 50%plus CPU usage to 1% CPU usage and stays there for 20 mins

    Use the SPID column to match the rows from sp_who2 and sp_lock. See if one of the SPIDs that is holding resources is Internet Information Services.

    To recycle a web site you have to KILL the process. Kill comes with the Windows Resource Kit. For IIS 5 (Windows 2000) there is a tool called iis5recycle.exe that couls be set up to recycle web sites. However, the architecture of IIS 5 does not allow successful recycling every time.
    Zlatko Michailov
    Z-SQL

Posting Permissions

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