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.

Writer: Joseph Sack (SQLskills.com)

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

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

 

Published: April 2014

Applies to: SQL Server 2014

 

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.docx