2

We have a website for SolarWinds Orion which utilizes SQL and we've had many reports of slowness issues with the website. I ran sp_Blitz on the SQL box and I'm seeing these errors:

Poison Wait Detected: RESOURCE_SEMAPHORE 98:07:56:03 of this wait have been recorded. This wait often indicates killer performance problems.

Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE 33:06:03:08 of this wait have been recorded. This wait often indicates killer performance problems.

Too Much Free Memory 7GB of free memory inside SQL Server's buffer pool, which is 12GB. You would think lots of free memory would be good, but check out the URL for more information.

The server has been provisioned 16GB of RAM and SQL server is configured to use 13GB of maximum server memory, 1024KB per query. Is this server over provisioned? I feel like its a memory misconfiguration but any assistance or insight would be appreciated.

5

This is not an over-provisioning issue or even a memory misconfiguration.

RESOURCE_SEMAPHORE: "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."

Depending on which version and build you are using of SQL Server, you may be able to query for large memory grants so that you know where to start on which queries are causing a problem. If you are on SQL 2012 SP3 or greater, SQL 2014 SP2 or greater or SQL 2016 RTM or greater, then you can query for this information. See the query at the bottom of New memory grant query hint MIN_GRANT_PERCENT came to rescue.

Check out Query Plans: Memory Grants and High Row Estimates for more information on memory grants, which is the link inside the link sp_Blitz directed you to.

  • Tara, I appreciate the response. I am still fairly new to SQL so bear wit me. I am able to run that query and and see a list of queries by max grant size descending but not sure what to do with this information. This DB is utilized by a web server so how can the queries be optimized in this case without rewriting web code? – Brad Maples Jan 20 '17 at 20:26
  • @BradMaples, the queries likely need to be changed. I've seen a large memory grant when it was the data type of the column in a table that was causing the problem. But usually it's going to be a query problem, I think. – Tara Kizer Jan 20 '17 at 20:33
  • So start from the top of that memory grant query and see what can be done about the queries in the statement_text column. Notice that there is a large discrepancy between max_grant_kb and max_used_grant_kb. – Tara Kizer Jan 20 '17 at 20:36
  • The queries with the large discrepancy between max_grant_kb and max_used_grant_kb are the culprit of the RESOURCE_SEMAPHORE issue. – Tara Kizer Jan 20 '17 at 20:37
  • I have several with large discrepancies but if these queries are generated by the Solar website how can they be optimized/changed? Is this just poor coding on Solar's side? – Brad Maples Jan 20 '17 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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