SQL Server 2016 - excessive memory grant warning on poor performing query The Next CEO of Stack OverflowQuery slower after upgrade from sql server 2008R2 to 2014sp2TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing QuerySpeed up INSERT procedureSame query way faster with distinct than without distinctLarge memory grant requestsPoor performing Query -Tsql execution plan - estimated number of rows =1 Paste the PlanMSSQL - Query had to wait for memory grantPerformance tuning on a queryBad performance using “NOT IN”why is this left join faster than an inner join?
Could a dragon use its wings to swim?
Inductor and Capacitor in Parallel
Vector calculus integration identity problem
Won the lottery - how do I keep the money?
Is there a difference between "Fahrstuhl" and "Aufzug"?
Why am I getting "Static method cannot be referenced from a non static context: String String.valueOf(Object)"?
What are the unusually-enlarged wing sections on this P-38 Lightning?
Yu-Gi-Oh cards in Python 3
Players Circumventing the limitations of Wish
Do scriptures give a method to recognize a truly self-realized person/jivanmukta?
What day is it again?
Calculate the Mean mean of two numbers
My ex-girlfriend uses my Apple ID to login to her iPad, do I have to give her my Apple ID password to reset it?
Is there such a thing as a proper verb, like a proper noun?
What happened in Rome, when the western empire "fell"?
Airplane gently rocking its wings during whole flight
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Traveling with my 5 year old daughter (as the father) without the mother from Germany to Mexico
Can I board the first leg of the flight without having final country's visa?
Point distance program written without a framework
Is there a reasonable and studied concept of reduction between regular languages?
Do I need to write [sic] when including a quotation with a number less than 10 that isn't written out?
Film where the government was corrupt with aliens, people sent to kill aliens are given rigged visors not showing the right aliens
What flight has the highest ratio of timezone difference to flight time?
SQL Server 2016 - excessive memory grant warning on poor performing query
The Next CEO of Stack OverflowQuery slower after upgrade from sql server 2008R2 to 2014sp2TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing QuerySpeed up INSERT procedureSame query way faster with distinct than without distinctLarge memory grant requestsPoor performing Query -Tsql execution plan - estimated number of rows =1 Paste the PlanMSSQL - Query had to wait for memory grantPerformance tuning on a queryBad performance using “NOT IN”why is this left join faster than an inner join?
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.
Josh Darnell has kindly suggested to re-factor the query to the below in order to avoid parallelism being inhibited which he spotted on a certain operator. The re-factored query throws the error "Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "et.EntityName" could not be bound." How do I work around that?
DECLARE @tinDetailsId int;
SELECT @tinDetailsId = et.EntityTypeID
FROM entitytype et
WHERE et.EntityName = 'TIN_Details';
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)
UNION ALL
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
sql-server t-sql query-performance sql-server-2016 memory-grant
add a comment |
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.
Josh Darnell has kindly suggested to re-factor the query to the below in order to avoid parallelism being inhibited which he spotted on a certain operator. The re-factored query throws the error "Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "et.EntityName" could not be bound." How do I work around that?
DECLARE @tinDetailsId int;
SELECT @tinDetailsId = et.EntityTypeID
FROM entitytype et
WHERE et.EntityName = 'TIN_Details';
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)
UNION ALL
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
sql-server t-sql query-performance sql-server-2016 memory-grant
Thanks for your input. I'll run update statistics with fullscan against the four tables listed in my post and let you know if that makes any difference and if the execution plan changes. It will take some time since the DataGathering table is large! I was hoping to focus my efforts on re-writing that hideous query though. So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?
– Fza
2 days ago
2
"So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?" - No, that is not logically equivalent. Your current query removes duplicates from each of the individual sets (with DISTINCT), and then combines those sets with UNION ALL - allowing duplicates between the two sets. Kin's suggestion eliminates all duplicate rows, even those between the two sets, so results could be different.
– Josh Darnell
2 days ago
add a comment |
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.
Josh Darnell has kindly suggested to re-factor the query to the below in order to avoid parallelism being inhibited which he spotted on a certain operator. The re-factored query throws the error "Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "et.EntityName" could not be bound." How do I work around that?
DECLARE @tinDetailsId int;
SELECT @tinDetailsId = et.EntityTypeID
FROM entitytype et
WHERE et.EntityName = 'TIN_Details';
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)
UNION ALL
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
sql-server t-sql query-performance sql-server-2016 memory-grant
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.
Josh Darnell has kindly suggested to re-factor the query to the below in order to avoid parallelism being inhibited which he spotted on a certain operator. The re-factored query throws the error "Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "et.EntityName" could not be bound." How do I work around that?
DECLARE @tinDetailsId int;
SELECT @tinDetailsId = et.EntityTypeID
FROM entitytype et
WHERE et.EntityName = 'TIN_Details';
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)
UNION ALL
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
sql-server t-sql query-performance sql-server-2016 memory-grant
sql-server t-sql query-performance sql-server-2016 memory-grant
edited yesterday
Fza
asked 2 days ago
FzaFza
3791414
3791414
Thanks for your input. I'll run update statistics with fullscan against the four tables listed in my post and let you know if that makes any difference and if the execution plan changes. It will take some time since the DataGathering table is large! I was hoping to focus my efforts on re-writing that hideous query though. So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?
– Fza
2 days ago
2
"So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?" - No, that is not logically equivalent. Your current query removes duplicates from each of the individual sets (with DISTINCT), and then combines those sets with UNION ALL - allowing duplicates between the two sets. Kin's suggestion eliminates all duplicate rows, even those between the two sets, so results could be different.
– Josh Darnell
2 days ago
add a comment |
Thanks for your input. I'll run update statistics with fullscan against the four tables listed in my post and let you know if that makes any difference and if the execution plan changes. It will take some time since the DataGathering table is large! I was hoping to focus my efforts on re-writing that hideous query though. So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?
– Fza
2 days ago
2
"So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?" - No, that is not logically equivalent. Your current query removes duplicates from each of the individual sets (with DISTINCT), and then combines those sets with UNION ALL - allowing duplicates between the two sets. Kin's suggestion eliminates all duplicate rows, even those between the two sets, so results could be different.
– Josh Darnell
2 days ago
Thanks for your input. I'll run update statistics with fullscan against the four tables listed in my post and let you know if that makes any difference and if the execution plan changes. It will take some time since the DataGathering table is large! I was hoping to focus my efforts on re-writing that hideous query though. So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?
– Fza
2 days ago
Thanks for your input. I'll run update statistics with fullscan against the four tables listed in my post and let you know if that makes any difference and if the execution plan changes. It will take some time since the DataGathering table is large! I was hoping to focus my efforts on re-writing that hideous query though. So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?
– Fza
2 days ago
2
2
"So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?" - No, that is not logically equivalent. Your current query removes duplicates from each of the individual sets (with DISTINCT), and then combines those sets with UNION ALL - allowing duplicates between the two sets. Kin's suggestion eliminates all duplicate rows, even those between the two sets, so results could be different.
– Josh Darnell
2 days ago
"So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?" - No, that is not logically equivalent. Your current query removes duplicates from each of the individual sets (with DISTINCT), and then combines those sets with UNION ALL - allowing duplicates between the two sets. Kin's suggestion eliminates all duplicate rows, even those between the two sets, so results could be different.
– Josh Darnell
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
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
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
1
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
1
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
add a comment |
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233536%2fsql-server-2016-excessive-memory-grant-warning-on-poor-performing-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
1
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
1
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
add a comment |
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
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
1
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
1
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
add a comment |
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
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
edited yesterday
answered 2 days ago
Josh DarnellJosh Darnell
7,38522241
7,38522241
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
1
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
1
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
add a comment |
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
1
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
1
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
thanks a lot for your help. The update statistics on the DataGathering table didn't change anything, the query still executes in an improved 6-7 seconds and the execution plan still hasn't changed. I never would have noticed that parallelism was inhibited on that specific operator. Is that something you usually look out for? I've tried your suggestion but couldn't get the query to compile... will add a comment with those details..
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
interestingly, if I slap a MAXDOP 12 query hint on the original query it executes slightly quicker at 5 seconds but the memory grant warning is gone, I'll paste the execution plan below. This is good, I was certain that the memory grant warning is what was causing the query to perform poorly at scale. I know we can do better though and still very keen to try your suggestion but just can't get the query to compile... brentozar.com/pastetheplan/?id=HyIHWohu4
– Fza
yesterday
1
1
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
@Fza sorry, I didn't notice there was a reference to EntityName in the select list. Since it's always the same now, you can just hardcode it. I've updated my answer.
– Josh Darnell
yesterday
1
1
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
@Fza And unbalanced parallelism isn't the first thing I'd look for, I just happened to notice it 😀 It's a little bit more obvious in Plan Explorer than in SSMS.
– Josh Darnell
yesterday
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233536%2fsql-server-2016-excessive-memory-grant-warning-on-poor-performing-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Thanks for your input. I'll run update statistics with fullscan against the four tables listed in my post and let you know if that makes any difference and if the execution plan changes. It will take some time since the DataGathering table is large! I was hoping to focus my efforts on re-writing that hideous query though. So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?
– Fza
2 days ago
2
"So are you saying that removing the distinct keyword throughout the entire query and replacing union all with union is logically the same and will return the same data?" - No, that is not logically equivalent. Your current query removes duplicates from each of the individual sets (with DISTINCT), and then combines those sets with UNION ALL - allowing duplicates between the two sets. Kin's suggestion eliminates all duplicate rows, even those between the two sets, so results could be different.
– Josh Darnell
2 days ago