Btukfyl

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 ParentEntityId = -1)

The actual execution plan shows the below memory grant warning:

The graphical execution plan can be found here:

https://www.brentozar.com/pastetheplan/?id=r18ZtCidN

Below are the row counts and sizes of the tables touched by this query. The most expensive operator is an index scan of a non-clustered index on the DataGathering table which makes sense given the size of the table compared to the others. I understand why/how the memory grant is required which I believe is due to how the query is written which requires multiple sorts and hash operators. What I need advice/guidance on is how to avoid the memory grants, T-SQL and re-factoring code is not my strong point, is there a way to re-write this query so that it is more performant? If I can tune the query to run faster in isolation then hopefully the benefits would transfer to when it is run at scale which is when the performance starts to suffer. Happy to provide any more information and hoping to learn something from this!

After updating statistics on 3 of the tables:

UPDATE STATISTICS Entity WITH FULLSCAN; 

UPDATE STATISTICS EntityMapping WITH FULLSCAN; 

UPDATE STATISTICS EntityType WITH FULLSCAN;

...the execution plan has improved some:

https://www.brentozar.com/pastetheplan/?id=rkVmdkh_4

Unfortunately, the "Excessive Grant" warning is still there.

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, et.EntityName, 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