max buffers per lava operator

maximum buffers per lava opeator sets an upper limit for the number of buffers used by Lava operators that perform sorting or hashing (which are “expensive” in terms of processing). Lava operators use buffers from the session’s tempdb data cache pool as a work area for processing rows.

Lava operators often recurse through their input streams. Sorting requires subsequent merge passes until there are enough buffers available to merge all of the remaining runs. Hashing requires subsequent passes to build hash tables on any spilled sets until all of the remaining data can fit into an in-memory hash table. Some queries require less I/O if you increase max buffers per lava operator. This is particularly true for queries that use the HASH DISTINCT, HASH VECTOR AGGREGATE, and HASH UNION operators.

Be careful when you increase the default value of maximum buffers per lava operator for servers with many concurrent users: Adaptive Server may allocate more buffers solely for expensive operators, reducing the number of buffers available for caching user’s tables and other session’s worktables. Use sp_sysmon to analyze tempdb's data caching effectiveness.

maximum buffers per lava operator works with max resource granularity to limit the number of buffers used. The limit is set to the minimum of:

• The value of maximum buffers per lava operator, or,

• (max resource granularity) X (the number of data buffers in tempdb’s pagesize pool)