Different plans on Readonly Secondary Replica
We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.
One of the reports intermittently runs for much longer than normal. I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.
However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.
Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?
The servers have exactly the same hardware and the configuration is the same.
sql-server query-performance sql-server-2014 optimization cardinality-estimates
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
bumped to the homepage by Community♦ 23 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.
One of the reports intermittently runs for much longer than normal. I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.
However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.
Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?
The servers have exactly the same hardware and the configuration is the same.
sql-server query-performance sql-server-2014 optimization cardinality-estimates
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
bumped to the homepage by Community♦ 23 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are there any temporary stats on the secondary that it may be using?use MyDB; go; SELECT * FROM sys.stats where is_temporary = 1;
– Sean Gallardy
Feb 5 '17 at 1:21
Yeah, thats the bit i am confused about. When I do SELECT * FROM sys.stats where is_temporary = 1 and object_id=object_id('tablename') I find a few but non that ends with suffix _readonly_database_statistic. So what does that mean. How are they temporary without having the suffix _readonly_database_statistic
– jesijesi
Feb 5 '17 at 1:27
This sounds like a possible bug around temporary statistics on readonly secondaries. Perhaps file a bug report on connect.microsoft.com - also, see duckduckgo.com/… for several possibly related issues.
– Max Vernon
Feb 14 '17 at 18:22
add a comment |
We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.
One of the reports intermittently runs for much longer than normal. I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.
However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.
Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?
The servers have exactly the same hardware and the configuration is the same.
sql-server query-performance sql-server-2014 optimization cardinality-estimates
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.
One of the reports intermittently runs for much longer than normal. I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.
However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.
Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?
The servers have exactly the same hardware and the configuration is the same.
sql-server query-performance sql-server-2014 optimization cardinality-estimates
sql-server query-performance sql-server-2014 optimization cardinality-estimates
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
edited Feb 14 '17 at 17:54
Max Vernon
50.2k13111220
50.2k13111220
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
asked Feb 5 '17 at 0:16
jesijesijesijesi
878419
878419
bumped to the homepage by Community♦ 23 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 23 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are there any temporary stats on the secondary that it may be using?use MyDB; go; SELECT * FROM sys.stats where is_temporary = 1;
– Sean Gallardy
Feb 5 '17 at 1:21
Yeah, thats the bit i am confused about. When I do SELECT * FROM sys.stats where is_temporary = 1 and object_id=object_id('tablename') I find a few but non that ends with suffix _readonly_database_statistic. So what does that mean. How are they temporary without having the suffix _readonly_database_statistic
– jesijesi
Feb 5 '17 at 1:27
This sounds like a possible bug around temporary statistics on readonly secondaries. Perhaps file a bug report on connect.microsoft.com - also, see duckduckgo.com/… for several possibly related issues.
– Max Vernon
Feb 14 '17 at 18:22
add a comment |
Are there any temporary stats on the secondary that it may be using?use MyDB; go; SELECT * FROM sys.stats where is_temporary = 1;
– Sean Gallardy
Feb 5 '17 at 1:21
Yeah, thats the bit i am confused about. When I do SELECT * FROM sys.stats where is_temporary = 1 and object_id=object_id('tablename') I find a few but non that ends with suffix _readonly_database_statistic. So what does that mean. How are they temporary without having the suffix _readonly_database_statistic
– jesijesi
Feb 5 '17 at 1:27
This sounds like a possible bug around temporary statistics on readonly secondaries. Perhaps file a bug report on connect.microsoft.com - also, see duckduckgo.com/… for several possibly related issues.
– Max Vernon
Feb 14 '17 at 18:22
Are there any temporary stats on the secondary that it may be using?
use MyDB; go; SELECT * FROM sys.stats where is_temporary = 1;– Sean Gallardy
Feb 5 '17 at 1:21
Are there any temporary stats on the secondary that it may be using?
use MyDB; go; SELECT * FROM sys.stats where is_temporary = 1;– Sean Gallardy
Feb 5 '17 at 1:21
Yeah, thats the bit i am confused about. When I do SELECT * FROM sys.stats where is_temporary = 1 and object_id=object_id('tablename') I find a few but non that ends with suffix _readonly_database_statistic. So what does that mean. How are they temporary without having the suffix _readonly_database_statistic
– jesijesi
Feb 5 '17 at 1:27
Yeah, thats the bit i am confused about. When I do SELECT * FROM sys.stats where is_temporary = 1 and object_id=object_id('tablename') I find a few but non that ends with suffix _readonly_database_statistic. So what does that mean. How are they temporary without having the suffix _readonly_database_statistic
– jesijesi
Feb 5 '17 at 1:27
This sounds like a possible bug around temporary statistics on readonly secondaries. Perhaps file a bug report on connect.microsoft.com - also, see duckduckgo.com/… for several possibly related issues.
– Max Vernon
Feb 14 '17 at 18:22
This sounds like a possible bug around temporary statistics on readonly secondaries. Perhaps file a bug report on connect.microsoft.com - also, see duckduckgo.com/… for several possibly related issues.
– Max Vernon
Feb 14 '17 at 18:22
add a comment |
1 Answer
1
active
oldest
votes
It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.
Details here:
FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
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%2f163239%2fdifferent-plans-on-readonly-secondary-replica%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
It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.
Details here:
FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
add a comment |
It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.
Details here:
FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
add a comment |
It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.
Details here:
FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.
Details here:
FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
edited Jul 5 '17 at 11:48
SqlWorldWide
8,04521335
8,04521335
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
answered Jul 5 '17 at 10:02
AhmadYaseenAhmadYaseen
1
1
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%2f163239%2fdifferent-plans-on-readonly-secondary-replica%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
Are there any temporary stats on the secondary that it may be using?
use MyDB; go; SELECT * FROM sys.stats where is_temporary = 1;– Sean Gallardy
Feb 5 '17 at 1:21
Yeah, thats the bit i am confused about. When I do SELECT * FROM sys.stats where is_temporary = 1 and object_id=object_id('tablename') I find a few but non that ends with suffix _readonly_database_statistic. So what does that mean. How are they temporary without having the suffix _readonly_database_statistic
– jesijesi
Feb 5 '17 at 1:27
This sounds like a possible bug around temporary statistics on readonly secondaries. Perhaps file a bug report on connect.microsoft.com - also, see duckduckgo.com/… for several possibly related issues.
– Max Vernon
Feb 14 '17 at 18:22