INSERT takes over 5 hours, when inserting more than specific number of rows

When inserting less then about 1,350,000 rows to the table it all takes about 2 minutes, however when number of inserted rows is bigger, then the time needed to insert data grows to about 5 hours.

The problem is not connected with query or indexes, because everything has been working fine for a long time and nothing has changed in the structure of query, tables or indexes.

Problem appeared for the first time about 2 weeks ago and it appears repeatedly on days, when number of inserted rows is bigger than +-1,350,000. For example on one day number of inserted rows is 1,200,000 and the process takes 2 minutes, on the other day number of rows is 1,450,000 and it takes 5-6 hours to insert data.

I tried to rebuild indexes, but it has not helped.

My guess would be - if you are not actually being blocked - that you are hitting a threshold above which the data file (and/or log file) has to grow, and that your configuration is not optimized to support this growth. Ensure that:

• Your data file growth rate is reasonable, and is a fixed size (not %!) large enough to accommodate this transaction and anything else that may be going on concurrently.

• Same for log file.

• Instant file initialization is enabled. This will help speed up data file growths but not the often more troublesome log file growths.

• You aren't inserting 1.35 million individual rows in a loop or otherwise all as one big transaction. There is something to be said for breaking your transactions into chunks.

StackExchange Database Administrators Q#76021, answer score: 15