Problem with HUGE memory grant

Hello, could anyone help me with this query ?

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

Thank you :)

avatar image By klunky 16 asked Dec 30, 2015 at 01:29 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By SDyckes2 81 answered Dec 30, 2015 at 05:27 PM
more ▼
(comments are locked)
avatar image klunky Dec 31, 2015 at 11:58 AM

You mean the REPLACE ?

avatar image SDyckes2 Jan 01, 2016 at 03:03 AM

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.

10|10000 characters needed characters left

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.

avatar image By SQLkiwi ♦ 6.6k answered Jan 02, 2016 at 04:51 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x166
x109
x41
x26

asked: Dec 30, 2015 at 01:29 PM

Seen: 1765 times

Last Updated: Jan 02, 2016 at 04:51 AM