4

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

Improve this question
7
  • 1
    Go here, get sp_BlitzCache, and run EXEC sp_BlitzCache @StoredProcName = 'YourProcName', then post the plan XML here so we can help.
    – Erik Reasonable Rates Darling
    Commented Dec 6, 2017 at 12:43
  • How much memory does it ask for? Does the amount change based on parameters?
    – Joe Obbish
    Commented Dec 7, 2017 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
    Commented Dec 7, 2017 at 5:44
  • @sp_BlitzErik I have updated my main post with the query plan
    – Swifty
    Commented Dec 7, 2017 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
    Commented Dec 7, 2017 at 12:59

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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