Package stored in MSDB runs slower while executing in DEBUG from SSDT
Customer had opened a case with Microsoft Customer Service and Support (CSS) SQL Support to find why Package stored in MSDB runs slower while executing in DEBUG mode from SSDT comparatively to the package stored on the file system.
For a specific customer scenario, package imports about one million rows. If the package is stored on the file system, Package execution from SSDT in DEBUG mode finishes importing all million rows in about 2 minutes. if the package is stored in MSDB, package execution from SSDT in DEBUG takes about 20-30 minutes to import same amount of the source data .
I enabled package log for all the tasks and events and collected one memory dumps for every 1-2 minutes for the processes :
DEVENV.EXE
DTSDEBUGHOST.EXE - 64 bit
DTSDEBUGHOST.EXE - 32 bit
Package logs showed a pattern that "OnPipelineRowSent" is always 5 seconds apart indicating delay in processing the buffers of the data .
As per the package log below , 4748 rows per each buffer,
User:BufferSizeTuning,SGAJJE010430VM,SGAJJE010430VM\Administrator,Data Flow Task 1,{F2030480-53DF-409F-81A9-0BE8B26E9FE7},{DA5A9C54-F935-4821-B9BB-F0ACD107AD40},2/21/2014 9:58:59 AM,2/21/2014 9:58:59 AM,0,0x,Rows in buffer type 0 would cause a buffer size greater than the configured maximum. There will be only 4748 rows in buffers of this type.
So, for 1 million rows about 210 data flow pipeline buffers and 5 seconds delay between processing each buffer.
Hence total delayed time for importing all the 210 buffers that has 1 million rows is 5 *210 = 1053 seconds = 17.5 seconds
The total delay time (17.5 seconds) + Time for importing the 1 million rows ( about 2-3 minutes) = ~20 minutes, which the total time to finish the package execution.
The memory dumps showed that DTSDEBUGHOST.EXE raised an RPC event to send to the debugger window over RPC
The RPC listener thread which is handling the custom event in DtsDebuggerHOST.exe window waiting for with for predefined time 5 seconds. This is the 5 seconds delay that was logged in the package log.
SSIS Product team confirmed the slowness is due to a bug and a fix will be released in the future versions of the SSDT that can be downloaded from public facing portal and the version that ships with SQL 2012 Product .
Until a hot fix is released, The alternate solution is to store the package on the file system to resolve the performance problem while executing in DEBUG MODE from SSDT.