Post reply

Questions about Cardinality Estimator

  • December 22, 2017 at 7:57 am

    #334171

    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

  • December 22, 2017 at 3:04 pm

    #1973304

    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

  • December 26, 2017 at 8:57 am

    #1973466

    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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply