What is the min and max size of the cache modes in lookup transformation ssis 2008

Question

Hi,

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.

Thanks,

Manju

Answer 1

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.

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 2

This 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.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 3

Hi,

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 ?

Regards,

Manjusha

---

Regards, Manju

Answer 4

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 :)

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 5

Hi,

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:)

Regards,

Manjusha 

---

Regards, Manju

Answer 6

The 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).

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 7

Thanks a lot!..

This is what i expect:)

Regards,

Manju

---

Regards, Manju