Question
Based on the idea presented here, I've created a QueuedDataReader which wraps a ConcurrentQueue in an IDataReader so I can pass it to SqlBulkCopy and 'stream' data into the database.
It works ok, and pretty fast, but the log file grows quite dramatically, even though I believe I've implemented everything suggested here, here (and numerous other places).
Two possibly important details are:
- I'm inserting into a dozen tables in parallel (that is, one
Taskper table) - The tables have
IDENTITYcolumns (which SqlBulkCopy is not inserting into), so I don't think the 'SORT' provisos are relevant.
In addition to that:
- The tables are empty when I start inserting.
- I've tried both CLUSTERED and NONCLUSTERED indexes on the PRIMARY KEY, not much observable difference.
- I'm using
SqlBulkCopyOptions.TableLock - I've tried with and without UseInternalTransactions, and various BatchSizes.
- The database is in SIMPLE recovery mode
- The tables do have FK constraints, but I've tried disabling/re-enabling them and it doesn't stop the log file growing (during insertion)
The question:
- What are some things I could look at to try and troubleshoot what could be causing the log file growth?
Popular Answer
Whilst writing my question, I discovered another excellent resource here, which pointed to BatchSize as a potential culprit.
The results are quite surprising. If we use the BatchSize parameter, performance get worse and worse as we set it to lower values.
Using a very small value for the batch size the network bandwidth stays well under 20% of usage for the whole process (please note that the 500 batch size graph upper limit is 25%, which is different from the other graphs). Any value below 10.000 decreases performance in a very heavy way, leading to very poor time and a heavy log file usage.
When we reach 10.000 of Batch Size, then the difference in time among the various tests becomes very small. But, since we have 8 threads writing 750.000 rows each, then we only have 75 chunks of data sent from each thread. Needless to say, the best performance is obtained when we use 0 as BatchSize, sending the whole 750.000 rows in a single batch.
It might be interesting to discover if there is any benefit, apart from performance, by using the BatchSize parameter. We did not find anything mentioned in the Books On Line nor have we ever seen anything interesting during our experience, this lead us to say that the best thing to do with BatchSize is to leave it to zero, which is its default value, since any value different from that will decrease the performance of the load process.
I had previously abandoned BatchSize = 0 because of timeout errors, but by setting my BulkCopyTimeout to zero, I get some log file growth, but far less than previously.
UPDATE. After messing around for too long doing manual tests, I finally knuckled down to writing an automated test suite, trying variations of BatchSize(0-5000), enqueuing rates, and clustered/nonclustered indexes. (In my case, I'm writing data to a dozen tables in parallel).
This is what I've found:
- Tests of 200000 inserts: If primary keys are
CLUSTEREDor BatchSize is zero, I get no log file growth. - Tests of 1 million inserts: even BatchSize=0 has the log file growing (though considerably less than for other BatchSizes).
CLUSTEREDstill prevents log growth, whatever the BatchSize. - Outside of those conditions, everything depends on insertion speed. As soon as I 'push' too hard, the log file starts growing.
Note, my tests write data at a given rate into a queue wrapped in an IDataReader, which is dequeued by BulkInsert. One queue/BulkInsert for each table. Next step is to try with bigger data sets.