New Design for Cardinality Estimation

Next Post
By Admin at 20 Jun 2016, 15:59 PM
  • Cardinality estimation predetermines the number of ordered pairs returned by a query. It’s a fundamentally important task in data management, and used in query optimization, progress estimation, and resource supply.

    Before SQL Server 2014 was released, Cardinality Estimation had fewer and more restrictive ways for a query plan to be estimated. Typically, a single table query is straight forward: you can predict the number of rows you would get. In a simple example “ SELECT * FROM TABLE “, the rows would be the same as the number in the table. When you need to join two or more tables, the query estimation has to total up rows that will be returned while joining these tables, and it gets more complex when you add filters to the query.

    SQL Server 2014’s new design for Cardinality Estimation uses algorithms and assumptions to improve the quality of query plans.

    Assumptions of the model in previous version of SQL server (Pre 2014)

    • Independence
    • Uniformity
    • Containment
    • Inclusion

    Assumptions of the model in SQL server 2014

    • Independence becomes Correlation
    • Simple Containment becomes Base Containment

    Query Optimization in SQL Server

    The following points are considered when a Query Optimization occurs:

    • Get the lowest-cost option
    • Estimate the number of rows needed for the operation of the execution plan
    • The cost is an abstraction that holds no correlation to the performance
    • The start cost is based on optimization

    We considered the following areas for Cardinality estimation

    • ASC/DESC Key Estimation
    • Multiple column dependency
    • Join Estimates

    The cardinality estimator has a direct impact on improving the query performance. It will work on modern OLTP and data warehousing workloads.

    Areas covered inCardinality estimation

    • Auto Create/Update stats
    • Database compatibility mode
    • Query trace fags
    • XML show plan and XEvents

    Note: the new cardinality estimator is enabled for all new databases that are created in SQL Server 2014. However, this is not applicable for existing databases that are upgraded from an older version of SQL Server.

    To test thecardinality estimator for SQL Server 2012 databases:

    1. ALTER DATABASE <Database Name> SET COMPATIBILITY_LEVEL = 110;
    2. GO

    To enable the new cardinality estimator for SQL Server 2012 databases in SQL Server 2014:

    1. ALTER DATABASE <Database Name> SET COMPATIBILITY_LEVEL = 120;
    2. GO

    There are two ways to check the COMPATIBILITY LEVEL 110 in SQL Server 2014.

    • Set the whole database’s compatibility level back to 110:
    1. ALTER DATABASE <Database Name> SET COMPATIBILITY_LEVEL = 110;
    2. GO
    1. SELECT * FROM <Table Name>
    2. OPTION (QUERYTRACEON 9481)

    To force a few queries into the newer cardinality estimation in older compatibility level database, add OPTION (QUERYTRACEON 2312) to the very end of your query:

    1. SELECT * FROM <Table Name>
    2. OPTION (QUERYTRACEON 2312)

    The new cardinality estimator in SQL Server 2014 can be used in two ways:

    1. The new cardinality estimator can be utilized in the SQL Server 2014 XML plan. The value for the attribute “CardinalityEstimationModelVersion” is set as 120. This attribute only exists in version 2014 or later.
    2. The XEvent “query_optimizer_estimate_cardinality” is produced during compilation when the program uses the new cardinality estimator.

    Comments

     

    Post a comment

    Please correct the following: