SQL Server 2014 Cardinality Estimator

Cardinality Estimator

Microsoft SQL Server has implemented a significant change to the database engine’s Optimizer with the introduction of an improved Cardinality Estimator in version 2014. The Optimizer component has not changed since version 7 (about 15 years). This represents a significant update and can potentially provide your environment with a huge performance boost.

In a nutshell, the areas where improvements have been made include:

  • Ascending key estimation
  • Join estimates
  • Predicate estimation

There are other improvements in addition to these listed above. The Microsoft SQL Server white paper* referenced at the end of this blog post, gives an excellent explanation of all the improvements.   I highly recommend every SQL Server DBA to give it a read.

You may be asking, what is the “Cardinality Estimator”? Basically, cardinality estimation is what SQL Server is calling the process of estimating the number of rows/records when optimizing your query. Microsoft SQL Server’s Optimizer relies heavily on operator and row estimations to determine the best query plan to use. With the release of SQL Server 2014, Microsoft has updated their internal optimizer algorithms to improve record estimations to determine the most optimal and “cheapest” execution plan.

 

The Optimizer (Extreme Overview)

When submitting a query, the SQL Server Optimizer follows this basic process: Parsing -> Binding -> Optimization -> Execution -> Results. Optimization is the step where execution plans are evaluated and also where the Cardinality Estimator comes in to play.

SQL Server evaluates your query to determine the best and most efficient execution plan based on data estimation. Data estimation is based on statistics created on the tables involved in the query. Since SQL Server’s Optimizer is a cost-based optimizer, these estimations are critical to help determine the “plan cost”.  This is why the current state of statistics on your tables (which includes indexes) is very important. The new Cardinality Estimator has been improved to help deal with out-of-date statistics in certain situations.

 

Should You Upgrade?

With SQL Server 2014, most queries should show an overall improvement in query performance with the new Cardinality Estimator compared to the legacy version.  Performance on some queries may stay the same.  There may be cases when the opposite can be true and have worse performance. In these cases, you have the ability to revert to the legacy optimizer by either setting the database compatibility version or specifying special trace flags for specific queries.

Thorough testing is recommended before migrating existing SQL Server code or application code from previous SQL Server versions to the new optimizer in SQL Server 2014.  Don’t know where to start?  Here’s a high-level guideline:

  • Identify critical SQL queries and processes in your existing SQL Server Production environment.
  • Document existing Production performance statistics.
  • Install and configure a SQL Server 2014 Test environment with copies of your existing Production databases.  If possible, configure additional test environments for applications, reporting and other critical processes to interact with the SQL Server 2014 Test environment.
  • Execute your existing code in the new Test environment and document performance statistics.  Attempt to simulate existing processes as much as possible.  For example:  Application scenarios, Critical Reports, Imports, Archiving, Updates, etc.
  • Compare your performance results between Production and the SQL Server 2014 Test environment.

Once you are armed with performance data, you can make a confident decision.

 

References:
“Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator”
By |2017-09-20T08:27:01+00:00October 22nd, 2014|Performance Wire|Comments Off on SQL Server 2014 Cardinality Estimator