Random insane bad execution plans in Microsoft SQL Server
Hi together I know that issue has been discussed for several times, but we face an insane weird issue on our new SQL Server 2014 SP3 right now, and we just can't get behind.
We switched over from Oracle to MS SQL Server last year. Our mayor DB is quite big, abt. 800GB, big tables, PDM system. +1000 active users.
16 cores, 192 GB Memory, SSD SAN Storage. ESX 6.5
Settings in SQL Server:
-> Create Auto Statistics Enabled
-> optimize for Ad Hoc Queries = true
-> Snapshot Isolation enabled
-> Max Parallel = 4
-> Threshold 50
-> Statistics Updates async in TempDB, normally enabled on our main DBs.
Any how, we have some queries which are handled extremly bad. All leads to the fact that the SQl Server Optimizer creates an execution plan, thinks its okay but on execution it does an inner join (or multiples) with Millions instead of expected 1-2 rows. And of cours millions of logical reads. And I can't get behind whats ongoing in this. These statements ran minutes then.
So basically, we have already 3 databases. All same version and hardware, Prod Test and Dev Environment. My tests can be done pretty easily. All dbs are configured the same and show the same behaviour, but on different queries. Lets say somethimes the test environment does the same statement multipletimes in 47s, the prod db in just a second. Others take seconds in Prod, are instand in Test. What the heck is going on? I always retry the statements muliple times to ensure its cached.
Good Case
Bad Case
1s vs 47s. Both of these DBs share the same SQL Instance.
Any ideas what could have been wrong here? How to correct single execution plans? How can it be that the sql server doenst learn from that extremly bad statement and to correct it the next time it runs?
Thanks for your help and ideas.
sql-server sql
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
add a comment |
Hi together I know that issue has been discussed for several times, but we face an insane weird issue on our new SQL Server 2014 SP3 right now, and we just can't get behind.
We switched over from Oracle to MS SQL Server last year. Our mayor DB is quite big, abt. 800GB, big tables, PDM system. +1000 active users.
16 cores, 192 GB Memory, SSD SAN Storage. ESX 6.5
Settings in SQL Server:
-> Create Auto Statistics Enabled
-> optimize for Ad Hoc Queries = true
-> Snapshot Isolation enabled
-> Max Parallel = 4
-> Threshold 50
-> Statistics Updates async in TempDB, normally enabled on our main DBs.
Any how, we have some queries which are handled extremly bad. All leads to the fact that the SQl Server Optimizer creates an execution plan, thinks its okay but on execution it does an inner join (or multiples) with Millions instead of expected 1-2 rows. And of cours millions of logical reads. And I can't get behind whats ongoing in this. These statements ran minutes then.
So basically, we have already 3 databases. All same version and hardware, Prod Test and Dev Environment. My tests can be done pretty easily. All dbs are configured the same and show the same behaviour, but on different queries. Lets say somethimes the test environment does the same statement multipletimes in 47s, the prod db in just a second. Others take seconds in Prod, are instand in Test. What the heck is going on? I always retry the statements muliple times to ensure its cached.
Good Case
Bad Case
1s vs 47s. Both of these DBs share the same SQL Instance.
Any ideas what could have been wrong here? How to correct single execution plans? How can it be that the sql server doenst learn from that extremly bad statement and to correct it the next time it runs?
Thanks for your help and ideas.
sql-server sql
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
1
Did you try to use a modern join syntax instead of the old school Oracle one?
– Seth
Jan 24 at 6:48
1
Consider asking on Database Administrators instead. Super User isn't really the best place for this question. You can flag your own question to request migration.
– Bob
Jan 24 at 7:38
add a comment |
Hi together I know that issue has been discussed for several times, but we face an insane weird issue on our new SQL Server 2014 SP3 right now, and we just can't get behind.
We switched over from Oracle to MS SQL Server last year. Our mayor DB is quite big, abt. 800GB, big tables, PDM system. +1000 active users.
16 cores, 192 GB Memory, SSD SAN Storage. ESX 6.5
Settings in SQL Server:
-> Create Auto Statistics Enabled
-> optimize for Ad Hoc Queries = true
-> Snapshot Isolation enabled
-> Max Parallel = 4
-> Threshold 50
-> Statistics Updates async in TempDB, normally enabled on our main DBs.
Any how, we have some queries which are handled extremly bad. All leads to the fact that the SQl Server Optimizer creates an execution plan, thinks its okay but on execution it does an inner join (or multiples) with Millions instead of expected 1-2 rows. And of cours millions of logical reads. And I can't get behind whats ongoing in this. These statements ran minutes then.
So basically, we have already 3 databases. All same version and hardware, Prod Test and Dev Environment. My tests can be done pretty easily. All dbs are configured the same and show the same behaviour, but on different queries. Lets say somethimes the test environment does the same statement multipletimes in 47s, the prod db in just a second. Others take seconds in Prod, are instand in Test. What the heck is going on? I always retry the statements muliple times to ensure its cached.
Good Case
Bad Case
1s vs 47s. Both of these DBs share the same SQL Instance.
Any ideas what could have been wrong here? How to correct single execution plans? How can it be that the sql server doenst learn from that extremly bad statement and to correct it the next time it runs?
Thanks for your help and ideas.
sql-server sql
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
Hi together I know that issue has been discussed for several times, but we face an insane weird issue on our new SQL Server 2014 SP3 right now, and we just can't get behind.
We switched over from Oracle to MS SQL Server last year. Our mayor DB is quite big, abt. 800GB, big tables, PDM system. +1000 active users.
16 cores, 192 GB Memory, SSD SAN Storage. ESX 6.5
Settings in SQL Server:
-> Create Auto Statistics Enabled
-> optimize for Ad Hoc Queries = true
-> Snapshot Isolation enabled
-> Max Parallel = 4
-> Threshold 50
-> Statistics Updates async in TempDB, normally enabled on our main DBs.
Any how, we have some queries which are handled extremly bad. All leads to the fact that the SQl Server Optimizer creates an execution plan, thinks its okay but on execution it does an inner join (or multiples) with Millions instead of expected 1-2 rows. And of cours millions of logical reads. And I can't get behind whats ongoing in this. These statements ran minutes then.
So basically, we have already 3 databases. All same version and hardware, Prod Test and Dev Environment. My tests can be done pretty easily. All dbs are configured the same and show the same behaviour, but on different queries. Lets say somethimes the test environment does the same statement multipletimes in 47s, the prod db in just a second. Others take seconds in Prod, are instand in Test. What the heck is going on? I always retry the statements muliple times to ensure its cached.
Good Case
Bad Case
1s vs 47s. Both of these DBs share the same SQL Instance.
Any ideas what could have been wrong here? How to correct single execution plans? How can it be that the sql server doenst learn from that extremly bad statement and to correct it the next time it runs?
Thanks for your help and ideas.
sql-server sql
sql-server sql
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
asked Jan 24 at 6:37
KrautmasterKrautmaster
61
61
1
Did you try to use a modern join syntax instead of the old school Oracle one?
– Seth
Jan 24 at 6:48
1
Consider asking on Database Administrators instead. Super User isn't really the best place for this question. You can flag your own question to request migration.
– Bob
Jan 24 at 7:38
add a comment |
1
Did you try to use a modern join syntax instead of the old school Oracle one?
– Seth
Jan 24 at 6:48
1
Consider asking on Database Administrators instead. Super User isn't really the best place for this question. You can flag your own question to request migration.
– Bob
Jan 24 at 7:38
1
1
Did you try to use a modern join syntax instead of the old school Oracle one?
– Seth
Jan 24 at 6:48
Did you try to use a modern join syntax instead of the old school Oracle one?
– Seth
Jan 24 at 6:48
1
1
Consider asking on Database Administrators instead. Super User isn't really the best place for this question. You can flag your own question to request migration.
– Bob
Jan 24 at 7:38
Consider asking on Database Administrators instead. Super User isn't really the best place for this question. You can flag your own question to request migration.
– Bob
Jan 24 at 7:38
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fsuperuser.com%2fquestions%2f1397783%2frandom-insane-bad-execution-plans-in-microsoft-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1397783%2frandom-insane-bad-execution-plans-in-microsoft-sql-server%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
1
Did you try to use a modern join syntax instead of the old school Oracle one?
– Seth
Jan 24 at 6:48
1
Consider asking on Database Administrators instead. Super User isn't really the best place for this question. You can flag your own question to request migration.
– Bob
Jan 24 at 7:38