Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    63

    Wink Unanswered: Disk Time 100% during Insert Data

    Hi ,
    I am using SQL 2000 SP3 2 CPU (Intel II ~1230) with 4 GB RAM .
    The server is data warehouse server that load data and served reports.

    I am loading data every 15 minute with Balk Insert command into temporarty tables and than insert the data to two fact tables with logic implemented by store procedure.

    Each time I am insert new data ( every 15 minutes , for around ~ 4 minute of ~ 250,000 lines) the Disk time is 100% .

    I am using RAID 5 with logical partitions for system ,SQL Data file and SQL transaction log file .

    In order to solve the Disk Time bottleneck ( the CPU is normal during the insert ~ 40 % but still the duration is too long due to the disk time problem) I though to create separate file group to each fact table and change the Disk configuration from RAID 5 to double RAID 1 ,each one for each File group ( and other disks for the transaction log and system ) .I though that in this way I will be able to use each of the physical disks rather than current RAID 5 .

    Any idea ? do you know other option to solve this problem ? what is the reaon for the fact that the Data file that store on RAID 5 do not use the 5 physical Disk headers ?

    Thanks in advance
    Eyal

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You didn't mention the "C" word...but I imagine it's in play...where's the data coming from? INSERTS are a logged operation....and 250k of rows, every 15 minutes...is a lot of data....

    I would try to figure out a way to perform a nonlogged operation (bulk insert?) and apply the logic against the database...

    My own opinion (MOO)

    What type of data is this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Worth removing any indexes on the load tables & then recreating the indexes once data has been loaded?

    FWIW

    Cheers,

    SG.

  4. #4
    Join Date
    Jul 2002
    Posts
    63
    Hi no one response regard the Disk configuration .This is my best solution to the Disk time - separate the two Fact table to different phiscal disk means working with two disk header in parallel . !!??

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And you mentioned that you're interested in speed...if you're doing insert a row at a time then that'll be slow, if you're using a cursor, that'll be slow, if your modifying data on import, that'll be slow...

    If you want to split the data across attached drives, go ahead, make a patitioned view and go nuts...

    Is that the root cause of your problem?

    Hard for us to tell until the mind reading machine comes back online...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    I agree with Brett 100%...more info required please

    Cheers,

    SG

Posting Permissions

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