Execution plan doesn't show memory grant
I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to look at the execution plan I can't see the memory grant there.
My question now is whether I can only see the memory grant if I am logged in as an admin? Or does SQL Server 2019 have a complete different query engine than SQL Server 2017?
sql-server ssms execution-plan memory-grant sql-server-2019
edited 4 hours ago
MDCCL
6,72731744
asked yesterday
5th5th
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to look at the execution plan I can't see the memory grant there.
My question now is whether I can only see the memory grant if I am logged in as an admin? Or does SQL Server 2019 have a complete different query engine than SQL Server 2017?
sql-server ssms execution-plan memory-grant sql-server-2019
edited 4 hours ago
MDCCL
6,72731744
asked yesterday
5th5th
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Maybe the query didn't need any memory grant.
– Denis Rubashkin
yesterday
add a comment |
I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to look at the execution plan I can't see the memory grant there.
My question now is whether I can only see the memory grant if I am logged in as an admin? Or does SQL Server 2019 have a complete different query engine than SQL Server 2017?
sql-server ssms execution-plan memory-grant sql-server-2019
edited 4 hours ago
MDCCL
6,72731744
asked yesterday
5th5th
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to look at the execution plan I can't see the memory grant there.
My question now is whether I can only see the memory grant if I am logged in as an admin? Or does SQL Server 2019 have a complete different query engine than SQL Server 2017?
sql-server ssms execution-plan memory-grant sql-server-2019
sql-server ssms execution-plan memory-grant sql-server-2019
edited 4 hours ago
MDCCL
6,72731744
asked yesterday
5th5th
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
MDCCL
6,72731744
asked yesterday
5th5th
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
MDCCL
6,72731744
edited 4 hours ago
MDCCL
6,72731744
edited 4 hours ago
MDCCL
6,72731744
6,72731744
asked yesterday
5th5th
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked yesterday
5th5th
1335
asked yesterday
5th5th
1335
1335
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5th is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Maybe the query didn't need any memory grant.
– Denis Rubashkin
yesterday
add a comment |
1
Maybe the query didn't need any memory grant.
– Denis Rubashkin
yesterday
1
1
Maybe the query didn't need any memory grant.
– Denis Rubashkin
yesterday
Maybe the query didn't need any memory grant.
– Denis Rubashkin
yesterday
add a comment |
1 Answer
1
active
oldest
votes
Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.
If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.
The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.
answered yesterday
Paul White♦Paul White
50.7k14277447
2
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
add a comment |
Your Answer
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
});
}
});
5th is a new contributor. Be nice, and check out our Code of Conduct.
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%2f228549%2fexecution-plan-doesnt-show-memory-grant%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
Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.
If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.
The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.
answered yesterday
Paul White♦Paul White
50.7k14277447
2
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
add a comment |
Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.
If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.
The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.
answered yesterday
Paul White♦Paul White
50.7k14277447
2
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
add a comment |
Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.
If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.
The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.
answered yesterday
Paul White♦Paul White
50.7k14277447
Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.
If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.
The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.
answered yesterday
Paul White♦Paul White
50.7k14277447
answered yesterday
Paul White♦Paul White
50.7k14277447
answered yesterday
Paul White♦Paul White
50.7k14277447
answered yesterday
Paul White♦Paul White
50.7k14277447
50.7k14277447
2
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
add a comment |
2
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
2
2
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
Thanks. I didn't know that suttle distinction. Now it works.
– 5th
yesterday
add a comment |
5th is a new contributor. Be nice, and check out our Code of Conduct.
5th is a new contributor. Be nice, and check out our Code of Conduct.
5th is a new contributor. Be nice, and check out our Code of Conduct.
5th is a new contributor. Be nice, and check out our Code of Conduct.
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%2f228549%2fexecution-plan-doesnt-show-memory-grant%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
1
Maybe the query didn't need any memory grant.
– Denis Rubashkin
yesterday