SQL Sorting Memory (-SQLTempBuff)

Use SQL Sorting Memory (-SQLTempBuff) to define the amount of memory to use as a data buffer for temporary data operations including:
  • Sorting
  • Aggregation
  • Creation of dynamic indexes
  • Creation of Temp-tables
Beyond the configured buffer size, SQL Server begins using disk storage which increases I/O operations.
Operating system and syntax UNIX / Windows -SQLTempBuff value
Use with Maximum value Minimum value Single-user default Multi-user default
SQL System Dependent 8 1000 1000
value
Size in Kilobytes of memory to use for each SORT operation.

This is a performance-tuning parameter and is not ordinarily required.

The optimal buffer setting depends on the nature of SQL queries being executed. For instance, queries that involve large sort operations, such as sorting millions of rows, may require more memory. This could be due to business requirements or inefficiencies like non-optimized database schema, stale statistics, or poorly written SQL queries. The default buffer size of 1 MB may be insufficient in such cases, leading to frequent disk I/O and negatively impacting query and system performance.

For example,
  • Sorting 10,000 rows with each row sized at 100 bytes may be completed entirely in memory.
  • Sorting the same number of rows with each row sized at 400 bytes may exceed the buffer and spill to disk.
In general, the buffer size should be tuned based on the typical workload to strike a balance:
  • Avoid allocating excessive memory where it is not needed, which could affect other processes.
  • Prevent frequent disk usage due to an undersized buffer.
Currently, monitoring must be done at the OS or process level. Optimization efforts may include:
  • Analyzing query plans for temporary table or dynamic index usage.
  • Estimating the number and size of rows involved in sort operations.

The SQL Sorting Memory (-SQLTempBuff) parameter may be modified while your database is online using PROMON or through the _DbParams VST. This change will only take effect on the primary broker and its newly spawned servers.

Note: Each connection allocates one temporary table buffer.