SQL Server 2014 Secret Killer New Feature, Cardinality Estimator (okay, it's not Secret, but it seems pretty killer...)

Jimmy May's Blog - MSDN Whitepaper: More Cowbell—Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Hot off the presses is this new MSDN white paper:

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

One of the gems introduced in SQL Server 2014 is the Cardinality Estimator (CE)—new! improved! & now with more cowbell.  I'm thrilled to be a Technical Reviewer for a superb MSDN white paper authored by my friend, buddy, & pal Joe Sack (b|t). It's exciting & humbling to see my name among such an array of Contributors & Reviewers—including several former colleagues from Azure CAT (formerly SQL CAT b|t).

What’s a CE?

As described on the Cardinality Estimation (SQL Server) page:

Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance.

Why a New CE?

The pre-existing CE is more than a decade old.  Both OLTP & DW workloads have changed—& databases are bigger by far than they used to be.  Often, cardinality changes spawned disparate plans (in one prototype, over 78 different plans were generated by the former CE).  Plainly & simply—the CE needed more cowbell.

What’s New?

During SQL14 TAP, SQL Engineer Kate Smith provided a heads up.  Highlights included:

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Summary: SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process, ultimately affecting average query execution plan quality and associated workload performance.  This paper provides an overview of the primary changes made to the cardinality estimator functionality by the Microsoft query processor team, covering how to enable and disable the new cardinality estimator behavior, and showing how to troubleshoot plan-quality regressions if and when they occur.

Authors: Joseph Sack (SQLskills.com)

Contributers: Yi Fang (Microsoft), Vassilis Papadimos (Microsoft)

Technical Reviewers: Barbara Kess (Microsoft), Jack Li (Microsoft), Jimmy May (Microsoft), Sanjay Mishra (Microsoft), Shep Sheppard (Microsoft), Mike Weiner (Microsoft), Paul White (SQL Kiwi Limited)

Introduction

The SQL Server Query Optimizer’s purpose is to find an efficient physical execution plan that fulfills a query request. It attempts this by assigning estimated costs to various query execution plan alternatives and then choosing the plan alternative with the lowest estimated cost. One key factor for determining operator cost is the estimation of rows that will be processed for each operator within a query execution plan. This row estimation process is commonly referred to as cardinality estimation. SQL Server 2014 marks the first, significant redesign of the SQL Server Query Optimizer cardinality estimation component since version SQL Server 7.0.

The SQL Server query optimization process seeks the most efficient processing strategy for executing queries across a wide variety of workloads. Achieving predictable query performance across online transaction processing (OLTP), relational data warehousing, and hybrid database schemas is inherently difficult. While many workloads will benefit from the new cardinality estimator changes, in some cases, workload performance may degrade without a specific tuning effort.

In this paper, we will discuss the fundamentals of the SQL Server 2014 cardinality estimator changes. We will provide details on activating and deactivating the new cardinality estimator. We will also provide troubleshooting guidance for scenarios where query performance degrades as a direct result of cardinality estimate issues.

...

Summary

SQL Server 2014 marks the first significant redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0. Use of the new CE can result in an overall improvement in average query performance for a wide range of application workloads. The new CE also provides diagnostic output for use in troubleshooting cardinality estimate issues. As described in this paper, some workloads may encounter degraded performance with the new CE. We recommend that you thoroughly test existing applications before migrating. When using the new CE, users can leverage trace flags to use the legacy model for queries that regress. This allows you to still benefit from queries that improved under the new model.

I've been hearing a number of good things about SQL Server 2014's new Cardinality Estimator, for example, The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast. If you've been hearing the same thing, or are just interested, you should check out this 43 page doc.