SQL Server 2016 - excessive memory grant warning on poor performing query

I have a relatively large database of 550GB on a SQL Server 2016 EE instance which has a max memory limit of 112GB of the total 128GB RAM available to the OS. The database is at the latest compatibility level of 130. Developers have complained of the below query which executes within an acceptable time to them of 30 seconds when executed in isolation, but when they run their processes at scale the same query is executed multiple times concurrently across several threads and this is when they have observed that the execution time suffers and performance/throughput drops. The problematic T-SQL is:

```
select distinct dg.entityId, et.EntityName, dg.Version
from DataGathering dg with(nolock)
inner join entity e with(nolock)
on e.EntityId = dg.EntityId
inner join entitytype et with(nolock)
on et.EntityTypeID = e.EntityTypeID
and et.EntityName = 'Account_Third_Party_Details'
inner join entitymapping em with(nolock)
on em.ChildEntityId = dg.EntityId
and em.ParentEntityId = -1
where dg.EntityId = dg.RootId

union all

select distinct dg1.EntityId, et.EntityName, dg1.version
from datagathering dg1 with(nolock)
inner join entity e with(nolock)
on e.EntityId = dg1.EntityId
inner join entitytype et with(nolock)
on et.EntityTypeID = e.EntityTypeID
and et.EntityName = 'TIN_Details'
where dg1.EntityId = dg1.RootId
and dg1.EntityId not in (
select distinct ChildEntityId
from entitymapping
where ChildEntityId = dg1.EntityId
and ParentE

This might not help with the memory grant situation (hopefully the additional stats updates will help some with that), but I noticed that parallelism is being inhibited in this query. Check out this part of the plan:

Since there's only one row on the outer side of the nested loops join, all 900k rows are being funneled onto one thread. So despite this query running at DOP 8, this portion of the plan is completely serial. That includes the sort. Here's the XML for that sort:

If at all possible, consider avoiding the join to EntityType, and instead just grabbing that Id and filtering the Entity table with it. This will allow it to just be a predicate on an index scan of the Entity table, hopefully allowing parallelism and speeding up the execution.

Something like this:

DECLARE @tinDetailsId int;

SELECT @tinDetailsId = et.EntityTypeID 
FROM entitytype et 
WHERE et.EntityName = 'TIN_Details';

Which you could then reference in the bottom half of the query, eliminating the join:

select distinct dg1.EntityId, 'TIN_Details', dg1.version
                     from datagathering dg1 with(nolock)  
                     inner join entity e with(nolock)   
                         on e.EntityId = dg1.EntityId
                     where dg1.EntityId = dg1.RootId  
                     and e.EntityTypeID = @tinDetailsId
                     and dg1.EntityId not in (  
                         select distinct ChildEntityId   
                         from entitymapping  
                         where ChildEntityId = dg1.EntityId 
                         and ParentEntityId = -1)

You would want to do the same thing with EntityName "Account_Third_Party_Details" in the top part of the query, as it has the same problem - with twice as many rows.

PS: Totally unrelated to the topic at hand, I noticed that you have nolock hints on all the tables in this query. Make sure that you are aware of the implications of this. Check out this nifty blog posts on the topic:

Bad habits : Putting NOLOCK everywhere by Aaron Bertrand

The Read Uncommitted Isolation Level by Paul White

StackExchange Database Administrators Q#233536, answer score: 8