Skip to main content
Commonmark migration
Source Link

I try my best to explain what can do in this situation base on your question information, and I hope help you.

When moving to the new CE, you can expect that some query execution plans will remain the same and some will change. Neither condition inherently suggests an issue.

enter image description here

Base on the Microsoft Link and white paper wrote by Joseph Sack (SQLskills.com) I think you have to pay attention to this items that wrote in the white paper in Page 18

To review the document, please download the Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator Word document.

What Actions can You Take if You See a Plan Regression?

 

Consider the following actions if you have encountered performance degradation directly caused by the new CE:

 

• Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods. For example, for a relational data warehouse query with significant fact-table cardinality estimate skews, consider moving to a columnstore index solution. In this scenario, you retain the cardinality estimate skew. However, the compensating performance improvement of a columnstore index could remove the performance degradation from the legacy version.

 

• Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE. This may be appropriate for tests where only a few queries in a workload had performance degradation caused by the new CE.

 

• Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE. This may be appropriate for tests where only a few queries in the workload had improved performance.

 

• Use fundamental cardinality estimate skew troubleshooting methods. This option does not address the original root cause. However, using fundamental cardinality estimation troubleshooting methods might address an overall estimate skew problem and improve query performance.

 

• Revert to the legacy CE entirely. This might be appropriate if multiple, critical queries encounter performance regressions and you do not have time to test and redesign around the issues.

Conclusion I think base of these items you have to change some parameters,queries,set some flags and etc , i don't think have shortcut solution like set special parameters and suddenly everything is ok!, I hope its help you.

I try my best to explain what can do in this situation base on your question information, and I hope help you.

When moving to the new CE, you can expect that some query execution plans will remain the same and some will change. Neither condition inherently suggests an issue.

enter image description here

Base on the Microsoft Link and white paper wrote by Joseph Sack (SQLskills.com) I think you have to pay attention to this items that wrote in the white paper in Page 18

To review the document, please download the Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator Word document.

What Actions can You Take if You See a Plan Regression?

 

Consider the following actions if you have encountered performance degradation directly caused by the new CE:

 

• Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods. For example, for a relational data warehouse query with significant fact-table cardinality estimate skews, consider moving to a columnstore index solution. In this scenario, you retain the cardinality estimate skew. However, the compensating performance improvement of a columnstore index could remove the performance degradation from the legacy version.

 

• Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE. This may be appropriate for tests where only a few queries in a workload had performance degradation caused by the new CE.

 

• Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE. This may be appropriate for tests where only a few queries in the workload had improved performance.

 

• Use fundamental cardinality estimate skew troubleshooting methods. This option does not address the original root cause. However, using fundamental cardinality estimation troubleshooting methods might address an overall estimate skew problem and improve query performance.

 

• Revert to the legacy CE entirely. This might be appropriate if multiple, critical queries encounter performance regressions and you do not have time to test and redesign around the issues.

Conclusion I think base of these items you have to change some parameters,queries,set some flags and etc , i don't think have shortcut solution like set special parameters and suddenly everything is ok!, I hope its help you.

I try my best to explain what can do in this situation base on your question information, and I hope help you.

When moving to the new CE, you can expect that some query execution plans will remain the same and some will change. Neither condition inherently suggests an issue.

enter image description here

Base on the Microsoft Link and white paper wrote by Joseph Sack (SQLskills.com) I think you have to pay attention to this items that wrote in the white paper in Page 18

To review the document, please download the Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator Word document.

What Actions can You Take if You See a Plan Regression?

Consider the following actions if you have encountered performance degradation directly caused by the new CE:

• Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods. For example, for a relational data warehouse query with significant fact-table cardinality estimate skews, consider moving to a columnstore index solution. In this scenario, you retain the cardinality estimate skew. However, the compensating performance improvement of a columnstore index could remove the performance degradation from the legacy version.

• Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE. This may be appropriate for tests where only a few queries in a workload had performance degradation caused by the new CE.

• Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE. This may be appropriate for tests where only a few queries in the workload had improved performance.

• Use fundamental cardinality estimate skew troubleshooting methods. This option does not address the original root cause. However, using fundamental cardinality estimation troubleshooting methods might address an overall estimate skew problem and improve query performance.

• Revert to the legacy CE entirely. This might be appropriate if multiple, critical queries encounter performance regressions and you do not have time to test and redesign around the issues.

Conclusion I think base of these items you have to change some parameters,queries,set some flags and etc , i don't think have shortcut solution like set special parameters and suddenly everything is ok!, I hope its help you.

Source Link

I try my best to explain what can do in this situation base on your question information, and I hope help you.

When moving to the new CE, you can expect that some query execution plans will remain the same and some will change. Neither condition inherently suggests an issue.

enter image description here

Base on the Microsoft Link and white paper wrote by Joseph Sack (SQLskills.com) I think you have to pay attention to this items that wrote in the white paper in Page 18

To review the document, please download the Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator Word document.

What Actions can You Take if You See a Plan Regression?

Consider the following actions if you have encountered performance degradation directly caused by the new CE:

• Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods. For example, for a relational data warehouse query with significant fact-table cardinality estimate skews, consider moving to a columnstore index solution. In this scenario, you retain the cardinality estimate skew. However, the compensating performance improvement of a columnstore index could remove the performance degradation from the legacy version.

• Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE. This may be appropriate for tests where only a few queries in a workload had performance degradation caused by the new CE.

• Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE. This may be appropriate for tests where only a few queries in the workload had improved performance.

• Use fundamental cardinality estimate skew troubleshooting methods. This option does not address the original root cause. However, using fundamental cardinality estimation troubleshooting methods might address an overall estimate skew problem and improve query performance.

• Revert to the legacy CE entirely. This might be appropriate if multiple, critical queries encounter performance regressions and you do not have time to test and redesign around the issues.

Conclusion I think base of these items you have to change some parameters,queries,set some flags and etc , i don't think have shortcut solution like set special parameters and suddenly everything is ok!, I hope its help you.