The value of max resource granularity configures the maximum percentage of system resources a query can use. As of version 15.0, max resource granularity affects only procedure cache. This parameter is set to 10% by default. However, it 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
