Post reply

New cardinality estimator (SQL Server 2014) is way off

  • October 23, 2014 at 7:09 am

    #297692

    I have a data warehouse database and I'm facing problems with the new cardinality estimator of SQL Server 2014.

    After upgrading the database server to SQL Server 2014 I have observed a big difference in query performance. Some queries are executing much slower (30 sec in SQL 2012 vs. 5 minutes in SQL 2014). After researching execution plans I've seen that the cardinality estimates on the SQL Server 2014 are way off and I can't find a reason for it.

    Here's an example of a query execution plan in SQL 2012 vs. SQL 2014:

    Some details:

    My queries are typical data warehouse fact table load queries. I query a transactional table and join a lot (15-20) dimension tables (there's always either 0 or 1 record that is joined from the dimensional table).

    I have updated statistics of all tables (with FULLSCAN) to be sure that the statistics is up-to-date.

    The business keys of the dimension tables are indexed (unique non-clusted index). It seems to me that because of the uniqueness of this index the old cardinality estimator (SQL 2012) correctly assumes that there's max. 1 record that joins (the estimated number of records does not change in the execution plan).

    I tried to narrow down the issue to the simplest example – SELECT with 2 joins:

    Here's the cardinality estimation on operators 1 and 2 in SQL 2012 vs. SQL 2014:

    | Est.rows - SQL2012 | Est.rows - SQL2014

    Operator 1 | 7653 | 7653

    Operator 2 | 7653 | 10000

    As you can see, SQL Server 2014 misses the estimation by more than 30% (10000 vs. 7653). Because I have cca. 15-20 joins in a typical query, the final estimate goes way off.

    I can put the database in the lower compatibility mode (110) and it works fine then (same like on SQL Server 2012), but I would really like to know what is the reason for this behaviour. Why is the result of cardinality estimator of SQL Server 2014 so wrong?

  • October 23, 2014 at 8:50 am

    #1754369

    One of the big changes that was made in SQL Server 2014 was that the cardinality estimator was significantly changed. This resulted in behavior changes, both good and bad.

    Tom LaRock has written extensively on the changes in the CE and how to work with them. You can check out his blog here and here.

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

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