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:
Relaxing Independence Assumption: The old CE assumed that column values were independent. Yet columns such as City and State, or Manufacturer, Make, and Model are tightly correlated. Algorithms in the new CE better account for this.
Join Changes: Improvements to equijoins, non-equijoins, & join estimates related to primary keys.
Ascending Key Modifications: Newly inserted data are out of the range in histogram. The new CE assumes not only that the data actually does exist & also is present at the average frequency of values in the table. (And the same heuristics also apply to missing values in sample statistics.)
In other words, more cowbell.
Joe provides numerous examples & walk-throughs detailing the behavior of the new CE.
Inside Baseball
Here’s some behind-the-scenes info. "Cardinality Estimator" didn't appear in the original title which referred merely to performance tuning. Who wouldn’t want to read a perf paper from Joe? Yet the working title belied the true nature of the paper. The published title provides the precision the topic deserves.
I won’t reprise the penultimate comma soliloquy that I shared with the editors, but you can learn more here, or pick up a copy of Fowler’s Modern English Usage.
White Paper Metadata
Tile: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
URL: https://msdn.microsoft.com/en-us/library/dn673537.aspx
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 b|t)
Contributers: Yi Fang (Microsoft), Vassilis Papadimos (Microsoft)
Technical Reviewers: Barbara Kess (Microsoft), Jack Li (Microsoft), Jimmy May (Microsoft b|t), Sanjay Mishra (Microsoft), Shep Sheppard (Microsoft), Mike Weiner (Microsoft), Paul White (SQL Kiwi Limited b|t)
I'm confident you'll find the paper as edifying as I did. Enjoy!