SQL Server 2012 Service Pack 3 – New Query Option

The estimated reading time for this post is 2 minutes

Download-SQL-Server-2012-Service-Pack-1-Cumulative-Update-3-2

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.
Service pack information

To get Service Pack 3 for SQL Server 2012, see the following Knowledge Base article:

How to get the latest service pack for SQL Server 2012
For more information about SQL Server 2012 Service Pack 3 (SP3), see bugs that are fixed in SQL Server 2012 Service Pack 3.

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.

 

Reference: Microsoft Support

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn