Query memory grant

Question

Query memory grant

sqlguru

SSC-Addicted

Points: 471
More actions
September 23, 2022 at 4:13 pm

#4091276

We have reporting server and thinking of limiting the max percentage granted to each query. So it will use less memory than desired. I know there are some cons with this. Any thoughts?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

sqlguru wrote:

We have reporting server and thinking of limiting the max percentage granted to each query. So it will use less memory than desired. I know there are some cons with this. Any thoughts?

My thought is that totally the wrong thing to do. Stop and think about it... one of the standard recommendations to help queries run a bit faster is to add more memory... and you want to take some away from each query? On a reporting server?

A MUCH better idea is to stop looking at taking shortcuts and, instead, spend the time making the queries faster. Instead of making everyone wait their turn, make it so that no one has to wait for their results when it is their turn and the won't need to wait for turn.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlguru SSC-Addicted Points: 471 More actions · Answer 2

Thanks. I agree with you. But this is read only copy used for reporting. We are looking for other ways to improve performance by adding more memory but also thinking about limiting the max grant per query.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

sqlguru wrote:

Thanks. I agree with you. But this is read only copy used for reporting. We are looking for other ways to improve performance by adding more memory but also thinking about limiting the max grant per query.

I will just say that I think this is a very bad idea. What exactly are you trying to improve here - the rendering of the reports in SSRS? The retrieval of the data for the reports from the source system(s)? The CPU utilization or memory utilization?

If you have SSRS installed on that read-only copy, that could be contributing to the problem. SSRS needs enough memory and CPU to render the reports. So if both the engine and reporting service are on the same host, you have to reduce the amount of memory the engine can utilize.

There are also MAXDOP settings that could be causing issues - which should be addressed prior to trying to limit query memory grants.

And finally, there are ways to reduce the impact on generating the reports by creating reporting tables tailored for your reporting requirements instead of directly from the source database. Refreshing those tables every day/week/month - as needed - with appropriate indexing. Be careful here - you want tables to support reporting, but not to the extent of a single table for one version of one report with all data converted to external values so all the report does is basically a 'SELECT * FROM reporting_table'. Go that route and you end up with a TB database of adhoc tables that were used for a one-time report 3 years ago that no one will delete for <<insert random reason here>>.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlguru SSC-Addicted Points: 471 More actions · Answer 4

This is secondary read only replica which does not have SSRS installed. We use this replica for dedicated to reporting queries something like that. Sorry If I wasn't provide you details earlier.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

sqlguru wrote:

Thanks. I agree with you. But this is read only copy used for reporting. We are looking for other ways to improve performance by adding more memory but also thinking about limiting the max grant per query.

You've missed what I was saying. All limiting the "max grant per query" would do is slow the queries down. It would be like you trying to run a marathon with your mouth taped shut and you could only breath through your nose. Add more memory, fix the queries, see if indexes will make improvements, etc.

As a long passed friend of mine would say, "Sometime ya just gotta do the needy". Like I say, if you always take short cuts, you'll always come up short. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)