A merge join can be fast when both inputs arrive in compatible order, but existing indexes do not guarantee that it is the cheapest complete plan. SQL Server compares the cost of scans, lookups, sorts, residual predicates, memory, and expected row counts before choosing a physical join.
Last updated: October 2, 2026.
SET STATISTICS IO, TIME ON;
SELECT t.TransactionId, t.CardId, t.TransactionDate
FROM dbo.Transactions AS t
JOIN dbo.BlockedCards AS b
ON b.CardId = t.CardId
WHERE t.TransactionDate >= b.BlockedDate
OPTION (RECOMPILE);
SET STATISTICS IO, TIME OFF;Capture the actual execution plan with representative parameters. Compare estimated and actual rows, logical reads, elapsed time, spills, and any Sort operators instead of judging the join icon by itself.
Check whether both inputs are truly merge-ready
Microsoft explains in its join documentation that merge joins need sorted inputs on the merge columns. An index may start with the correct key yet still require extra work because of key direction, filters, lookups, parallel exchanges, or an additional inequality predicate. A many-to-many merge join may also use a temporary worktable when duplicate keys exist.
Large estimate errors can make the optimizer price a good strategy incorrectly. Check statistics on the join and filter columns, compare actual versus estimated rows at each input, and test with realistic data distribution.
Use a hint as a test, not as the first fix
A MERGE join hint is useful for an experiment: it shows what the alternative plan would cost and whether it remains faster across representative inputs. Microsoft’s join-hint reference warns that hints restrict optimizer choices, so avoid making one permanent until statistics, indexes, and estimate errors are understood.
For related plan work, review SQL joins, index design for filter patterns, and SQL Server parameter-sensitive plans.
