Mixing
Warning about memory “Excessive Grant†in the query plan - how to find out what is causing it?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.
I am trying to find what might me causing the Excessive Grant. Can it be conversions?
Looking at the execution plan I can see the following:
<ScalarOperator
ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
And this one:
<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
Here is the query, although you can see the query with the execution plan here too:
DECLARE @arrivalDate DATEtime = '2018-08-20'
SELECT app.applicantID,
app.applicationID,
a.preferredName,
u.firstname,
u.lastname,
u.loginId AS emailAddress,
s.status AS statusDescription,
CAST(repl.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
u.loginId,
c.countryName
FROM app.application AS app
JOIN app.applicant AS a ON a.applicantId = app.applicantId
JOIN usr.[user] AS u ON u.userId = a.userId
JOIN app.ref_applicationStatus AS s ON s.statusCode = app.status
JOIN APIA_Repl_Sub.dbo.repl_Aupair AS repl ON repl.JunoCore_applicationID = app.applicationID
JOIN app.Country AS c ON c.countryCode = a.nationalityCode
WHERE repl.arrivalDate = @arrivalDate
UNION ALL
(
SELECT app.applicantID,
app.applicationID,
app.preferredName,
app.firstname,
app.lastname,
app.emailAddress,
ap.status,
CAST(app.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
app.emailAddress AS loginId,
c.countryName
FROM JUNOCore.dbo.vw_SelectApplication AS app
INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
WHERE arrivalDate = @arrivalDate AND
app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)
This is how the warning looks like:
The execution plan is here.
How do I tackle this warning?
As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?
Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.
sql-server query-performance optimization execution-plan performance-tuning
edited Sep 6 at 20:11
Peter Mortensen
22119
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
add a comment |Â
up vote
3
down vote
favorite
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.
I am trying to find what might me causing the Excessive Grant. Can it be conversions?
Looking at the execution plan I can see the following:
<ScalarOperator
ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
And this one:
<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
Here is the query, although you can see the query with the execution plan here too:
DECLARE @arrivalDate DATEtime = '2018-08-20'
SELECT app.applicantID,
app.applicationID,
a.preferredName,
u.firstname,
u.lastname,
u.loginId AS emailAddress,
s.status AS statusDescription,
CAST(repl.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
u.loginId,
c.countryName
FROM app.application AS app
JOIN app.applicant AS a ON a.applicantId = app.applicantId
JOIN usr.[user] AS u ON u.userId = a.userId
JOIN app.ref_applicationStatus AS s ON s.statusCode = app.status
JOIN APIA_Repl_Sub.dbo.repl_Aupair AS repl ON repl.JunoCore_applicationID = app.applicationID
JOIN app.Country AS c ON c.countryCode = a.nationalityCode
WHERE repl.arrivalDate = @arrivalDate
UNION ALL
(
SELECT app.applicantID,
app.applicationID,
app.preferredName,
app.firstname,
app.lastname,
app.emailAddress,
ap.status,
CAST(app.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
app.emailAddress AS loginId,
c.countryName
FROM JUNOCore.dbo.vw_SelectApplication AS app
INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
WHERE arrivalDate = @arrivalDate AND
app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)
This is how the warning looks like:
The execution plan is here.
How do I tackle this warning?
As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?
Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.
sql-server query-performance optimization execution-plan performance-tuning
edited Sep 6 at 20:11
Peter Mortensen
22119
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
Is this actually causing a problem (and how do you know), or do you just want to get rid of the warning?
– Forrest
Sep 6 at 15:42
Where is@arrivalDatecoming from? Also, look at the sizes there. A 2MB memory grant isn't likely to cause an issue, even on a flip phone.
– sp_BlitzErik
Sep 6 at 15:52
I dont think this is issue, its execution plan just doing its duty of reporting excessive memory grant. If you see grant request was 1.2 MB and used was 24 KB this should not be an issue. I always first make sure statistics are updated if I get excessive memory grant warning
– Shanky
Sep 6 at 18:16
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.
I am trying to find what might me causing the Excessive Grant. Can it be conversions?
Looking at the execution plan I can see the following:
<ScalarOperator
ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
And this one:
<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
Here is the query, although you can see the query with the execution plan here too:
DECLARE @arrivalDate DATEtime = '2018-08-20'
SELECT app.applicantID,
app.applicationID,
a.preferredName,
u.firstname,
u.lastname,
u.loginId AS emailAddress,
s.status AS statusDescription,
CAST(repl.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
u.loginId,
c.countryName
FROM app.application AS app
JOIN app.applicant AS a ON a.applicantId = app.applicantId
JOIN usr.[user] AS u ON u.userId = a.userId
JOIN app.ref_applicationStatus AS s ON s.statusCode = app.status
JOIN APIA_Repl_Sub.dbo.repl_Aupair AS repl ON repl.JunoCore_applicationID = app.applicationID
JOIN app.Country AS c ON c.countryCode = a.nationalityCode
WHERE repl.arrivalDate = @arrivalDate
UNION ALL
(
SELECT app.applicantID,
app.applicationID,
app.preferredName,
app.firstname,
app.lastname,
app.emailAddress,
ap.status,
CAST(app.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
app.emailAddress AS loginId,
c.countryName
FROM JUNOCore.dbo.vw_SelectApplication AS app
INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
WHERE arrivalDate = @arrivalDate AND
app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)
This is how the warning looks like:
The execution plan is here.
How do I tackle this warning?
As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?
Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.
sql-server query-performance optimization execution-plan performance-tuning
edited Sep 6 at 20:11
Peter Mortensen
22119
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.
I am trying to find what might me causing the Excessive Grant. Can it be conversions?
Looking at the execution plan I can see the following:
<ScalarOperator
ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
And this one:
<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
<Convert DataType="date" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
Here is the query, although you can see the query with the execution plan here too:
DECLARE @arrivalDate DATEtime = '2018-08-20'
SELECT app.applicantID,
app.applicationID,
a.preferredName,
u.firstname,
u.lastname,
u.loginId AS emailAddress,
s.status AS statusDescription,
CAST(repl.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
u.loginId,
c.countryName
FROM app.application AS app
JOIN app.applicant AS a ON a.applicantId = app.applicantId
JOIN usr.[user] AS u ON u.userId = a.userId
JOIN app.ref_applicationStatus AS s ON s.statusCode = app.status
JOIN APIA_Repl_Sub.dbo.repl_Aupair AS repl ON repl.JunoCore_applicationID = app.applicationID
JOIN app.Country AS c ON c.countryCode = a.nationalityCode
WHERE repl.arrivalDate = @arrivalDate
UNION ALL
(
SELECT app.applicantID,
app.applicationID,
app.preferredName,
app.firstname,
app.lastname,
app.emailAddress,
ap.status,
CAST(app.arrivalDate AS DATE) AS arrivalDate,
app.moodleCourseComplete,
app.moodleCourseCompleteUpdated,
app.emailAddress AS loginId,
c.countryName
FROM JUNOCore.dbo.vw_SelectApplication AS app
INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
WHERE arrivalDate = @arrivalDate AND
app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)
This is how the warning looks like:
The execution plan is here.
How do I tackle this warning?
As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?
Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.
sql-server query-performance optimization execution-plan performance-tuning
edited Sep 6 at 20:11
Peter Mortensen
22119
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
edited Sep 6 at 20:11
Peter Mortensen
22119
edited Sep 6 at 20:11
Peter Mortensen
22119
edited Sep 6 at 20:11
Peter Mortensen
22119
22119
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
asked Sep 6 at 15:33
marcello miorelli
5,1181559120
5,1181559120
Is this actually causing a problem (and how do you know), or do you just want to get rid of the warning?
– Forrest
Sep 6 at 15:42
Where is@arrivalDatecoming from? Also, look at the sizes there. A 2MB memory grant isn't likely to cause an issue, even on a flip phone.
– sp_BlitzErik
Sep 6 at 15:52
I dont think this is issue, its execution plan just doing its duty of reporting excessive memory grant. If you see grant request was 1.2 MB and used was 24 KB this should not be an issue. I always first make sure statistics are updated if I get excessive memory grant warning
– Shanky
Sep 6 at 18:16
add a comment |Â
Is this actually causing a problem (and how do you know), or do you just want to get rid of the warning?
– Forrest
Sep 6 at 15:42
Where is@arrivalDatecoming from? Also, look at the sizes there. A 2MB memory grant isn't likely to cause an issue, even on a flip phone.
– sp_BlitzErik
Sep 6 at 15:52
I dont think this is issue, its execution plan just doing its duty of reporting excessive memory grant. If you see grant request was 1.2 MB and used was 24 KB this should not be an issue. I always first make sure statistics are updated if I get excessive memory grant warning
– Shanky
Sep 6 at 18:16
Is this actually causing a problem (and how do you know), or do you just want to get rid of the warning?
– Forrest
Sep 6 at 15:42
Is this actually causing a problem (and how do you know), or do you just want to get rid of the warning?
– Forrest
Sep 6 at 15:42
Where is
@arrivalDate coming from? Also, look at the sizes there. A 2MB memory grant isn't likely to cause an issue, even on a flip phone.– sp_BlitzErik
Sep 6 at 15:52
Where is
@arrivalDate coming from? Also, look at the sizes there. A 2MB memory grant isn't likely to cause an issue, even on a flip phone.– sp_BlitzErik
Sep 6 at 15:52
I dont think this is issue, its execution plan just doing its duty of reporting excessive memory grant. If you see grant request was 1.2 MB and used was 24 KB this should not be an issue. I always first make sure statistics are updated if I get excessive memory grant warning
– Shanky
Sep 6 at 18:16
I dont think this is issue, its execution plan just doing its duty of reporting excessive memory grant. If you see grant request was 1.2 MB and used was 24 KB this should not be an issue. I always first make sure statistics are updated if I get excessive memory grant warning
– Shanky
Sep 6 at 18:16
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
6
down vote
The most common two types of operators that consume memory are:
- Sorts
- Hashes
If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.
Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
6
down vote
The most common two types of operators that consume memory are:
- Sorts
- Hashes
If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.
Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
add a comment |Â
up vote
6
down vote
The most common two types of operators that consume memory are:
- Sorts
- Hashes
If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.
Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
add a comment |Â
up vote
6
down vote
up vote
6
down vote
The most common two types of operators that consume memory are:
- Sorts
- Hashes
If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.
Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
The most common two types of operators that consume memory are:
- Sorts
- Hashes
If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.
Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
edited Sep 6 at 18:05
Joe Obbish
18.7k32477
18.7k32477
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
answered Sep 6 at 16:21
sp_BlitzErik
19.5k1161101
19.5k1161101
add a comment |Â
add a comment |Â
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216931%2fwarning-about-memory-excessive-grant-in-the-query-plan-how-to-find-out-what%23new-answer', 'question_page');
);
Post as a guest
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
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
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
Is this actually causing a problem (and how do you know), or do you just want to get rid of the warning?
– Forrest
Sep 6 at 15:42
Where is
@arrivalDatecoming from? Also, look at the sizes there. A 2MB memory grant isn't likely to cause an issue, even on a flip phone.– sp_BlitzErik
Sep 6 at 15:52
I dont think this is issue, its execution plan just doing its duty of reporting excessive memory grant. If you see grant request was 1.2 MB and used was 24 KB this should not be an issue. I always first make sure statistics are updated if I get excessive memory grant warning
– Shanky
Sep 6 at 18:16