The value of max resource granularity configures the maximum percentage of system resources a query can use. In Adaptive Server version 15.0 and later, max resource granularity affects only procedure cache. By default, max resource granularity is 10%. However, this value is not enforced at execution time; it is only a guide for the query optimizer. The query engine can avoid memory-intensive strategies, such as hash-based algorithms, when max resource granularity is set to a low value.
To set max resource granularity to 5%, enter:
sp_configure "max resource granularity", 5
