2012年3月29日星期四

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
EyalYou 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?|||Howdy

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

FWIW

Cheers,

SG.|||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 . !!??|||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...|||I agree with Brett 100%...more info required please

Cheers,

SG

没有评论:

发表评论