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 ?
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...