Friday, November 30, 2012 6:19 AM
It will be helpful if i can get a clear chart on the Cache mode sizes of Lookup transformation in SSIS 2008.
And also, at what situation and size , we can use each mode(full cache, Partial or No cache).
Please reply/ any referenced url will be helpful.
Friday, November 30, 2012 7:37 AM
Full cache is everything that is in the referenced table. So make sure you only extract columns you actually need (don't select the table from the dropdown box, use a query instead). If there isn't enough room in memory, it will spill to disk and your performance will go subzero.
Partial cache only selects an amount of the referenced table. Again, make sure you keep this as small as possible.
Here are the options min and max cache for. If a row is in the cache, you get a hit and SSIS moves on to the next row. If a row can't be found in the cache, SSIS will fetch it from the database (you can re-write the query if you want in the advanced tab) and put it in the cache. If the cache is full, another row will be kicked out. The key is to have as many cache hits. If you have a lot of cache misses, performance will go down as SSIS has to fetch every miss individually from the database.
No caching simply caches the last row in memory. If you have a lot of rows that search for the same value in the same order, performance can be acceptable. If not, you'll get a lot of individual select statements agains your database.
- Proposed As Answer by SSISJoostMicrosoft Community Contributor, Moderator Friday, November 30, 2012 8:13 AM
Friday, November 30, 2012 7:38 AMModeratorThis could be a start http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx It describes the differences between those three modes.
Friday, November 30, 2012 8:53 AM
Thanks a lot for your reply.
Is there anything like if it is full cache then we can use this much MB max,
If it is partial cache then default is 5 MB its showing in the Advanced tab of Look up transformation.So what it would be the maximum size of MB we can use?
Any ideas ?
Friday, November 30, 2012 8:57 AM
No, full cache will try to load all the data, so there's no maximum.
Partial cache is exactly build for that requirement: to specify a maximum.
The maximum size is something you need to calculate yourself, we cannot do that without knowing your data and your environment.
I would take it sufficiently large, but without consuming too much RAM.
ps: I just checked, there's no minimum value :)
Friday, November 30, 2012 9:32 AM
Thanks a lot for your reply..
I doesnt have any requirement...I was just asking to know if there is any max size limit for the cache modes(full and partial)...
like if default is 5 MB for partial cache, then whats the max?
Sorry if my question is wrong:)
Friday, November 30, 2012 10:07 AMThe partial cache has only one setting and that's the max value, which is default 25MB. (Actually, there are two, one for 32-bit and one for 64-bit).
- Marked As Answer by Manju_2012 Friday, November 30, 2012 11:34 AM
Friday, November 30, 2012 11:35 AM
Thanks a lot!..
This is what i expect:)