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

Question

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

Answer 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:

• Table variables
• Scalar UDFs (like dbo.fn_GetUtcOffset)
• Filtering on expressions (like LocalTime)
• Optional Parameters (col = @parameter or @parameter is null)