New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012

About MIN_GRANT_PERCENT and MAX_GRANT_PERCENT
Service Pack 3 (SP3) adds the following memory grant options to Microsoft SQL Server 2012.

MIN_GRANT_PERCENT

A percentage value that specifies the minimum amount of memory that should be granted to a query. Range is 0.0 to 100.0. Float value is valid.

MAX_GRANT_PERCENT

A percentage value that specifies the maximum amount of memory that can be granted a query. Range is 0.0 to 100.0. Float value is valid.
If the size of this max memory limit is smaller than the required memory to run a query, the required memory is granted to the query.
Resolution
This functionality was fist introduced in the following Service Packs for SQL Server. 
For more information about SQL Server 2012 Service Pack 3 (SP3), see bugs that are fixed in SQL Server 2012 Service Pack 3.

About Service packs for SQL Server

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.

How to determine the version, edition and update level of SQL Server and its components
More information
The percentage value is based on the memory grant that's specified in the resource governor configuration. For example, consider the following scenario:
  • You have a resource pool whose maximum amount of memory is 10 gigabytes (GB).
  • You have a workload group in the resource pool, and the maximum memory grant of the query in the workload group is set to 10 GB * 50% = 5 GB.
  • You execute a query by using the following statement:
    SELECT * FROM Table1 ORDER BY Column1 OPTION (min_grant_percent = 10, max_grant_percent = 50)
In this scenario, the minimum amount of memory that should be granted to the query is 5 GB * 10% = 0.5 GB, and the maximum amount of memory that it can't exceed is 5 GB * 50% = 2.5 GB. If this query obtains 1 GB without these options, it will obtain the same amount because 1 GB belongs to this minimum and maximum range.

The min_grant_percent memory grant option overrides the sp_configure option (minimum memory per query (KB)) regardless of the size.

Note These two new query memory grant options aren't available for index creation or rebuild.

On the server that has X GB memory, the maximum usable memory for the server (Y GB) is less than X GB (typically 90 percent or less). Maximum memory that's granted to per query is (Z GB) Y GB * REQUEST_MAX_MEMORY_GRANT_PERCENT/100.

The following query options (min_grant_percent and max_grant_percent) apply to Z GB:
  • Min_grant_percent is guaranteed to the query.
  • Max_grant_percent is the maximum limit.
For more information about the memory grant, see Understanding SQL Server memory grant.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 3107401 - Last Review: 07/11/2016 17:12:00 - Revision: 2.0

Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard

  • kbqfe kbsurveynew kbexpertiseadvanced kbfix KB3107401
Feedback