On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get
CXCONSUMER wait (due to parallel execution) and
So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.
I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.
The queries which are not optimized are LINQ queries and changing that will require some time for the team.
So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?
I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.