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.

Summary Information

Default value

2048

Range of values

500 – 65535

Status

Dynamic

Display level

Comprehensive

Required role

System administrator

Configuration group

Query Tuning

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.

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