SQL Server 2012 Service Pack 3 – New Query Option

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:
[codesyntax lang=”tsql”]

SELECT * FROM Table1 ORDER BY Column1 OPTION (min_grant_percent = 10, max_grant_percent = 50)

[/codesyntax]
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

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

Leave a Reply

avatar
  Subscribe  
Notify of

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Leave a Reply

avatar
  Subscribe  
Notify of

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan