SSIS Package performance tuning for 15 million + rows

• Regan Galbraith

  SSCertifiable

  Points: 5227

  More actions

  February 28, 2008 at 2:29 pm

  #120555

  The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.

  I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.

  I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.

  I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?

  I see a LOT of the following messages in my progress, when running with default settings:

  [DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

  The design of the data flow at the moment is:

  ..........................................|--target table 1

  SOURCE SP ---- MULTICAST---|--target table 2

  ..........................................|--target table 3

  ..........................................|--target table 4

  any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?