Execution plan doesn't show memory grant












6















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?










share|improve this question









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
















6















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?










share|improve this question









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














6












6








6








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?










share|improve this question









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






share|improve this question









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.











share|improve this question









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.









share|improve this question




share|improve this question








edited 4 hours ago









MDCCL

6,72731744




6,72731744






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




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














  • 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










1 Answer
1






active

oldest

votes


















10














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.






share|improve this answer



















  • 2





    Thanks. I didn't know that suttle distinction. Now it works.

    – 5th
    yesterday











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.










draft saved

draft discarded


















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









10














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.






share|improve this answer



















  • 2





    Thanks. I didn't know that suttle distinction. Now it works.

    – 5th
    yesterday
















10














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.






share|improve this answer



















  • 2





    Thanks. I didn't know that suttle distinction. Now it works.

    – 5th
    yesterday














10












10








10







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered yesterday









Paul WhitePaul White

50.7k14277447




50.7k14277447








  • 2





    Thanks. I didn't know that suttle distinction. Now it works.

    – 5th
    yesterday














  • 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










5th is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

"Incorrect syntax near the keyword 'ON'. (on update cascade, on delete cascade,)

Alcedinidae

Origin of the phrase “under your belt”?