SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Questions about Cardinality Estimator


Questions about Cardinality Estimator

Author
Message
SQL Guy 1
SQL Guy  1
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12750 Visits: 2759

Hi all,

I have some questions about Cardinality Estimator in newest versions of SQL Server. Our version is 2014 with a trace setting back to the old C.E.

1. Is C.E. is still an issue in versions 2016, 2017 ?
2. What types of queries are the most vulnerable to the new C.E. mishaps?


Thanks


Sue_H
Sue_H
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58133 Visits: 12789
SQL Guy 1 - Friday, December 22, 2017 7:57 AM

Hi all,

I have some questions about Cardinality Estimator in newest versions of SQL Server. Our version is 2014 with a trace setting back to the old C.E.

1. Is C.E. is still an issue in versions 2016, 2017 ?
2. What types of queries are the most vulnerable to the new C.E. mishaps?


Thanks



There isn't necessarily an issue as much as the CE changed a lot starting with SQL Server 2014. I don't believe there is a plan to revert back to the old CE - it's still used in 2016 and 2017. Many queries will run faster, some run the same and there are some that run slower. Microsoft wrote in the CE documentation that the queries they feel would be most susceptible to issues -
An OLTP (online transaction processing) query that runs so frequently that multiple instance of it often run concurrently.
A SELECT with substantial aggregation that runs during your OLTP business hours.
Refer to this documentation:
Cardinality Estimation (SQL Server)

I would think that the goal would be to rewrite queries affected by the changes rather continue with the old CE. The article above has troubleshooting advice using things such as the query store, utilizing query_optimizer_estimate_cardinality in XE, etc
In addition to the article above, there is a white paper on optimizing queries with the new CE. You can download it from this link:
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Sue



Steve Jones
Steve Jones
SSC Guru
SSC Guru (535K reputation)SSC Guru (535K reputation)SSC Guru (535K reputation)SSC Guru (535K reputation)SSC Guru (535K reputation)SSC Guru (535K reputation)SSC Guru (535K reputation)SSC Guru (535K reputation)

Group: Administrators
Points: 535956 Visits: 20702
As Sue noted, the CE is the CE now. You can go backwards with trace flags, but you should get used to the new CE being the one that is available.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search