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
If the the query processor’s seach engine has consumed more than the configured percentage of procedure cache, and if it has found at least one full plan, the search engine times out and uses the current best plan for the query.
If the query processor does not find a full plan before reaching the value for max resource granularity as a percentage of procedure cache, the search engine continues to search until it finds the next full plan. However, if the search engine reaches 50% of the complete procedure cache and finds no plan, it aborts the query compilation to avoid shutting down the server.
