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

    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 products user to have a lower priority than other 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?)

    Thx.

    David

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can specify batch size for your ETL processes which will give the server some breathing time between shorter transactions.

    You can also recommend your .Net programmers to lower transaction isolation level on their SELECTs.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2004
    Posts
    2

    Batch size is 10

    Sorry about the double post, but dbForums timed out on the first one and I thought it did not go through.

    I use the same batch size I used on Oracle (I am using JDBC), 10. On my test box when doing batch inserts or updates it seems to manage about 1500 per second.

    I'll let the guys on the front end to try lowering the transaction isolation level.

    Thx.

    David

Posting Permissions

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