Think Like the Cardinality Estimator

Explore the intricacies of SQL Server's cardinality estimation process in this 58-minute conference talk from PASS Data Community Summit. Dive deep into how the query optimizer generates row estimates based on statistics, and learn to leverage this knowledge to write more efficient TSQL code. Discover the impact of cardinality estimation on query execution, including object access methods, physical join types, and data sorting. Examine various scenarios such as multiple predicates, range predicates, unknown variable values, and ascending order predicate values. Gain insights into histogram usage and out-of-range predicate values. Walk away with enhanced skills to analyze and optimize challenging query plans, equipped with a thorough understanding of cardinality estimation techniques and their effects on query performance.

Introduction
Housekeeping
Session Evolution
How to contact me
About the session
Definitions
Pred selectivity
Example
Density
Density Example
cardiology estimation
SQL estimation
Execution plan
Why Cardinality Matters
Effects of Cardinality
Murphys Law
DBCC Statistics
Histogram
Explanation
Versions
Compatibility Level
Updated Stats
Actual Execution Plan
Multiple Predicates
What Changed
Parameter sniffing
Unknowns
Ascending Key