Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004

    Unanswered: How to restrict 100% CPU hog user

    I have an ETL product, every 30 minutes it loads maybe a coupla 100K records of various construction into an SQL Server Db (2000 SP3, on Win 2000 Std Server also Win 2003 Ent Server). It really hammers the Db with a combination of batch insert update and insert fallback to update etc. It does not use 100% CPU actually and is IO bound. Works a treat.

    Now the problem, someone has built a .Net ADO based front end, and for the 5 minutes my product runs every 30 minutes they have a hope in hell of getting a reasonable response for their select queries. Now my take is that maybe I can delay for 5ms after each transaction I do to allow the other user processes a chance. But better maybe would be for my ETL product Db user to have a lower priority than other Db users.

    What is the best way to achieve this end? If I understood more about how SQL Server prioritizes users and their requests it might help. (round robin?)



  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    I read an article a while ago that pointed out an interesting thing about the TPC-C scores for SQL Server. They actually run their tests with "max degree of parallelism" not equal to 0. This means that even if you have a monster query running on processors 1 - 3, processor 4 has a hope of satisfying your query. This is something you would definitely want to try out on test first, as I believe changing this still requires a restart of the service. And too many of those in production anger the users.

    If this works for you let us know. This will come up in a server consolidation project coming up around here.

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    If defaults were taken during setup and no configuration changes were made since, - you can apply the desired degree of parallelism by using OPTION (MAXDOP <number_of_cpus>)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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