Why would a Clustered Index Seek return a higher “Actual Number of Rows” than there are rows in the...
I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, until a few hours or days later when a bad plan gets cached again.
Looking at the "bad" plan, I noticed something that strikes me as odd: there is a Clustered Index Seek on a table that currently has about 1.7 million rows. The "Estimated Number of Rows" for this operation is about 1200, which is definitely in line with the average row count I would expect from that operation in this case, but the "Actual Number of Rows" is in excess of 60 million! Following the fat line from this leaf node, various downstream operations such as joins and sorts are being performed on all 60 million, causing excessive slowness, spills to tempdb, and other badness.
I must be misunderstanding what a Clustered Index Seek actually does, because I wouldn't think it's possible for it to "output" more rows than are in the underlying table. What could cause this? And better yet, any pointers on how to fix it?
[Bonus points for including something like "sp_updatestats fixes it every time but can't figure out how to fix it permanently? Go read this article." This has been a general problem for us on a few different fronts lately.]
sql-server optimization execution-plan azure-sql-database
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
add a comment |
I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, until a few hours or days later when a bad plan gets cached again.
Looking at the "bad" plan, I noticed something that strikes me as odd: there is a Clustered Index Seek on a table that currently has about 1.7 million rows. The "Estimated Number of Rows" for this operation is about 1200, which is definitely in line with the average row count I would expect from that operation in this case, but the "Actual Number of Rows" is in excess of 60 million! Following the fat line from this leaf node, various downstream operations such as joins and sorts are being performed on all 60 million, causing excessive slowness, spills to tempdb, and other badness.
I must be misunderstanding what a Clustered Index Seek actually does, because I wouldn't think it's possible for it to "output" more rows than are in the underlying table. What could cause this? And better yet, any pointers on how to fix it?
[Bonus points for including something like "sp_updatestats fixes it every time but can't figure out how to fix it permanently? Go read this article." This has been a general problem for us on a few different fronts lately.]
sql-server optimization execution-plan azure-sql-database
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
Certainly: brentozar.com/pastetheplan/?id=BktRIhC1V
– Todd Menier
Dec 12 '18 at 16:27
60m row index seek in question is on ProductCatalog. I do see there's an index scan in the plan as well and I may look into it, but the "good" plan contains that too and time-wise it looks to be a non-factor in both cases.
– Todd Menier
Dec 12 '18 at 16:30
add a comment |
I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, until a few hours or days later when a bad plan gets cached again.
Looking at the "bad" plan, I noticed something that strikes me as odd: there is a Clustered Index Seek on a table that currently has about 1.7 million rows. The "Estimated Number of Rows" for this operation is about 1200, which is definitely in line with the average row count I would expect from that operation in this case, but the "Actual Number of Rows" is in excess of 60 million! Following the fat line from this leaf node, various downstream operations such as joins and sorts are being performed on all 60 million, causing excessive slowness, spills to tempdb, and other badness.
I must be misunderstanding what a Clustered Index Seek actually does, because I wouldn't think it's possible for it to "output" more rows than are in the underlying table. What could cause this? And better yet, any pointers on how to fix it?
[Bonus points for including something like "sp_updatestats fixes it every time but can't figure out how to fix it permanently? Go read this article." This has been a general problem for us on a few different fronts lately.]
sql-server optimization execution-plan azure-sql-database
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, until a few hours or days later when a bad plan gets cached again.
Looking at the "bad" plan, I noticed something that strikes me as odd: there is a Clustered Index Seek on a table that currently has about 1.7 million rows. The "Estimated Number of Rows" for this operation is about 1200, which is definitely in line with the average row count I would expect from that operation in this case, but the "Actual Number of Rows" is in excess of 60 million! Following the fat line from this leaf node, various downstream operations such as joins and sorts are being performed on all 60 million, causing excessive slowness, spills to tempdb, and other badness.
I must be misunderstanding what a Clustered Index Seek actually does, because I wouldn't think it's possible for it to "output" more rows than are in the underlying table. What could cause this? And better yet, any pointers on how to fix it?
[Bonus points for including something like "sp_updatestats fixes it every time but can't figure out how to fix it permanently? Go read this article." This has been a general problem for us on a few different fronts lately.]
sql-server optimization execution-plan azure-sql-database
sql-server optimization execution-plan azure-sql-database
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
edited Dec 12 '18 at 16:36
Todd Menier
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
asked Dec 12 '18 at 16:23
Todd MenierTodd Menier
31239
31239
Certainly: brentozar.com/pastetheplan/?id=BktRIhC1V
– Todd Menier
Dec 12 '18 at 16:27
60m row index seek in question is on ProductCatalog. I do see there's an index scan in the plan as well and I may look into it, but the "good" plan contains that too and time-wise it looks to be a non-factor in both cases.
– Todd Menier
Dec 12 '18 at 16:30
add a comment |
Certainly: brentozar.com/pastetheplan/?id=BktRIhC1V
– Todd Menier
Dec 12 '18 at 16:27
60m row index seek in question is on ProductCatalog. I do see there's an index scan in the plan as well and I may look into it, but the "good" plan contains that too and time-wise it looks to be a non-factor in both cases.
– Todd Menier
Dec 12 '18 at 16:30
Certainly: brentozar.com/pastetheplan/?id=BktRIhC1V
– Todd Menier
Dec 12 '18 at 16:27
Certainly: brentozar.com/pastetheplan/?id=BktRIhC1V
– Todd Menier
Dec 12 '18 at 16:27
60m row index seek in question is on ProductCatalog. I do see there's an index scan in the plan as well and I may look into it, but the "good" plan contains that too and time-wise it looks to be a non-factor in both cases.
– Todd Menier
Dec 12 '18 at 16:30
60m row index seek in question is on ProductCatalog. I do see there's an index scan in the plan as well and I may look into it, but the "good" plan contains that too and time-wise it looks to be a non-factor in both cases.
– Todd Menier
Dec 12 '18 at 16:30
add a comment |
1 Answer
1
active
oldest
votes
The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).
Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
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%2f224793%2fwhy-would-a-clustered-index-seek-return-a-higher-actual-number-of-rows-than-th%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
The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).
Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
add a comment |
The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).
Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
add a comment |
The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).
Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).
Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
edited Dec 12 '18 at 16:53
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
answered Dec 12 '18 at 16:41
ForrestForrest
2,1021619
2,1021619
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%2f224793%2fwhy-would-a-clustered-index-seek-return-a-higher-actual-number-of-rows-than-th%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
Certainly: brentozar.com/pastetheplan/?id=BktRIhC1V
– Todd Menier
Dec 12 '18 at 16:27
60m row index seek in question is on ProductCatalog. I do see there's an index scan in the plan as well and I may look into it, but the "good" plan contains that too and time-wise it looks to be a non-factor in both cases.
– Todd Menier
Dec 12 '18 at 16:30