Results 1 to 5 of 5

Thread: SQL 7 dogged

  1. #1
    Join Date
    Feb 2005

    Red face Unanswered: SQL 7 dogged

    I have a SQL 7 DB on a Dell 6450 with 3.5GB Ram running Win NT. The problem is that the DB is only about 260MB and with about 100 web users it is pegging the CPU to a steady stream over 90%. This is killing the return time. I have eliminated some queries in the software there were no longer necessary and cleaned up some code that was old and needed it. I still have a lot to read but was looking for something to help with the CPU. I have indexed and keyed what I can and it helped some but it still stays over 90% about 1/2 the day.

    Any suggestions would greatly be appreciated.

    We have plans to move to SQL 2000 on a Dell 2850 with 4GB ram running Windows 2003, but it isn't soon enough, testing is not complete. Not sure if I'm not taking trouble with me moving the DB to the newer machine.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    Break out Profiler, and see if your server is receiving a lot of little queries, or a few big ones. You may have one query run on each page that is moderately expensive, so reading the output will take a little bit of patience.

    You may have already done this as part of the index tuning wizard. If so, let us know.

  3. #3
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    I would run a trace on queries with execution times over 5 seconds. Start with your longest running queries and work down. Examine their execution plans. If you have done all of the tuning you can do, it may be time to revisit your database design.

    Things to think about:
    1. Are you running reporting and data entry on the same copy of the data? OLTP and decision support systems require different things.

    2. Is your web server and your database on the same machine? Big no no.

    3. If all else fails, throw hardware at it like you plan.

    Testing? Scaredy cat.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2005

    SQL 7 dogged

    Well, if I had my way we would have already moved to the new hardware, but someone else makes those decisions and I just have to go with it. I have been going through the code, it is about 4 years old and needs some tuning, to clean up queries that were inefficient and some that just weren't necessary. Those pages have helped some. There is a scheduled task that takes about a minute and runs about every 8 and I am cleaning up some serious queries in it.

    I am running everything through the QA Execution plan to help reduce things as I go. I have found about 4 runnable processes and am trying to isolate those as well since they must be hogging some of the resources.

    Just was looking for some other guidance trying to reduce the CPU usage.


  5. #5
    Join Date
    Jan 2005
    You're CPU-bound because your entire database fits comfortably in RAM. Your problem is due to lack of indexes. SQL Server is happily scanning away in RAM, chewing up your CPU. Drop your memory below 260M and your high CPU utilization will go away and you'll have disk i/o problems, instead. I doubt hardware will solve your problem. Unless you get a 32-way 32-bit box. If you can afford that for a 260M database, I'd like to do some consulting work for you...

    What happens is that SQL Server hits a threshhold (it is mysterious and a secret handshake and large sums of money are needed to know it) when you insert the nth row into a poorly indexed table and then all hell breaks loose. If you'd stayed at 259.9999 M, this wouldn't be a problem

    Whatever table is showing up in your slow performing queries needs an index - badly. Finally, I'd suggest any query that takes more than 1 second is a poor performer.

Posting Permissions

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