Query memory grant

  • 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?

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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

  • 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.

  • 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)
    Intro to Tally Tables and Functions

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

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