How SQL Server Estimates Rows Before Running a Query

How SQL Server Estimates Rows Before Running a Query

Indexes help SQL Server find data. Statistics help SQL Server estimate how much data it is likely to find.

Before SQL Server executes a query, it faces an important problem:

How can it know whether a condition will return 5 rows, 5,000 rows or 5 million rows without running the query first?

This is not a minor detail. The expected number of rows influences whether SQL Server chooses:

• an index seek or a scan;
• Nested Loops, Merge Join or Hash Join;
• a serial or parallel plan;
• a small or large memory grant;
• a streaming or hashing aggregate;
• a lookup or direct table access;
• a plan that remains in memory or spills work into tempdb.

SQL Server answers this question using statistics.

An index is an access path. Statistics are a compact description of the data's distribution. Understanding that distinction makes execution plans, indexing decisions and query-performance problems much easier to reason about.

---

SQL Server must choose a plan before seeing the result

Consider this query:

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM Sales.Orders
WHERE CustomerId = 1250;

Suppose an index exists on CustomerId.

If SQL Server expects three matching orders, it might use an index seek followed by a few key lookups. If it expects three million matching orders, repeated lookups may be far more expensive than scanning a larger structure once.

The SQL text is identical. The available index is identical. Only the expected number of rows changes.

SQL Server's Query Optimizer considers different execution plans and assigns an estimated cost to each one. Those costs depend heavily on cardinality estimates.

Cardinality means the number of rows expected from an operation. Every filter, join, aggregation and intermediate stage in an execution plan has an estimated cardinality.

Read customers
Estimated rows: 1,000
        ↓
Filter active customers
Estimated rows: 120
        ↓
Join to orders
Estimated rows: 8,500
        ↓
Group by region
Estimated rows: 12

If those estimates are reasonably accurate, SQL Server has a good chance of choosing an appropriate plan. If an early estimate is badly wrong, that error can spread through everything that follows.

For the wider planning process, read How SQL Server Optimizes a Query. For the physical operators that carry the chosen plan out, continue with How SQL Server Executes a Query.

---

Statistics describe data without storing every value

SQL Server cannot inspect every row during query optimization. Doing so would effectively execute much of the query before deciding how to execute it.

Instead, it maintains statistics objects. A statistics object contains a compact model of one or more columns. Its most important components include:

• a header describing when and how the statistics were created;
• a histogram describing the distribution of values in the leading column;
• density information describing the number of distinct values;
• additional information for supported string data.

Statistics can be created:

• automatically when SQL Server encounters a useful column;
• when an index is created;
• explicitly with CREATE STATISTICS;
• as filtered statistics for a particular subset of rows;
• as multicolumn statistics when relationships between columns matter.

By default, SQL Server can also update statistics when enough underlying data has changed. These automatic behaviours are normally helpful and should not be disabled without a measured reason.

---

An index creates statistics, but statistics do not require an index

Creating an index normally creates an associated statistics object:

CREATE INDEX IX_Orders_CustomerId
ON Sales.Orders(CustomerId);

SQL Server receives two related benefits:

1. The index provides a route to rows ordered by CustomerId.
2. The statistics help estimate how many rows different customer values may return.

But SQL Server can also create statistics on a column without creating an index. For example:

SELECT *
FROM Sales.Orders
WHERE Status = 'Pending';

If automatic statistics creation is enabled and no relevant statistics exist, SQL Server may create a statistics object for Status.

That does not give SQL Server a new route into the table. It only gives the optimizer better information about the distribution of status values.

Index
Provides physical access to data

Statistics
Provide information for estimating data

Better statistics may produce a better plan even when no new index is created. A new index may still be required if the chosen plan needs a more efficient access path.

Our companion lesson, SQL Server Indexes: Make Reads Faster Without Making Writes Expensive, explains the access-path side of this relationship.

---

Selectivity tells SQL Server how restrictive a condition is

Selectivity is the fraction of rows expected to satisfy a condition.

Suppose a table contains 1,000,000 orders:

SELECT *
FROM Sales.Orders
WHERE Status = 'Cancelled';

If 2,000 rows are cancelled, the condition has a selectivity of:

2,000 / 1,000,000 = 0.002

Approximately 0.2% of the table qualifies. This is a highly selective condition.

Now consider:

WHERE Status <> 'Cancelled'

If 998,000 rows qualify, the condition is not selective.

A selective predicate may encourage SQL Server to navigate an index. A predicate returning most of the table may make a scan more attractive. The presence of an index alone does not determine whether SQL Server will use it. The optimizer must estimate how selective the condition is and compare the available alternatives.

