0

We have a bulk import task running in SQL Server 2016 which imports more than 5 million rows. The task itself is configured at cloud using third party services. The strange thing is that the new data being imported is appended to the data file (.mdf) and already allocated empty space is not being used. Tables are being truncated and reloaded. All the tables have clustered indexes defined. We have even used pre-processing task of truncate all related tables -> reorganize all related tables -> DBCC updateusage but to no avail. We have even double the size of the data file but we still see some auto growth events. Although such events decreased by 80 percent. After the import we are left with 70 percent of data file being empty. Am I missing something or any specific setting/change in SQL Server 2016 which is why we are experiencing such behavior? Thanks.

  • What is the batch size of the bulk insert? Small batch sizes can result in a significant increase in unused reserved space due to a full extent allocation for each batch per this Tiger Team post. That said, I would expect existing unallocated extents (not to be confused with unused space) to be reused before growing the file. – Dan Guzman Jul 1 at 10:51
  • Thanks Dan for the response. The batch size I can see is around 200 rows (too small but that is third party so I would have to check if that can be changed). I am expecting the same that unallocated extents should be reused first. That is why we used a pre-task to re-organize all the tables after truncate, – NOOK Jul 1 at 11:51
  • BTW, what is your recommendation? Should we try trace flag 692 for testing? – NOOK Jul 1 at 12:00
  • I don't see a risk testing with the trace flag. Also, be aware that extent deallocations for large tables are asynchronous. So if you truncate and then immediately load, the original extents may not get reused, although think the pre-process you've tried would rule that out. – Dan Guzman Jul 1 at 12:07
  • Sorry for the late response. With trace flag enabled, the problem is fixed but at expense of some performance loss. Thanks Dan! Can you change your comment to an answer, so I can accept it. Cheers. – NOOK Jul 15 at 11:25
0

Bulk inserts with small batch sizes can result in a significant increase in unused reserved space due to a full extent allocation for each batch for fast inserts per this Tiger Team post. I see from your comment that the batch size is only 200 rows and this explains the main reason for the large amount of unused space, although I would expect existing unallocated extents (not to be confused with unused space) to be reused before growing the file.

If you don't have control over the optimal batch size (e.g. third-party application), you can turn on trace flag 692 to disable fast inserts and reduce space needs but at the expense of performance.

  • Sorry I can't upvote the answer but marked it as accepted though – NOOK Jul 18 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.