sp_blitzCache against one stored procedureCode creates different plan when ran ad-hoc vs. in a stored procedureCan I get SSMS to show me the Actual query costs in the Execution plan pane?Procedure cache (plan cache) sizeSQL Server 2012 Resource Governor and memory used by CLRUnderstanding below execution planQueries running slower on production (failover cluster) than on homologation envcomparing left join and outer apply doing the same thingQuery memory grant and tempdb spillInclude stored procedure text output in the maintenance plan logHash join spills to disk even though there is plenty of memory granted for the query
How do I extract a value from a time formatted value in excel?
Do the temporary hit points from the Battlerager barbarian's Reckless Abandon stack if I make multiple attacks on my turn?
Was Spock the First Vulcan in Starfleet?
Large drywall patch supports
How can a function with a hole (removable discontinuity) equal a function with no hole?
Valid Badminton Score?
when is out of tune ok?
Is exact Kanji stroke length important?
How does buying out courses with grant money work?
What is the intuitive meaning of having a linear relationship between the logs of two variables?
System.debug(JSON.Serialize(o)) Not longer shows full string
Hostile work environment after whistle-blowing on coworker and our boss. What do I do?
Why are there no referendums in the US?
A problem in Probability theory
Term for the "extreme-extension" version of a straw man fallacy?
What is the difference between "behavior" and "behaviour"?
Is expanding the research of a group into machine learning as a PhD student risky?
How do scammers retract money, while you can’t?
Go Pregnant or Go Home
How did Arya survive the stabbing?
Tiptoe or tiphoof? Adjusting words to better fit fantasy races
A Rare Riley Riddle
How easy is it to start Magic from scratch?
Describing a person. What needs to be mentioned?
sp_blitzCache against one stored procedure
Code creates different plan when ran ad-hoc vs. in a stored procedureCan I get SSMS to show me the Actual query costs in the Execution plan pane?Procedure cache (plan cache) sizeSQL Server 2012 Resource Governor and memory used by CLRUnderstanding below execution planQueries running slower on production (failover cluster) than on homologation envcomparing left join and outer apply doing the same thingQuery memory grant and tempdb spillInclude stored procedure text output in the maintenance plan logHash join spills to disk even though there is plenty of memory granted for the query
Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.
I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here
https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv
The command run to generate this output was
exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'
Few questions
1) I noticed that we have multiple rows for the same plan handle, why is that?
2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?
3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2
4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?
We are running SQL Server 2012.
Thanks,
Kevin
sql-server sql-server-2012 execution-plan sp-blitzcache
edited Mar 21 at 15:23
Randi Vertongen
4,016924
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
New contributor
kevinnwhat 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 |
Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.
I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here
https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv
The command run to generate this output was
exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'
Few questions
1) I noticed that we have multiple rows for the same plan handle, why is that?
2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?
3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2
4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?
We are running SQL Server 2012.
Thanks,
Kevin
sql-server sql-server-2012 execution-plan sp-blitzcache
edited Mar 21 at 15:23
Randi Vertongen
4,016924
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
New contributor
kevinnwhat 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 |
Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.
I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here
https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv
The command run to generate this output was
exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'
Few questions
1) I noticed that we have multiple rows for the same plan handle, why is that?
2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?
3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2
4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?
We are running SQL Server 2012.
Thanks,
Kevin
sql-server sql-server-2012 execution-plan sp-blitzcache
edited Mar 21 at 15:23
Randi Vertongen
4,016924
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
New contributor
kevinnwhat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.
I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here
https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv
The command run to generate this output was
exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'
Few questions
1) I noticed that we have multiple rows for the same plan handle, why is that?
2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?
3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2
4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?
We are running SQL Server 2012.
Thanks,
Kevin
sql-server sql-server-2012 execution-plan sp-blitzcache
sql-server sql-server-2012 execution-plan sp-blitzcache
edited Mar 21 at 15:23
Randi Vertongen
4,016924
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
New contributor
kevinnwhat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 21 at 15:23
Randi Vertongen
4,016924
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
New contributor
kevinnwhat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 21 at 15:23
Randi Vertongen
4,016924
edited Mar 21 at 15:23
Randi Vertongen
4,016924
edited Mar 21 at 15:23
Randi Vertongen
4,016924
4,016924
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
New contributor
kevinnwhat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
asked Mar 21 at 15:08
kevinnwhatkevinnwhat
132
132
New contributor
kevinnwhat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
kevinnwhat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
kevinnwhat 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 |
add a comment |
1 Answer
1
active
oldest
votes
Rephrased your questions a little:
Q: Why multiple rows for the same plan handle?
A: Because a plan can include multiple statements.
Q: Why does a proc have multiple lines in sp_BlitzCache?
Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.
Q: When I see "CREATE PROC" does that mean the proc was created?
No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.
Q: Why is the memory granted different from memory used?
Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
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
);
);
kevinnwhat 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%2f232744%2fsp-blitzcache-against-one-stored-procedure%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
Rephrased your questions a little:
Q: Why multiple rows for the same plan handle?
A: Because a plan can include multiple statements.
Q: Why does a proc have multiple lines in sp_BlitzCache?
Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.
Q: When I see "CREATE PROC" does that mean the proc was created?
No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.
Q: Why is the memory granted different from memory used?
Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
add a comment |
Rephrased your questions a little:
Q: Why multiple rows for the same plan handle?
A: Because a plan can include multiple statements.
Q: Why does a proc have multiple lines in sp_BlitzCache?
Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.
Q: When I see "CREATE PROC" does that mean the proc was created?
No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.
Q: Why is the memory granted different from memory used?
Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
add a comment |
Rephrased your questions a little:
Q: Why multiple rows for the same plan handle?
A: Because a plan can include multiple statements.
Q: Why does a proc have multiple lines in sp_BlitzCache?
Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.
Q: When I see "CREATE PROC" does that mean the proc was created?
No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.
Q: Why is the memory granted different from memory used?
Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
Rephrased your questions a little:
Q: Why multiple rows for the same plan handle?
A: Because a plan can include multiple statements.
Q: Why does a proc have multiple lines in sp_BlitzCache?
Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.
Q: When I see "CREATE PROC" does that mean the proc was created?
No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.
Q: Why is the memory granted different from memory used?
Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
answered Mar 21 at 15:38
Brent OzarBrent Ozar
35.6k19109241
35.6k19109241
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
add a comment |
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
Got it thanks Brent.
– kevinnwhat
Mar 21 at 15:47
add a comment |
kevinnwhat is a new contributor. Be nice, and check out our Code of Conduct.
kevinnwhat is a new contributor. Be nice, and check out our Code of Conduct.
kevinnwhat is a new contributor. Be nice, and check out our Code of Conduct.
kevinnwhat 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%2f232744%2fsp-blitzcache-against-one-stored-procedure%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
