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