We are using Visual Studio 2008, SQL 2008R2 (10.50.4000) Enterprise, Windows Server 2008 R2 SP1, 64 bit, 24 cores.
We have 128G RAM.
While loading our data warehouse we have SQL Server Max Memory set at 20480 (20G), leaving essentially 108G to the operating system and DTExec.
Perfmon (Available MBytes) and the log outputs show that during the load we still have upwards of 10G available memory.
Getting to my question...During the "Pre-Execute" phase of the data flow we have numerous Lookup components set with Full Cache. During the Pre-Execute phase these Lookups are being cached, as can be seen in the package Progress tab. One of these lookups caches 210 million rows, which takes between 8-10 minutes. After the 210 million rows are cached, there is a delay of about 1 hour before the Pre-Execute completes, this aforementioned Lookup being the final Lookup component in the data flow.
My question: Why the huge delay after the 210 million rows are cached and the Pre-Execute phase completes?