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.

number of sort buffers – affects parallel sorts only from the create index, and update statistics commands:
- The buffers are sourced from single-page default or named data caches.
- SAP ASE must have a sufficient number of free procedure cache buffers available for storing the metadata per row of data being sorted, otherwise it may abort the query processing.
- The SAP ASE sort manager verifies the amount of procedure cache space required with this formula:
  
  (number of sort buffers) X (rows_per_page) X 100
  
  Where rows_per_page is the number of rows of the sorted columns that can fit into a single-page data buffer.
max data buffers per lava sort – affects only regular lava sort or hash operations:
- The buffers are sourced from the single-page pool from the tempdb data cache assigned to the current session.
- The query processor uses the smaller of the following values as the upper limit for buffer space per lava operation:
  - The value of max data buffers per lava operator
  - The product of:
    
    max resource granularity X (the number of cache buffers in the tempdb's single-page pool)

Note: The differences betwen max data buffers per lava sort and number of sort buffers influences the lava operator during optimization. However, the query processor cannot predict the number of available cache buffers at runtime. If the Execute engine cannot find a sufficient number of tempdb single-page cache buffers, it may use system worktable for the sorting or hashing instead.