max buffers per lava operator

max buffers per lava operator 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 max buffers per lava operator for servers with many concurrent users: SAP ASE 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.

• Differences Between number of sort buffers and max data buffers per lava sort
  There are a number of differences between number of sort buffers and max data buffers per lava sort.