Problem with HUGE memory grant

klunky 2015-12-30 13:29:40

Hello, could anyone help me with this query ?

I don't understand why is almost requesting 44GB of memory grant.

Thank you 🙂

SDyckes2 2015-12-30 17:27:05
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.
klunky 2015-12-31 11:58:46
You mean the REPLACE ?
SDyckes2 2016-01-01 03:03:31
I cannot tell what the function is in the anonymized plan provided. If you check the size of the data before and after the COMPUTE SCALAR in the execution plan, you will see that the size was ~3GB before and 40+GB after.
I would try removing the function, get a new estimated plan and see what the difference may be. But I believe the REPLACE may be trying to process every record, row by row.
SQLkiwi 2016-01-02 04:51:49
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 Schema2.Object11:

CREATE (UNIQUE CLUSTERED) INDEX IndexName
ON Schema2.Object11
    (Column1, Column5, Column2);

That should eliminate the Sort. Whether it should be UNIQUE and/or CLUSTERED is something only you can determine at the point. Most tables should have a clustered index of some sort. See what effect adding that index has on the memory grant.

If you can provide DDL for the existing tables and indexes concerned, and an un-anonymized plan and query text, so much the better.