Resource Governor Max Memory settings

• Alexp012

  Old Hand

  Points: 369

  More actions

  January 9, 2023 at 9:27 am

  #4135357

  Hi

  we've had a spate of Resource Semaphore issues. They're on large server with 500GB+ RAM. The issues seem to caused (in part) by memory estimates being far larger than the used memory. Ideally you'd redevelop code so it returns fewer rows has better estimates etc.... However I don't think I'll get the dev resources to do that.

  I wondered if anyone has experience of altering the REQUEST_MAX_MEMORY_GRANT_PERCENT.

  ALTER WORKLOAD GROUP [DEFAULT] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10)
  GO
  ALTER RESOURCE GOVERNOR RECONFIGURE;
  GO
  

  With such a lot of RAM in each server I wondered if effectively creating more semaphore queues would resolve the issue. Does anyone have any experience of this? My rational mind says it sounds like a good idea, but my gut disagrees.

  Cheers

  Alex

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  January 10, 2023 at 10:10 am

  #4135853

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.