No granted memory when running query from entity

Question

Using EntityFrameworkCore, I noticed one of my queries reached a timeout (>30s), so I used debug and copy/pasted the DebugView.Query expression from my IQueryable object to SSMS, and it ran just fine in less than 1s.

I then opened the Activity Monitor in SSMS and re-ran the query from entity to check its execution plan and compare it with the one I got when I ran the query inside SSMS.

I noticed two main differences :

In the StatementText part, the plan with Entity includes the parameters (which looks like (@__start_Value_0 datetime,@__p_1 int,@__p_2 int)) and only this differs from the plan I got with the same query in SSRS.
The MemoryGrantInfo part differs a lot.

This is what I get when I run the query directly in SSMS:

This is what I get when I run the query with Entity:

I understand that speed can directly be related to granted memory, but what I don't understand is why wouldn't SQL Server grant any memory to Entity's queries?

If it is of any use, the query itself is as simple as:

DECLARE @__start_Value_0 datetime = '2020-01-01T00:00:00.000';
DECLARE @__p_1 int = 0;
DECLARE @__p_2 int = 100;

SELECT [l].[A],[l].[B],[l].[C],[l].[D]
FROM [MyView] AS [l]
WHERE [l].[D] > @__start_Value_0
ORDER BY [l].[D] DESC, [l].[A], [l].[B]
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

and if I remove any of the 3 order bys (no matter which one), query works fine with Entity.

Execution plan (with Entity)

Execution plan (from SSMS)

SELECT @@VERSION

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Edit:

I ended up adding an interceptor as suggested by @SvyatoslavDanyliv, but with OPTION(OPTIMIZE FOR UNKNOWN) and query time effectively dropped significantly. I've configured my interceptor for both options in case this one no longer works for my use cases.
However, I am still wondering why there was no GrantedMemory although required and desired memory were non-zero.

No granted memory when running query from entity

Edit:

0 Answers