Memory Grants part 5: Query hints

Arthur-Daniels, 2019-02-11 (first published: 2019-01-22)

Proceed with caution, if you’re thinking of using query hints. This post will specifically cover min_grant_percent and max_grant_percent.

My opinion on query hints is that you’re often better off tuning the query, than using a query hint. Especially if you have time to consider using a query hint, you should take enough time trying to tune the query first.

Okay, now that we’re done with the disclaimers, what does max_grant_percent do?

Max_grant_percent will set a maximum memory grant for the query. The percent is based on the maximum memory available for a query, formula here.

Without a hint, the formula is:

(Maximum SQL Server memory * 90%) * 20%

Demo time! First, creating the table from Memory Grants part 3.

CREATE TABLE BigMemoryGrant (Id INT IDENTITY(1,1), column1 NVARCHAR(max));
GO
CREATE CLUSTERED INDEX cx_BigMemoryGrant on BigMemoryGrant(Id);
GO
INSERT INTO BigMemoryGrant
SELECT 'A'
FROM sys.messages
INSERT INTO BigMemoryGrant
SELECT 'B'
FROM sys.messages
INSERT INTO BigMemoryGrant
SELECT 'C'
FROM sys.messages

And we’ll use the same query. I’ll run it again for a baseline.

SELECT * FROM BigMemoryGrant
ORDER BY column1
1.82 GB Memory grant with no hint

Add in max_grant_percent.

SELECT * FROM BigMemoryGrant
ORDER BY column1
OPTION(max_grant_percent = 50)
913 MB

Perfect! So the query still desires about 4 GB of memory, but since we applied our hint, the query receives 50% of the maximum memory grant.

Then with the hint, it becomes:

(Maximum SQL Server * 90%) * 20% * (max_grant_percent value)

What about min_grant_percent?

We need a query that gets a small memory grant to test this hint. I’ll re-use the SmallMemoryGrant table from Part 4.

CREATE TABLE SmallMemoryGrant (Id INT IDENTITY(1,1), column1 NVARCHAR(max));
GO
 
CREATE CLUSTERED INDEX cx_SmallMemoryGrant on SmallMemoryGrant(Id);
GO
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'A'
FROM sys.messages
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'B'
FROM sys.messages
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'C'
FROM sys.messages
 
--Run query once to see the memory grant size, get actual execution plan
SELECT * FROM SmallMemoryGrant
ORDER BY column1
With no hint, 1 MB memory grant
SELECT * FROM SmallMemoryGrant
ORDER BY column1
OPTION(min_grant_percent = 50)
With 50 percent min_grant_percent, we get 913 MB memory for this query

Interesting! I haven’t used min_grant_percent, but it has the inverse behavior of max_grant_percent. Both hints are based on the maximum query memory grant formula, but they move the memory grant in different directions.

(Maximum SQL Server memory * 90%) * 20% * (min_grant_percent value)

When are these hints useful?

As stated in the disclaimer, when your query is still getting the wrong memory grants after hours and hours of performance tuning. Once you’ve done that much legwork, and tested multiple parameters, in multiple environments, I could possible use this hint.

In my experience, this hint becomes useful when a query has very inaccurate row estimates. That can mean other performance issues are there, which is why I recommend query tuning so much.

Stay tuned! I hope you’re enjoying this memory grant series.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads