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.
Query is:
https://pastebin.com/fefxXgND
SELECT DISTINCT t_08.puid FROM PWORKSPACEOBJECT t_06 ,
PRELEASE_STATUS_LIST t_07 , PITEMREVISION t_08 , PRELEASESTATUS t_02 ,
PE9_MANUFACTURERSTORAGE t_09 , PEN_ITEMREVMASTER t_01 , PFORM t_05 ,
PIMANRELATION t_03 , PIMANRELATION t_04 WHERE ( (
UPPER(t_06.pobject_type) IN ( UPPER( 'EN_Item Revision' ) , UPPER(
'EN_Item Revision' ) ) AND ( ( ( t_07.pvalu_0 = t_02.puid ) AND
UPPER(t_02.pname) IN ( UPPER( 'V' ) , UPPER( 'E9_D' ) , UPPER( 'F'
) , UPPER( 'E9_to_F' ) , UPPER( 'E9_M' ) ) ) AND ( ( ( t_08.puid =
t_03.rprimary_objectu ) AND ( ( t_03.rrelation_typeu =
'gMx8h03uVJFL2B' ) AND ( ( t_03.rsecondary_objectu = t_05.puid ) AND (
( t_05.rdata_fileu = t_01.puid ) AND ( ( t_01.re9_manufactureru =
t_09.puid ) AND ( UPPER(t_09.pe9_id) = UPPER( '7300007' ) ) ) ) )
) ) AND ( ( t_08.puid = t_04.rprimary_objectu ) AND ( (
t_04.rrelation_typeu = 'gMx8h03uVJFL2B' ) AND ( (
t_04.rsecondary_objectu = t_05.puid ) AND ( ( t_05.rdata_fileu =
t_01.puid ) AND ( UPPER(t_01.phersteller_artikelnr) = UPPER(
'00100893' ) ) ) ) ) ) ) ) ) AND ( t_06.puid = t_07.puid AND
t_07.puid = t_08.puid ) );
generated by the 4 Tier Application we are using.
1s vs 47s. Both of these DBs share the same SQL Instance. The weird thing is that it might be instant as well if we reset the plan cache. But any how, it slows down again by a complete mess of misscalculation in the optimizer.
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 execution-plan
asked 8 mins ago
KrautmasterKrautmaster
1
New contributor
Krautmaster 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 |
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.
Query is:
https://pastebin.com/fefxXgND
SELECT DISTINCT t_08.puid FROM PWORKSPACEOBJECT t_06 ,
PRELEASE_STATUS_LIST t_07 , PITEMREVISION t_08 , PRELEASESTATUS t_02 ,
PE9_MANUFACTURERSTORAGE t_09 , PEN_ITEMREVMASTER t_01 , PFORM t_05 ,
PIMANRELATION t_03 , PIMANRELATION t_04 WHERE ( (
UPPER(t_06.pobject_type) IN ( UPPER( 'EN_Item Revision' ) , UPPER(
'EN_Item Revision' ) ) AND ( ( ( t_07.pvalu_0 = t_02.puid ) AND
UPPER(t_02.pname) IN ( UPPER( 'V' ) , UPPER( 'E9_D' ) , UPPER( 'F'
) , UPPER( 'E9_to_F' ) , UPPER( 'E9_M' ) ) ) AND ( ( ( t_08.puid =
t_03.rprimary_objectu ) AND ( ( t_03.rrelation_typeu =
'gMx8h03uVJFL2B' ) AND ( ( t_03.rsecondary_objectu = t_05.puid ) AND (
( t_05.rdata_fileu = t_01.puid ) AND ( ( t_01.re9_manufactureru =
t_09.puid ) AND ( UPPER(t_09.pe9_id) = UPPER( '7300007' ) ) ) ) )
) ) AND ( ( t_08.puid = t_04.rprimary_objectu ) AND ( (
t_04.rrelation_typeu = 'gMx8h03uVJFL2B' ) AND ( (
t_04.rsecondary_objectu = t_05.puid ) AND ( ( t_05.rdata_fileu =
t_01.puid ) AND ( UPPER(t_01.phersteller_artikelnr) = UPPER(
'00100893' ) ) ) ) ) ) ) ) ) AND ( t_06.puid = t_07.puid AND
t_07.puid = t_08.puid ) );
generated by the 4 Tier Application we are using.
1s vs 47s. Both of these DBs share the same SQL Instance. The weird thing is that it might be instant as well if we reset the plan cache. But any how, it slows down again by a complete mess of misscalculation in the optimizer.
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 execution-plan
asked 8 mins ago
KrautmasterKrautmaster
1
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Can you please upload the plans to PasteThePlan.com and replace your screenshots? The screenshots don't contain any of the useful meta data needed to properly diagnose the problem.
– George.Palacios
4 mins ago
thx, give me a min and they will be there.
– Krautmaster
3 mins ago
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.
Query is:
https://pastebin.com/fefxXgND
SELECT DISTINCT t_08.puid FROM PWORKSPACEOBJECT t_06 ,
PRELEASE_STATUS_LIST t_07 , PITEMREVISION t_08 , PRELEASESTATUS t_02 ,
PE9_MANUFACTURERSTORAGE t_09 , PEN_ITEMREVMASTER t_01 , PFORM t_05 ,
PIMANRELATION t_03 , PIMANRELATION t_04 WHERE ( (
UPPER(t_06.pobject_type) IN ( UPPER( 'EN_Item Revision' ) , UPPER(
'EN_Item Revision' ) ) AND ( ( ( t_07.pvalu_0 = t_02.puid ) AND
UPPER(t_02.pname) IN ( UPPER( 'V' ) , UPPER( 'E9_D' ) , UPPER( 'F'
) , UPPER( 'E9_to_F' ) , UPPER( 'E9_M' ) ) ) AND ( ( ( t_08.puid =
t_03.rprimary_objectu ) AND ( ( t_03.rrelation_typeu =
'gMx8h03uVJFL2B' ) AND ( ( t_03.rsecondary_objectu = t_05.puid ) AND (
( t_05.rdata_fileu = t_01.puid ) AND ( ( t_01.re9_manufactureru =
t_09.puid ) AND ( UPPER(t_09.pe9_id) = UPPER( '7300007' ) ) ) ) )
) ) AND ( ( t_08.puid = t_04.rprimary_objectu ) AND ( (
t_04.rrelation_typeu = 'gMx8h03uVJFL2B' ) AND ( (
t_04.rsecondary_objectu = t_05.puid ) AND ( ( t_05.rdata_fileu =
t_01.puid ) AND ( UPPER(t_01.phersteller_artikelnr) = UPPER(
'00100893' ) ) ) ) ) ) ) ) ) AND ( t_06.puid = t_07.puid AND
t_07.puid = t_08.puid ) );
generated by the 4 Tier Application we are using.
1s vs 47s. Both of these DBs share the same SQL Instance. The weird thing is that it might be instant as well if we reset the plan cache. But any how, it slows down again by a complete mess of misscalculation in the optimizer.
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 execution-plan
asked 8 mins ago
KrautmasterKrautmaster
1
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.
Query is:
https://pastebin.com/fefxXgND
SELECT DISTINCT t_08.puid FROM PWORKSPACEOBJECT t_06 ,
PRELEASE_STATUS_LIST t_07 , PITEMREVISION t_08 , PRELEASESTATUS t_02 ,
PE9_MANUFACTURERSTORAGE t_09 , PEN_ITEMREVMASTER t_01 , PFORM t_05 ,
PIMANRELATION t_03 , PIMANRELATION t_04 WHERE ( (
UPPER(t_06.pobject_type) IN ( UPPER( 'EN_Item Revision' ) , UPPER(
'EN_Item Revision' ) ) AND ( ( ( t_07.pvalu_0 = t_02.puid ) AND
UPPER(t_02.pname) IN ( UPPER( 'V' ) , UPPER( 'E9_D' ) , UPPER( 'F'
) , UPPER( 'E9_to_F' ) , UPPER( 'E9_M' ) ) ) AND ( ( ( t_08.puid =
t_03.rprimary_objectu ) AND ( ( t_03.rrelation_typeu =
'gMx8h03uVJFL2B' ) AND ( ( t_03.rsecondary_objectu = t_05.puid ) AND (
( t_05.rdata_fileu = t_01.puid ) AND ( ( t_01.re9_manufactureru =
t_09.puid ) AND ( UPPER(t_09.pe9_id) = UPPER( '7300007' ) ) ) ) )
) ) AND ( ( t_08.puid = t_04.rprimary_objectu ) AND ( (
t_04.rrelation_typeu = 'gMx8h03uVJFL2B' ) AND ( (
t_04.rsecondary_objectu = t_05.puid ) AND ( ( t_05.rdata_fileu =
t_01.puid ) AND ( UPPER(t_01.phersteller_artikelnr) = UPPER(
'00100893' ) ) ) ) ) ) ) ) ) AND ( t_06.puid = t_07.puid AND
t_07.puid = t_08.puid ) );
generated by the 4 Tier Application we are using.
1s vs 47s. Both of these DBs share the same SQL Instance. The weird thing is that it might be instant as well if we reset the plan cache. But any how, it slows down again by a complete mess of misscalculation in the optimizer.
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 execution-plan
sql-server execution-plan
asked 8 mins ago
KrautmasterKrautmaster
1
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
KrautmasterKrautmaster
1
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
KrautmasterKrautmaster
1
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
KrautmasterKrautmaster
1
asked 8 mins ago
KrautmasterKrautmaster
1
1
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Krautmaster is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Can you please upload the plans to PasteThePlan.com and replace your screenshots? The screenshots don't contain any of the useful meta data needed to properly diagnose the problem.
– George.Palacios
4 mins ago
thx, give me a min and they will be there.
– Krautmaster
3 mins ago
add a comment |
Can you please upload the plans to PasteThePlan.com and replace your screenshots? The screenshots don't contain any of the useful meta data needed to properly diagnose the problem.
– George.Palacios
4 mins ago
thx, give me a min and they will be there.
– Krautmaster
3 mins ago
Can you please upload the plans to PasteThePlan.com and replace your screenshots? The screenshots don't contain any of the useful meta data needed to properly diagnose the problem.
– George.Palacios
4 mins ago
Can you please upload the plans to PasteThePlan.com and replace your screenshots? The screenshots don't contain any of the useful meta data needed to properly diagnose the problem.
– George.Palacios
4 mins ago
thx, give me a min and they will be there.
– Krautmaster
3 mins ago
thx, give me a min and they will be there.
– Krautmaster
3 mins ago
add a comment |
0
active
oldest
votes
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
});
}
});
Krautmaster 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%2f227946%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
Krautmaster is a new contributor. Be nice, and check out our Code of Conduct.
Krautmaster is a new contributor. Be nice, and check out our Code of Conduct.
Krautmaster is a new contributor. Be nice, and check out our Code of Conduct.
Krautmaster 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%2f227946%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
Can you please upload the plans to PasteThePlan.com and replace your screenshots? The screenshots don't contain any of the useful meta data needed to properly diagnose the problem.
– George.Palacios
4 mins ago
thx, give me a min and they will be there.
– Krautmaster
3 mins ago