Problem with HUGE memory grant
I don't understand why is almost requesting 44GB of memory grant.
Thank you 🙂
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.