What is Cardinality Estimation in SQL Server and why does it matter?

Cardinality estimation predicts the number of rows returned by a query operation.

SQL Server's Query Optimizer uses cardinality estimation (CE) to decide join types, memory grants, and execution strategies. It relies on statistics, histograms, and assumptions about data distribution. Incorrect CE leads to bad plans like hash joins where nested loops would be better, or excessive memory grants causing spills to tempdb.