MSDN Documentation

Overview

The SQL Query Optimizer evaluates multiple execution plans for a given query and selects the most efficient one based on a cost model. It operates transparently, aiming to reduce I/O, CPU, and memory consumption.

How It Works

The optimizer follows three primary phases:

1. Parsing and algebrization – transforms the T‑SQL text into an internal tree.
2. Logical optimization – applies rewrite rules (e.g., predicate push‑down).
3. Physical optimization – enumerates possible physical operators and computes costs.

Cost Estimation

Costs are estimated using statistics about data distribution. The optimizer considers factors such as:

• Estimated rows (cardinality)
• IO cost (page reads)
• CPU cost (operator complexity)
• Memory grant requirements

Statistics & Cardinality

Accurate statistics are essential. They are stored as histogram steps and density vectors. Use UPDATE STATISTICS or sp_autostats to keep them fresh.

-- Example: Updating statistics for a table
EXEC sp_updatestats;
-- Or more granular
UPDATE STATISTICS dbo.Sales WITH FULLSCAN;

Optimizer Hints

When the optimizer's choice is suboptimal, you can influence it with hints:

SELECT /*+ INDEX(Sales IX_Sales_Date) */ *
FROM Sales
WHERE SaleDate BETWEEN '2024-01-01' AND '2024-01-31';

Use hints sparingly; they can lead to plan regressions as data changes.

Practical Examples