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

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.

up vote 5 down vote accepted

This is the opposite of what you should do.

When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.

Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.

  • So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests. – user9516827 Oct 23 at 14:58
  • @user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query. – sp_BlitzErik Oct 23 at 15:02
  • @user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query. – sp_BlitzErik Oct 23 at 15:14

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.