Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Jun 22 '12 at 16:00

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
add comment

CXPACKET isn't a problem, it never is. It simply means that one parallel thread is waiting for another parallel thread to do something else. By changing the MAXDOP to one you are simply restricting the query to a single thread, usually making the entire process longer.

Is the entire write transaction doing done in a single transaction or is the application doing the writes in batches? If in a single transaction, odds are the slow down that you are seeing is simply the destination server extending the transaction log over and over again and you are simply seeing the slow down as the log file is zero'd out. You need to look on the destination server and see what the wait times are on that size. Odds are the source machine isn't the problem here.

The NETWORK_IO wait type is simply the data being sent over the network faster than the receiving server can accept it.

share|improve this answer
add comment

ASYNC_NETWORK_IO means the server has completed the query, and is waiting on the client. This could be due to network latency, client-side processing, etc.

A few thoughts:

  1. How close are the two servers in network proximity - hopefully only a few hops away at gigabit speeds.
  2. Are there any indexes on the target table? if so, disable them during the load.
  3. Are you doing this as a minimally-logged operation?
share|improve this answer
    
1. The are in the same room. Should only be about 2 or three hops. 2. there are about 10 indexes on the target table. I'm going to disable them. Thanks for the suggestion. 3. The database recovery model is set to simple. The insert statement is using BULK (I didn't know you could do that). –  user1102742 Jun 22 '12 at 15:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.