What is the actual use of buffer temp and blob tem

2020-08-01 05:17发布

问题:

I have changed the memory setting in the sql server property to a low memory.Also i have changed the buffer temp path to a particular location of my system.But why package is failing with message as insufficient memory?.If we set buffer temp and blob temp,the data should swap to that temp location right?Then if it is failing, what is the use of buffer temp?

回答1:

Somewhat related What is the default file path location for BufferTempStoragePath in SSIS 2005? In particular, read the linked article from bimonkey concerning the accessibility of these locations on disk from the sql agent service account.

Generally speaking, when your package is reporting low memory, it is due to the use of fully blocking transformations and Lookup Tasks pulling back too much data. If your package make heavy use of blocking transformations, try and offload the work to source systems. If lookups are to blame, try being more selective in your query. Do not pull back the entire table, only pull the columns you need. If that isn't selective enough, can you try filtering that dataset with a where clause (I only need current year's data, etc). Failing that, switch the lookup from full cache mode to partial cache or no cache. No cache will result in one-off queries to the source system for every row that comes through. It will have no memory that it ran the exact same query 2 rows ago. Partial cache solves that dilemma by keeping the X MB of data in memory. If you want more details about how to reduce memory usage, post some screenshots of what your package looks like. Also note, settings like BufferTempStoragePath are per data flow so if you have multiple data flows in a package, each one will need to be configured.

The architecture of the dataflow is such that data is read into memory buffers and the address of those buffers are passed to the various tasks. Instead of each task needing however much memory allocated to them to hold the data that's passing through them, they all work off the same shared set of memory. Copying that memory from task to task would be slow and very expensive in terms of memory consumption.

With that preamble said, what are BufferTempStoragePath and BlobTempStoragePath? Anytime you pull large object types (n/varchar(max), xml, image, etc) into the dataflow, that data is not kept in memory buffers like native types. Instead it's written to disk and a pointer to that address is what is put into the memory buffer. BufferTempStoragePath is used when your data flow task still has work to do but you've either

  • fragmented your memory so much (through fully/partially blocking transformations) the engine can't get any more
  • are trying to do too damn many things in a single task. My rule of thumb is that I should be able to trace a line from any transformation in the package to all the sources and destinations. If you've created a package from the import/export wizard, those dataflows are prime candidates for being split out into separate flows as it loves to group unrelated things into a single data flow which makes them memory hungry.
  • the box simply doesn't have sufficient resources to perform for the data. I generally prefer to avoid throwing more hardware at a job but if you've addressed the first two bullets, this would be the last one in my pistol.


标签: ssis