New CE ignores statistics for LIKE predicates on varchar columns with UTF-8 collations — fixed 9% guess (partially fixed in CE 170, `%x%` still affected, no backport)

SUMMARY

For varchar columns using any UTF-8 collation (collation name ending in _UTF8), the modern cardinality estimator (CE model 120 to 160) never consults the histogram or string statistics for LIKE predicates. Every "LIKE 'prefix%'" and "LIKE '%substring%'" predicate is estimated at a fixed 9 percent of table rows, regardless of the actual data distribution, and regardless of whether statistics exist, are FULLSCAN-fresh, or are index-backed.

An identical column (same data, same declared length, same index) with a non-UTF8 collation is estimated correctly (about 2 percent error in our repro). nvarchar with the same UTF-8 collation is also estimated correctly. The trigger is the varchar-with-UTF8-encoding storage, not the collation's comparison rules.

The 9 percent guess is often wrong by 2 to 3 orders of magnitude, flipping plans from nested-loop seeks to hash joins with full scans. In our production-scale migration test (SQL Server 2019, nvarchar to varchar UTF-8), a bill-of-lading number prefix search regressed by a factor of 16 (129 ms to 2131 ms; estimate 43746 vs actual 321 rows; 7.2 million rows read from a 3.7 million row table to return 435 rows).

VERSIONS TESTED (all reproduce)

• SQL Server 2019 RTM, build 15.0.2000.5, CE 120 to 150: prefix LIKE = fixed 9 percent; substring LIKE = fixed 9 percent
• SQL Server 2022 RTM, build 16.0.1000.6, CE 120 to 160: same
• SQL Server 2025 CU8, build 17.0.4075.5, CE 120 to 160: same
• SQL Server 2025 CU8, build 17.0.4075.5, CE 170: prefix LIKE = range-style guess (see note 1); substring LIKE = STILL fixed 9 percent
• Any of the above with legacy CE (model 70): prefix LIKE = range-style guess; substring LIKE = fixed 9 percent

Notes:

1. CE 170 only partially fixes this. Prefix LIKE stops guessing 9 percent, but the new estimate is a flat range-style guess (about 24 rows in our 100000-row repro FOR EVERY PREFIX: a 1-character prefix matching 3846 rows and a 6-character prefix matching 2 rows both estimate 24). The histogram is still not consulted. Substring LIKE ('%x%') still guesses 9 percent under CE 170.
2. The CE 170 change is not backported. USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160') on the same 17.0.4075.5 engine still produces the 9 percent guess, so SQL Server 2022 (max CE 160) and 2019 (max CE 150) can never receive it.
3. Reproduced across all 1553 UTF-8 collations available on SQL Server 2019 (every language family, including BIN2, CS, SC and 140 variants, with no exception). 56 non-UTF8 control collations all estimate correctly on identical data.

REPRO (self-contained, any edition, tempdb only)

SET NOCOUNT ON;
CREATE TABLE #repro (
    id     int identity PRIMARY KEY,
    v_utf8 varchar(20) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
    v_norm varchar(20) COLLATE Latin1_General_100_CI_AS   -- control: identical data
);
;WITH n AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS r
    FROM sys.all_objects a CROSS JOIN sys.all_objects b)
INSERT INTO #repro (v_utf8, v_norm)
SELECT x.val, x.val
FROM n CROSS APPLY (SELECT CONCAT(CHAR(65 + r % 26), CHAR(65 + (r/26) % 26),
                    RIGHT('0000000' + CAST(r AS varchar(10)), 7))) x(val);
CREATE INDEX IX_u ON #repro (v_utf8);
CREATE INDEX IX_n ON #repro (v_norm);
UPDATE STATISTICS #repro WITH FULLSCAN;
-- Turn on "Include Actual Execution Plan", then run (parameterized, as drivers send it):
DECLARE @p varchar(20) = 'AB';   -- matches 148 rows
EXEC sp_executesql N'SELECT COUNT(*) FROM #repro WHERE v_utf8 LIKE @P0 + ''%''',
                   N'@P0 varchar(20)', @P0 = @p;
EXEC sp_executesql N'SELECT COUNT(*) FROM #repro WHERE v_norm LIKE @P0 + ''%''',
                   N'@P0 varchar(20)', @P0 = @p;
DROP TABLE #repro;

EXPECTED VS ACTUAL

On the Index Seek operator (actual rows = 148 for both columns):

• v_norm (non-UTF8): expected estimate about 148, actual estimate 145 (histogram used, correct)
• v_utf8 (UTF-8): expected estimate about 148, actual estimate 9000, which is exactly 9 percent of 100000

The 9 percent figure scales exactly with table size (verified at 100000 and 486063 rows) and matches the documented fixed selectivity guess for un-estimable double-bounded ranges. In other words, the LikeRangeStart / LikeRangeEnd interval computation appears to be unavailable for UTF-8-encoded varchar, so the estimator silently falls back to the guess even though a FULLSCAN histogram and string statistics exist.

IMPACT

UTF-8 collations are promoted as the low-friction path to Unicode for varchar-based applications (SQL Server 2019 and later). Any such database silently loses cardinality estimation for every LIKE search, which is typically the reference-number and name search boxes that dominate OLTP screens. Plans flip from seeks to scans and hash joins; in our migration the affected searches regressed 3x to 16x. There is no engine-level workaround that preserves the modern CE: legacy CE trades this bug for its own regressions, and per-query hints do not scale to ORM-generated workloads.

REQUEST

1. Make the CE use string statistics and the histogram for LIKE on varchar UTF-8, as it does for every other encoding (ideally data-aware, not the flat CE 170 guess).
2. Extend the CE 170 prefix fix to substring LIKE ('%x%').
3. Consider backporting to CE 150 and 160 (SQL Server 2019 and 2022), or at minimum document the limitation on the "UTF-8 support" and "Cardinality Estimation" documentation pages so migrations can plan for it.