---

The histogram models the leading column

The histogram is the most recognisable part of a SQL Server statistics object. You can inspect statistics using:

DBCC SHOW_STATISTICS
(
    'Sales.Orders',
    'IX_Orders_CustomerId'
);

You can also inspect statistics metadata and histograms through dynamic management functions:

sys.dm_db_stats_properties
sys.dm_db_stats_histogram

A histogram does not store every distinct value. It compresses the distribution into a maximum of 200 steps. Each step describes a boundary and the values leading up to it.

Important columns include:

• RANGE_HI_KEY
• EQ_ROWS
• RANGE_ROWS
• DISTINCT_RANGE_ROWS
• AVG_RANGE_ROWS

RANGE_HI_KEY

This is the upper boundary of a histogram step.

EQ_ROWS

This estimates how many rows equal the boundary value. If a step contains:

RANGE_HI_KEY = 100
EQ_ROWS      = 12,500

SQL Server can estimate approximately 12,500 rows for:

WHERE ProductId = 100

RANGE_ROWS and DISTINCT_RANGE_ROWS

RANGE_ROWS represents rows within the range before the upper boundary. DISTINCT_RANGE_ROWS estimates how many distinct values occur inside that range.

AVG_RANGE_ROWS

This is the average number of rows expected for each distinct value within the range:

RANGE_ROWS / DISTINCT_RANGE_ROWS

If a requested value is inside a histogram range but is not a boundary value, SQL Server may use this average. The estimate may be sensible, but it is still an approximation.

---

Histograms deliberately compress reality

A histogram can contain no more than 200 steps. A column may contain millions of different values, unevenly distributed across billions of rows. SQL Server must represent that distribution using a small statistical summary.

Imagine an OrderStatus column:

Completed    8,500,000
Pending         45,000
Failed           2,500
Cancelled      120,000

This distribution is skewed. Treating every value as equally common would be misleading.

Histograms try to preserve statistically important boundaries and frequent values. However, values grouped inside a range may be treated as though they follow the average distribution for that range.

SQL Server can have valid statistics and still produce an imperfect estimate. Statistics are a model, not a copy of the table.

---

Only the first statistics column receives a histogram

Suppose we create an index containing:

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON Sales.Orders(CustomerId, OrderDate);

The histogram is built on CustomerId, the leading column. SQL Server does not create another histogram for OrderDate inside the same statistics object.

The statistics can include density information about prefixes such as:

CustomerId
CustomerId + OrderDate

But the detailed histogram describes only the first column.

Column order therefore affects more than index navigation. It also determines which column receives the histogram. If queries frequently filter by OrderDate independently, SQL Server may need a separate statistics object or another appropriate index beginning with OrderDate.

---

Density helps when the exact value is unknown

Density is broadly related to the number of distinct values:

Density = 1 / number of distinct values

Suppose a column contains 500 distinct customer categories:

Density = 1 / 500 = 0.002

If the table contains 1,000,000 rows, the average number of rows per value is:

1,000,000 × 0.002 = 2,000

SQL Server may use this average when it cannot use a specific histogram value. One example is a local variable:

DECLARE @CustomerId int = 1250;

SELECT *
FROM Sales.Orders
WHERE CustomerId = @CustomerId;

At optimization time, SQL Server may not know the local variable's value. It cannot look up that value in the histogram, so it may use a density-based average or another modelled estimate.

Whether customer 1250 has 2 orders or 200,000 orders, the estimate may still be based on an average. The resulting plan can be reasonable for an average customer and terrible for this particular one.

---

Unknown values force SQL Server to make assumptions

The optimizer may not know the real selectivity of a condition because:

• a local variable hides the value during optimization;
• statistics are missing or stale;
• data has changed beyond the histogram's range;
• a scalar expression prevents direct use of column statistics;
• multiple columns are correlated;
• an intermediate result has no useful statistics;
• a table variable or function provides limited cardinality information;
• a parameter produces very different row counts for different values.

When exact statistical information is unavailable, SQL Server does not give up. It applies assumptions and estimation models. Those assumptions are necessary, but they may not match the application's data.

---

Expressions can hide useful information

Consider:

SELECT *
FROM Sales.OrderLines
WHERE Quantity * UnitPrice > 10000;

SQL Server may have statistics for Quantity and UnitPrice. That does not mean it knows the distribution of Quantity * UnitPrice. The combination is a new expression.

Without useful statistical information, SQL Server may fall back to a general estimate. One possible solution is a computed column:

ALTER TABLE Sales.OrderLines
ADD LineValue AS Quantity * UnitPrice;

SQL Server can create statistics for the computed result. The optimizer may then match the query expression to the computed-column definition and produce a better estimate.

The expression generally needs to match the computed-column definition closely. A computed column should not be added casually, but it can be useful when an important expression repeatedly causes estimation problems.

---

Correlated columns challenge estimation assumptions

Consider an address table containing City, Postcode and Country. These columns are not independent. A postcode is strongly related to a city and country.

SELECT *
FROM Customer.Address
WHERE City = 'Manchester'
  AND CountryCode = 'GB';

SQL Server may know how many rows contain Manchester and how many contain GB. Separate statistics do not necessarily tell it that almost every Manchester address is in the United Kingdom.

Modern cardinality-estimation models soften some independence assumptions, but they still cannot understand every real-world relationship.

Possible responses include:

• multicolumn statistics;
• filtered statistics;
• a suitable composite index;
• rewriting or separating complex processing;
• testing a different cardinality-estimation model for a known regression;
• using Query Store and current feedback features where appropriate.

Do not reach for trace flags or hints first. Begin by confirming the estimation error and understanding the data relationship.

---

Filtered statistics describe an important subset

Suppose the application frequently works with open support cases:

SELECT *
FROM Support.Cases
WHERE Status = 'Open'
  AND Priority = 'Critical';

Most cases may be closed. Statistics describing the entire table may provide limited detail about the smaller active subset.

Filtered statistics can focus on that subset:

CREATE STATISTICS ST_Cases_Open_Priority
ON Support.Cases(Priority)
WHERE Status = 'Open';

This gives SQL Server a histogram describing priorities among open cases rather than among every historical case.

Filtered statistics can be useful when:

• a query targets a stable subset;
• data is strongly skewed;
• columns are correlated within that subset;
• a huge table cannot represent important values adequately in one 200-step histogram.

As with filtered indexes, parameterised queries require careful testing. SQL Server must be confident that the filtered statistics safely apply to the compiled query.

---

New values may sit beyond the histogram

Date, identity and sequence columns commonly increase over time.

Suppose statistics were last updated when the newest order date was 31 July:

Histogram maximum: 2026-07-31

New August orders are inserted, but not enough changes occur to trigger an automatic statistics update.

WHERE OrderDate = '2026-08-19'

That date lies beyond the histogram. This is known as the ascending-key problem. SQL Server must estimate rows for values that were not represented when the statistics were built.

Modern cardinality estimation includes better approaches for such values, but large and continuously growing tables can still experience stale or unrepresentative statistics.

This is particularly relevant after:

• batch imports;
• data migrations;
• month-end processing;
• ETL loads;
• sudden growth in a new tenant or customer;
• loading a new partition.

After a significant data load, updating relevant statistics may be as important as validating the data itself.

---

Sample size affects statistics quality

SQL Server normally samples table pages when creating or updating statistics. Sampling reduces the cost of collecting information and is sufficient for many tables.

However, sampling can miss important patterns when:

• data is highly skewed;
• similar values are physically concentrated;
• the table is extremely large;
• a rare but important value influences a critical query;
• recently loaded data is not evenly distributed.

You can request a full scan:

UPDATE STATISTICS Sales.Orders
WITH FULLSCAN;

A full scan provides the most complete input but can be expensive. It consumes I/O, CPU and time, and updating statistics can invalidate dependent cached plans.

Do not use FULLSCAN everywhere by habit. Use it where evidence shows that the normal sample produces inadequate estimates.

---

Incremental statistics help partitioned tables

Large partitioned tables often receive changes only in recent partitions. Without incremental statistics, updating a statistics object may require sampling the complete table even when only the latest partition changed.

Incremental statistics allow SQL Server to update statistics for modified partitions and merge that information into the global statistics object.

This can be useful for:

• large fact tables;
• date-partitioned transaction data;
• sliding-window retention;
• data warehouse loads;
• append-heavy audit or telemetry tables.

The global histogram still has a limited number of steps, but incremental maintenance can reduce the work needed to keep partitioned data represented.

---

Bad estimates propagate through the plan

Imagine that SQL Server estimates 10 rows from a filter but actually receives 500,000.

The next operator may have been chosen on the assumption that only 10 rows were coming. Potential consequences include:

• Nested Loops repeating an inner seek 500,000 times;
• a key lookup executing far more often than expected;
• a hash operation receiving too little memory;
• a sort spilling to tempdb;
• a serial plan processing work that justified parallelism;
• a join order becoming inappropriate;
• a memory grant being much too small or unnecessarily large.

This is why estimated-versus-actual row counts are so valuable.

In an actual execution plan, start where the first meaningful difference appears:

Estimated rows: 10
Actual rows:    500,000

Do not begin at the final expensive operator. The root cause may be an earlier filter where the estimate first became inaccurate.

---

A practical cardinality-estimation investigation

1. Capture an actual execution plan

An estimated plan shows predictions. An actual plan allows comparison with runtime row counts.

2. Find the first significant mismatch

Work through the data flow and compare:

• Estimated Number of Rows;
• Actual Number of Rows;
• Number of Executions;
• rows read;
• rows returned.

3. Inspect the predicates

Look for functions, arithmetic expressions, implicit conversions, leading wildcards, local variables and correlated conditions.

4. Inspect the relevant statistics

DBCC SHOW_STATISTICS
sys.stats
sys.dm_db_stats_properties
sys.dm_db_stats_histogram

Review the last update time, rows represented, rows sampled, modification counter, histogram boundaries, leading column and any filter.

5. Compare the histogram with the queried value

Ask whether the value is represented as a boundary, averaged inside a range, beyond the histogram maximum, part of a skewed distribution or combined with a correlated column.

6. Correct the cause proportionately

Possible responses include:

• updating existing statistics;
• increasing the sample for selected statistics;
• creating multicolumn or filtered statistics;
• adding a computed column for a repeated expression;
• rewriting a non-searchable predicate;
• replacing an unsuitable intermediate structure;
• breaking a complex process into stages with temporary tables;
• reviewing parameter sensitivity;
• testing database compatibility changes safely.

7. Measure again

Compare estimated and actual rows, logical reads, CPU time, duration, memory grant, spills, join algorithms and execution frequency.

A more accurate estimate is valuable when it produces a more suitable plan and better application behaviour.

---

Statistics maintenance needs judgement

SQL Server can automatically create and update statistics, and these defaults are a strong starting point. Manual maintenance can still be helpful after major data changes or where automatic sampling is insufficient.

Important distinctions include:

• rebuilding an index updates that index's statistics using a full scan;
• rebuilding an index does not update unrelated column statistics;
• reorganising an index does not update statistics;
• UPDATE STATISTICS can update index statistics, column statistics or both;
• updating statistics can invalidate dependent cached execution plans.

A common mistake is rebuilding indexes and then immediately replacing their full-scan statistics with lower-quality sampled statistics. Another is updating every statistic constantly, creating unnecessary compilation and maintenance work.

Statistics maintenance should be coordinated with index maintenance and based on the characteristics of the database.

---

Estimated cost is for comparing plans

Execution plans show estimated operator and subtree costs. These values are useful to SQL Server when comparing candidate plans. They are not reliable predictions of execution time in seconds and should not be treated as absolute performance measurements.

Estimated costs are calculated from factors including:

• estimated rows;
• estimated executions;
• CPU work;
• expected I/O;
• operator algorithm;
• row size;
• available memory.

If the estimated rows are wrong, the estimated cost can also be misleading.

Use plan costs to understand optimizer choices, but use runtime evidence to assess performance: logical reads, CPU time, duration, waits, memory grants, spills and actual row counts.

---

The mentoring lesson to remember

SQL Server does not know the future. Before running a query, it builds a mathematical expectation from statistics, assumptions and available metadata. That expectation determines which plan appears cheapest.

Indexes
Where can SQL Server find the data?

Statistics
How much data does SQL Server expect to find?

Cardinality estimates
How many rows are expected at each step?

Cost model
How expensive might each candidate plan be?

Execution plan
Which strategy appears cheapest before execution?

When a query performs badly, do not ask only:

Is there an index?

Also ask:

What did SQL Server believe about the number of rows, and what information led it to that belief?

That question takes you beyond surface-level tuning and towards understanding why SQL Server selected the plan in the first place.

---

Continue the SQL Server performance journey

• How SQL Server Optimizes a Query: From T-SQL Text to Execution Plan
• How SQL Server Executes a Query: Read the Plan as Data Flow
• SQL Server Indexes: Make Reads Faster Without Making Writes Expensive
• SQL Mentoring Series: Performance Engineering and Modern Data Architecture

If this guide helped you understand what SQL Server believes before it executes a query, I would be pleased to hear from you. For mentoring, technical discussion or collaboration, contact dotnetdeveloper20xx@hotmail.com.