Hello, could anyone help me with this query ?
I don't understand why is almost requesting 44GB of memory grant.
Thank you :)
By klunky 16 asked Dec 30, 2015 at 01:29 PM
It looks like you are having an issue with the Function call in you select. The estimated data size jumps from ~3GB to 40GB at the Compute Scalar portion of the plan. I would start there.
By SDyckes2 81 answered Dec 30, 2015 at 05:27 PM
That memory grant does seem excessive, though there's a limit to the amount of analysis that can be done on an anonymized plan to determine the exact reasons.
Nevertheless, the Sort is the only operator using a significant fraction of the grant, so I would start by looking to eliminate it. Looking at the Order By columns and Output List of the Sort, I would suggest the following index on the heap table
That should eliminate the Sort. Whether it should be
If you can provide DDL for the existing tables and indexes concerned, and an un-anonymized plan and query text, so much the better.
By SQLkiwi ♦ 6.6k answered Jan 02, 2016 at 04:51 AM