Database conversion is very slow. Need suggestions

[PTW]

We are upgrading one of our apps, and part of that process is a conversion of the database (on SQL 2000 SP3). The vendor provided us with a tool to do the conversion that reads from the old database and writes into a new database. Both databases are on the same physical SQL Server box, and both on the same hard drive (the server has 2 mirrored SCSI drives and 1 controller). The client actually doing the conversion is on the same network with fast network connection. Our database is 3.5GB in size, and the largest table contain 5.5 million records.

Only about 100,000 records/hr are being written into the new database, and so whole process is taking days. On the dedicated SQL Server:
-CPU usage is low
-Memory (1 GB) appears to be maxed-out, but it is not paging to the hard drive much. We originally tried with 512 MB and upgrading to 1024 MB did not speed up the process at all.
-Hard disk queue is spiking up and down from 100 to almost nothing, which I would think is consistent with intermittent writing
-Network usuage is active, but not even close to being overloaded

On the client computer actually doing the conversion:
-CPU (Xeon 2.8 GHz) is constantly at 50-55%
-Only about 256MB of the 512MB of memoery is being used
-Hard drive does not appear that busy

Any idea where the bottleneck is? Neither CPU is maxing out. The network connection is not being overloaded. RAM on the client is not maxing out, and the SQL box does not appear to be paging to disk due to lack of RAM.

Could the read/writes being done to the same hard disk be the problem? Is there anything in SQL I should look at that could be seriously slowing it down? Or is writing 100,000 records/hr reasonable performance?

Thanks. We are a bit exasperated here and not getting much help from our vendor. We need to upgrade over a weekend but it takes 3 days to convert the database alone.

 

[MDXer]

The read writes will probably turn out to be the culprit. Run performance monitor and look at the physical disk counters you want to pay attention to your Disk ques and avg disk ques. if the avg disk que is greater than 4 then your drive config is the issue. If this is the case there is only two ways to fix it. Reduce IO which if your doing a copy you can't do. Or increase Avail IO which you can only do by adding more HDs.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

How is the database conversion being done? Is it a SQL Script or is it an application?

Try running profiller and see what the system is actually doing. That will tell you how long the inserts are actually taking.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[PTW]

It is an application, and so I cannot see what is actually being done.

We are building another server to try reading from one and then writing to another. Hopefully it works!

 

[mrdenny]

That's why I recommeded running profiler. That will let you see the actual SQL commands, how fast they are being executed, and how long they are taking to execute.

My guess would be that the migration app isn't very effecent.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief