1

How do I go about troubleshooting an SP that times out every once in a while. When you include the actual execution plan, it says "The query had to wait for XXs for memory grant"

This does not happen on all the environments where the SP is running.

I have tried increasing the memory the on one of the machines where this occurs, it had no effect (8gb -> 16gb).

I am aware of the Memory Grant query hints, but this is not an option since the minimum requirement is SQL2012 (with no SP) and the hints were introduced in SP3.

Any guidance would be appreciated.

Query execution plan: https://www.brentozar.com/pastetheplan/?id=S1YAhUUbf

  • 1
    Go here, get sp_BlitzCache, and run EXEC sp_BlitzCache @StoredProcName = 'YourProcName', then post the plan XML here so we can help. – Erik Darling Dec 6 '17 at 12:43
  • How much memory does it ask for? Does the amount change based on parameters? – Joe Obbish Dec 7 '17 at 1:07
  • Here is the offending statement's xml. <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" MemoryGrant="28944" CachedPlanSize="96" CompileTime="11" CompileCPU="11" CompileMemory="1064"> <Warnings> <Wait WaitType="Memory Grant" WaitTime="74" /> </Warnings> <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="45800" RequiredMemory="1536" DesiredMemory="45800" RequestedMemory="28944" GrantWaitTime="73916" GrantedMemory="28944" MaxUsedMemory="112" /> – Swifty Dec 7 '17 at 5:44
  • @sp_BlitzErik I have updated my main post with the query plan – Swifty Dec 7 '17 at 6:19
  • @sp_BlitzErik wow... that firstrepsonderkit is amazing! I was able to resolve my issues with the results from sp_BlitzCache. Please suggest it in an answer so I can mark it as the solution and give you some well deserved cred! Thank you so much! – Swifty Dec 7 '17 at 12:59
0

As sp_BlitzErik suggested, I used sp_Blitzcache to highlight the issues in the offending proc. I was able to address them and the problem went away. I highly recommend having a look at the SQL First Responders Kit. I wish I could give the credit for this answer to to Erik.

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.