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

disconnect a user whoz locking the DB with diff SPIDs

I have issued a simple insert statement on table and the statement could not insert a single row into the table.When i check the locks on the tables i found that almost 5000(five thousand) locks on the table for a single user with different SPID(s).
The user is and sql server user(not a windows user) and used to connect to the application.I wanted to disconnect the user from the DB so that all the locks will be freed.But i dunno how to disconnect a user from the DB. I know that i can issue KILL command to Terminate a user process based on the system process ID,but here the same user has nearly 1000 SPIDs.I thought that it would be very big job to kill each and every process and restared the sql server.but i guess its not the correct process to do.
how can i dosconnect a specific user from the DB.

Thanks.Well if you think it's a big job to kill 1,000 spids, how big of a job is it to create them?

I would want to know what process is soing that.

Also, watch the ROLLBACK...it'll be twice as long as letting the work COMMIT

But you've got bigger problems...

ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Will blow everyone out, rollback all uncommitted work, and give you control

disabling identity with bulk insert

hi,
can identity be disabled with the bulk insert operation.
if so then how is it to be done?
thnks
anu
use WITH KEEPIDENTITY as an option if usign BULK INSERT, -E is using bcp.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:237F7136-AEE5-440F-8495-BFEB475F477F@.microsoft.com...
> hi,
> can identity be disabled with the bulk insert operation.
> if so then how is it to be done?
> thnks
> anu

disabling identity with bulk insert

hi,
can identity be disabled with the bulk insert operation.
if so then how is it to be done?
thnks
anuuse WITH KEEPIDENTITY as an option if usign BULK INSERT, -E is using bcp.
--
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:237F7136-AEE5-440F-8495-BFEB475F477F@.microsoft.com...
> hi,
> can identity be disabled with the bulk insert operation.
> if so then how is it to be done?
> thnks
> anu

disabling identity with bulk insert

hi,
can identity be disabled with the bulk insert operation.
if so then how is it to be done?
thnks
anuuse WITH KEEPIDENTITY as an option if usign BULK INSERT, -E is using bcp.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:237F7136-AEE5-440F-8495-BFEB475F477F@.microsoft.com...
> hi,
> can identity be disabled with the bulk insert operation.
> if so then how is it to be done?
> thnks
> anu