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