patternsqlModerate
Execution plan doesn't show memory grant
Viewed 0 times
showgrantplandoesnmemoryexecution
Problem
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#228549, answer score: 10
Revisions (0)
No revisions yet.