Sql-server – SQL Server Table Move Stalled

sql serversql-server-2005

I'm running a data migration. The process is moving data from a query on a SQL Server 2005 database to a SQL Server 2008 R2 database single table destination.

The source query is a join between 2 tables. I cannot change the query or the app that is running the data migration. The process is moving around 70 million rows from the source server to the destination.

The process runs very well for 2 hours, moving around 400,000 rows per minute and looks like the total processing time will be about 2 hours. At around 46 million rows the process slows to around 5,000 rows per minute (almost 100 times slower).

Why would this happen?

I've looked at the query plan and I don't see anything that I can tune. I have been seeing CXPACKET waits (using Adam Mechanics sp_WhoIsActive).

(2x: 9078ms) CXPACKET:1, 
(1x: 1078ms) ASYNC_NETWORK_IO).

I changed the MaxDOP of the server to 1 in order to resolve the CXPacket but I haven't seen any change.

Best Answer

Shot in the dark: It means you client has reached the configured data/log file size and is now auto-growing them as a snail pace. Make sure you properly pregrow the data and the log files before your data transfer.

Related Solutions

Sql-server – SQL Server 2005 datacube processing in high transaction system

Perhaps it would be worth looking into SNAPSHOT transaction isolation level to guarantee read consistency (either at the level of individual statements with READ_COMMITTED_SNAPSHOT or a whole transaction with ALLOW_SNAPSHOT_ISOLATION)?

This should allow the reader to have the consistent view of the database while it is transferring data to datacube, even though other transactions might be modifying the same data concurrently. Also note that according to MSDN: "when the cursors are opened under the snapshot isolation level locks are not taken, which can reduce blocking on the server."

Sql-server – SQL Server 2005 – Query optimization for fetching large number of rows from table with 750 million rows

If you are determined to retain your existing table structure, the best indexes will be something like:

create index ix1 on fp_core(hashkey, note_id, timeoffset);
create index ix1 on fp_core_keys(hashkey);

You mention that the hashkeys can be different for the same song, but it sounds like you have some mechanism for detecting similar songs - if possible, add an additional column called 'songid' to your fp_core table and then query on this single value using a WHERE clause.

Best Answer

Related Solutions

Sql-server – SQL Server 2005 datacube processing in high transaction system

Sql-server – SQL Server 2005 – Query optimization for fetching large number of rows from table with 750 million rows

Related Question