Seeing 2 very different execution plans for an UPDATE between test & prod environmentsWhy does my query run fast in Environment A, but slow in Environment B?SQL update satement taking a very long time / high disk usage for hoursdifference in execution plans on UAT and PROD serverDeadlock when update different rows with non-clustered indexQueries not Running in ProdTemp Table Clustered Key Not Being Honored: Bug or Expected Functionality?Implicit conversion does not affect performancedelete query takes foreverSame query different execution plansIs there any way to test which clustered index is better for specific queries?Queries key look up
Why is the blank symbol not considered part of the input alphabet of a Turing machine?
Good introductory book to type theory?
Translation of "invincible independence"
Can a player choose to add detail and flavor to their character's spells and abilities?
Why did Gendry call himself Gendry Rivers?
Scaling rounded rectangles in Illustrator
Latex editor/compiler for Windows and Powerpoint
How could a humanoid creature completely form within the span of 24 hours?
Does this website provide consistent translation into Wookiee?
Was there a dinosaur-counter in the original Jurassic Park movie?
How to get the decimal part of a number in apex
Appropriate age to involve kids in life changing decisions
Antivirus for Ubuntu 18.04
shebang or not shebang
Why did Dr. Strange keep looking into the future after the snap?
Why was Gemini VIII terminated after recovering from the OAMS thruster failure?
A♭ major 9th chord in Bach is unexpectedly dissonant/jazzy
What's weird about Proto-Indo-European Stops?
What does the copyright in a dissertation protect exactly?
Explaining intravenous drug abuse to a small child
Bash prompt takes only the first word of a hostname before the dot
Which "exotic salt" can lower water's freezing point by 70 °C?
Are modes in jazz primarily a melody thing?
Game artist computer workstation set-up – is this overkill?
Seeing 2 very different execution plans for an UPDATE between test & prod environments
Why does my query run fast in Environment A, but slow in Environment B?SQL update satement taking a very long time / high disk usage for hoursdifference in execution plans on UAT and PROD serverDeadlock when update different rows with non-clustered indexQueries not Running in ProdTemp Table Clustered Key Not Being Honored: Bug or Expected Functionality?Implicit conversion does not affect performancedelete query takes foreverSame query different execution plansIs there any way to test which clustered index is better for specific queries?Queries key look up
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)
Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.
Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?
The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...
The extra operators in the plan from Prod below (minus Clustered Index Update):
sql-server query-performance update
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
add a comment |
I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)
Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.
Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?
The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...
The extra operators in the plan from Prod below (minus Clustered Index Update):
sql-server query-performance update
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
Not the same question, but this answer still applies
– LowlyDBA
May 3 at 18:08
add a comment |
I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)
Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.
Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?
The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...
The extra operators in the plan from Prod below (minus Clustered Index Update):
sql-server query-performance update
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)
Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.
Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?
The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...
The extra operators in the plan from Prod below (minus Clustered Index Update):
sql-server query-performance update
sql-server query-performance update
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
edited May 3 at 16:42
Eric Swiggum
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
asked May 3 at 15:34
Eric SwiggumEric Swiggum
323117
323117
Not the same question, but this answer still applies
– LowlyDBA
May 3 at 18:08
add a comment |
Not the same question, but this answer still applies
– LowlyDBA
May 3 at 18:08
Not the same question, but this answer still applies
– LowlyDBA
May 3 at 18:08
Not the same question, but this answer still applies
– LowlyDBA
May 3 at 18:08
add a comment |
1 Answer
1
active
oldest
votes
Hover over the clustered index update, you'll see a list of the other indexes involved.
If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.
See Wide vs. Narrow Plans by Bart Duncan.
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
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
);
);
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%2f237326%2fseeing-2-very-different-execution-plans-for-an-update-between-test-prod-enviro%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
Hover over the clustered index update, you'll see a list of the other indexes involved.
If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.
See Wide vs. Narrow Plans by Bart Duncan.
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
add a comment |
Hover over the clustered index update, you'll see a list of the other indexes involved.
If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.
See Wide vs. Narrow Plans by Bart Duncan.
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
add a comment |
Hover over the clustered index update, you'll see a list of the other indexes involved.
If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.
See Wide vs. Narrow Plans by Bart Duncan.
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
Hover over the clustered index update, you'll see a list of the other indexes involved.
If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.
See Wide vs. Narrow Plans by Bart Duncan.
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
answered May 3 at 15:45
Erik DarlingErik Darling
23.3k1370114
23.3k1370114
add a comment |
add a comment |
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%2f237326%2fseeing-2-very-different-execution-plans-for-an-update-between-test-prod-enviro%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
Not the same question, but this answer still applies
– LowlyDBA
May 3 at 18:08