SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues

All we need is an easy explanation of the problem, so here it is.

I have a stored procedure that takes too long sometimes even though I have added all suggested indexes.
I keep getting the Query had to wait for memory grant
The query plan can be found here: Query Plan

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

superfund

This query is pretty well a disaster, but if the reason it’s sometimes slow is that it’s waiting on memory (RESOURCE_SEMAPHORE) then the server you’re on is likely underpowered for the workload as a whole.

I see the EstimatedAvailableMemoryGrant="417308" and EstimatedAvailableDegreeOfParallelism="2" which would back that up.

You can artificially reduce the memory grant for the "problem" query by using the MAX_GRANT_PERCENT hint, and finding a value that allows it to run with less memory without spilling enough to cause a performance issue.

Since you’ve only posted the cached/estimated plan, I can’t really tell you exactly where the issue is, but general anti-patterns here:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply