Blog Post

Azure Database Support Blog
5 MIN READ

When SQL Performance Changes Without a Code Change: Read the Actual Execution Plan Before Tuning

Mohamed_Baioumy_MSFT's avatar
Nov 16, 2026

Your query returns 800 rows, but the engine processed 108,000 to get there. That gap may be the whole performance problem.

"This query used to be fast, and nobody changed it" is one of the most common things we hear in performance cases. Often the first reaction is to update statistics, add an index or scale up. Those can help, but they're guesses. A better first question is:

Which operator produced far more rows than expected, and why?

The actual execution plan answers that question.

Why the same SQL can slow down

Leaving the code untouched doesn't keep everything else the same. Common reasons a query slows down on its own:

  • Data growth: a history table that doubles in size makes every join against it more expensive.
  • A different plan: new statistics, a recompile or a parameter value can lead the optimizer to choose a different shape.
  • Changes in data distribution: for example, more versions per key than before.

The query text is the same, but what the engine actually does may not be.

Estimated vs. actual plans

  • An estimated plan is what the optimizer expects to happen.
  • An actual plan shows what did happen, including the real row count at every operator.

To capture one:

  • In SSMS: select Include Actual Execution Plan (Ctrl+M) before you run the query.
  • In T-SQL: run SET STATISTICS XML ON before the query.
  • For the last actual plan of a cached query: turn on LAST_QUERY_PLAN_STATS and query sys.dm_exec_query_plan_stats. Both are supported in Azure SQL.

Once you have the plan, read it from right to left. On each operator, compare Estimated Number of Rows with Actual Number of Rows. The first place where the two numbers diverge sharply is usually where to start.

A small example that gets expensive

Here's a common pattern: a table with one current row per entity, joined to two history tables before any filtering.

CREATE TABLE dbo.Entity        (EntityId int PRIMARY KEY, Status varchar(10));
CREATE TABLE dbo.PriceHistory  (EntityId int, ValidFrom date, Price decimal(10,2));
CREATE TABLE dbo.RegionHistory (EntityId int, ValidFrom date, Region varchar(10));

-- 1,000 entities, 800 of them Active
INSERT dbo.Entity
SELECT value, CASE WHEN value % 5 = 0 THEN 'Closed' ELSE 'Active' END
FROM GENERATE_SERIES(1, 1000);

-- 18 price versions per entity
INSERT dbo.PriceHistory
SELECT e.EntityId, DATEADD(MONTH, v.value, '2024-01-01'), 100 + v.value
FROM dbo.Entity AS e CROSS JOIN GENERATE_SERIES(1, 18) AS v;

-- 6 region versions per entity
INSERT dbo.RegionHistory
SELECT e.EntityId, DATEADD(MONTH, v.value * 3, '2024-01-01'), CONCAT('R', v.value)
FROM dbo.Entity AS e CROSS JOIN GENERATE_SERIES(1, 6) AS v;

This query looks harmless:

WITH joined AS (
    SELECT e.EntityId, e.Status, p.ValidFrom AS PriceFrom, p.Price,
           r.ValidFrom AS RegionFrom, r.Region
    FROM dbo.Entity AS e
    JOIN dbo.PriceHistory  AS p ON p.EntityId = e.EntityId
    JOIN dbo.RegionHistory AS r ON r.EntityId = e.EntityId
),
windowed AS (
    SELECT *,
           LAG(Price) OVER (PARTITION BY EntityId ORDER BY PriceFrom) AS PrevPrice,
           ROW_NUMBER() OVER (PARTITION BY EntityId
                              ORDER BY PriceFrom DESC, RegionFrom DESC) AS rn
    FROM joined
)
SELECT EntityId, Price, PrevPrice, Region
FROM windowed
WHERE Status = 'Active' AND rn = 1;

It returns 800 rows. Here's what happens along the way:

1,000 entities
   ↓ join price history (18 versions each)
18,000 rows
   ↓ join region history (6 versions each)
108,000 rows
   ↓ LAG + ROW_NUMBER → sort
108,000 rows sorted
   ↓ filter: Status = 'Active' AND rn = 1
800 rows

The engine builds and sorts 135 rows for every row you get back. Because Status isn't a partitioning column of the window functions, the filter on it can't be applied before them. The engine has to compute every window first and filter afterwards.

Count at every stage

The quickest way to see where rows multiply is to count them stage by stage:

SELECT COUNT(*) AS rows_, COUNT(DISTINCT e.EntityId) AS entities
FROM dbo.Entity AS e;                                                   -- 1,000 | 1,000

SELECT COUNT(*), COUNT(DISTINCT e.EntityId)
FROM dbo.Entity AS e
JOIN dbo.PriceHistory AS p ON p.EntityId = e.EntityId;                  -- 18,000 | 1,000

SELECT COUNT(*), COUNT(DISTINCT e.EntityId)
FROM dbo.Entity AS e
JOIN dbo.PriceHistory  AS p ON p.EntityId = e.EntityId
JOIN dbo.RegionHistory AS r ON r.EntityId = e.EntityId;                 -- 108,000 | 1,000

When COUNT(*) grows but COUNT(DISTINCT key) stays the same, the join is multiplying rows rather than adding new information. That's a many-to-many join between history tables.

Check correctness before you tune

That join multiplication can also change the results, not just the cost.

After the second join, every price version appears 6 times. LAG(Price) is meant to return the previous price, but it can now return a duplicate of the same price. The query runs, returns the expected number of rows, and some of the values may be wrong.

In a real case we worked on, duplicate history rows were identified as something that could affect previous-value logic like this. It was a hypothesis to test against the business rules, not a confirmed defect. Treat it the same way in your environment: confirm what the result should be before you make it faster.

A better shape

Reduce each history table to only what you need, then join:

WITH p AS (
    SELECT EntityId, Price,
           LAG(Price) OVER (PARTITION BY EntityId ORDER BY ValidFrom) AS PrevPrice,
           ROW_NUMBER() OVER (PARTITION BY EntityId ORDER BY ValidFrom DESC) AS rn
    FROM dbo.PriceHistory
),
r AS (
    SELECT EntityId, Region,
           ROW_NUMBER() OVER (PARTITION BY EntityId ORDER BY ValidFrom DESC) AS rn
    FROM dbo.RegionHistory
)
SELECT e.EntityId, p.Price, p.PrevPrice, r.Region
FROM dbo.Entity AS e
JOIN p ON p.EntityId = e.EntityId AND p.rn = 1
JOIN r ON r.EntityId = e.EntityId AND r.rn = 1
WHERE e.Status = 'Active';

The window functions now each run over a single history table, so LAG sees each price version once. The joins then combine one row per entity instead of multiplying versions. Compare the actual plans of the two queries side by side, and check that the results match what the business expects.

Other things to look for in the actual plan

  • Sort and hash warnings. A yellow warning icon on an operator can mean it spilled to tempdb because its memory grant was too small for the real row count.
  • Memory grant. Check the root operator's properties. A grant sized for 1,000 rows can't handle 108,000.
  • Late filters. A filter near the left of the plan (close to the end of processing) that removes most rows means the work to its right was mostly wasted.
  • Statistics. Use them as evidence. If estimated and actual row counts are far apart on a table scan or seek, outdated statistics may be the cause. If the estimates are accurate there but wrong after a join, updating statistics again usually won't help.

Checklist

  1. Capture the actual plan, not only the estimated one.
  2. Find the first operator where estimated and actual rows diverge sharply.
  3. Count rows and distinct keys at each join.
  4. Look for many-to-many joins between history tables.
  5. Check whether filters run before or after expensive sorts and window functions.
  6. Look for spills and undersized memory grants.
  7. Confirm the results are correct before you optimize.
  8. Test changes on a smaller copy of the data, then confirm the improvement at full size.

Summary

The most expensive rows in a query are often the ones your application never receives. Before you update statistics again or add another index, find out how many rows each step actually produced. The actual execution plan already shows that.

Published Nov 16, 2026
Version 1.0
No CommentsBe the first to comment