I would like to know what happens when a very large reference data set for a lookup transform with full caching enabled is getting loaded during package execution and the computer memory runs out or is very low.
Does SSIS
a) give an out of memory error of some sort
b) resort to a no caching or partial caching mode
c) maintain the full caching mode but will switch to using the paging file(virtual memory).
I think it will resort to using the page file in which case the benefits of in memory lookups are lost and performance would suffer. If I cannot upgrade the memory or shrink the reference set somehow, i should switch that lookup task to use partial caching or no caching with an indexed lookup table. Would this make sense?
It won't do B unless you explicitly set it. I think it does C as part of the standard Windows memory management, but you might see A too if you are dealing with large data sets.
Your approach would make sense. I'd start by making sure that you are only caching the bare minimum reference set needed, though.
No comments:
Post a Comment