Уровень совместимости и оценщик кардинальности в SQL Server

Автор: Vivek Johari, Compatibility Level vs. Cardinality Estimator in SQL Server: A Complete Guide;

Если вы когда-либо занимались настройкой производительности SQL Server, вы почти наверняка сталкивались с двумя терминами, которые постоянно путают: уровень совместимости (Compatibility Level, CL) и оценщик кардинальности (Cardinality Estimator, CE). Они звучат так, будто могут быть одним и тем же, поскольку изменение одного часто влияет на поведение другого. Но это два разных понятия в ядре SQL Server, и понимание того, где они пересекаются, а где расходятся, необходимо для всех, кто занимается обновлениями, миграциями или настройкой запросов.

В этой статье разбирается, что представляет собой каждое из этих понятий, как они связаны друг с другом, что и когда менялось, а также как диагностировать и исправлять проблемы, вызванные регрессиями, связанными с оценщиком кардинальности.

Что такое уровень совместимости базы данных?

Уровень совместимости — это настройка на уровне базы данных, которая указывает процессору запросов SQL Server, какое поведение, характерное для конкретной версии, эмулировать для этой базы данных. Он устанавливается для каждой базы данных (а не для экземпляра), что означает, что один экземпляр SQL Server может одновременно обслуживать базы данных, работающие на разных уровнях совместимости.

Вы можете легко проверить и изменить его:

-- Проверка текущего уровня совместимости
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseName';

-- Изменение уровня совместимости
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 160;

Номера уровней совместимости по версиям SQL Server

Версия SQL Server	Уровень совместимости
SQL Server 2008	100
SQL Server 2012	110
SQL Server 2014	120
SQL Server 2016	130
SQL Server 2017	140
SQL Server 2019	150
SQL Server 2022	160
SQL Server 2025	170

Номер — это просто номер версии SQL Server, умноженный на 10 (с некоторыми историческими исключениями для более ранних версий).

Что на самом деле контролирует уровень совместимости

Уровень совместимости в основном управляет поведением T-SQL на уровне синтаксиса и семантики, например:

• Будет ли устаревший синтаксис всё ещё работать или вызывать ошибку.
• Поведение некоторых функций (например, пограничные случаи разбора дат/времени).
• Доступность функций оптимизатора запросов (не только CE, но и такие вещи, как пакетный режим для хранилища строк, функции интеллектуальной обработки запросов, некоторые подсказки).
• Некоторые правила неявного преобразования и сравнения.

Важно отметить, что уровень совместимости не меняет физическую версию движка SQL Server или формат файла базы данных. Вы можете запускать базу данных на уровне совместимости 110 на экземпляре SQL Server 2022. Бинарные файлы движка будут версии 2022, но многие поведения T-SQL будут вести себя как SQL Server 2012. Именно поэтому уровень совместимости является основным инструментом для минимизации критических изменений при обновлении: сначала обновляется экземпляр, база данных остаётся на старом уровне совместимости, а затем уровень повышается постепенно после проверки.

Пример: функция, управляемая уровнем совместимости

Функции интеллектуальной обработки запросов (Intelligent Query Processing, IQP) — хороший реальный пример. Такие функции, как адаптивные соединения, перемежающееся выполнение для многооператорных табличных функций и обратная связь по гранту памяти, доступны только когда уровень совместимости базы данных достиг или превысил уровень, на котором они были введены, даже если базовый движок SQL Server полностью их поддерживает.

-- В SQL Server 2022 эта база данных не получит большинство функций IQP
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 130;

-- Повышение уровня открывает функции IQP, появившиеся в 2017+ и 2019+
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 150;

Что такое оценщик кардинальности?

Оценщик кардинальности (Cardinality Estimator, CE) — это компонент оптимизатора запросов SQL Server, отвечающий за оценку количества строк, которое будет возвращено каждым оператором в плане запроса — сканированием, соединением, фильтром, агрегацией и так далее.

Эти оценки количества строк, вероятно, являются самым влиятельным фактором в принятии решений оптимизатором. На основе оценок кардинальности оптимизатор решает:

• Использовать ли вложенный цикл, хэш-соединение или соединение слиянием.
• Использовать ли поиск (Seek) или сканирование (Scan).
• Сколько памяти выделить для операций сортировки/хэширования.
• Распараллеливать ли запрос и до какой степени (DOP).
• Порядок соединения таблиц.

Если оценки CE близки к реальности, оптимизатор обычно принимает хорошие решения. Если оценки сильно ошибочны (например, он оценивает 10 строк, а возвращается 10 миллионов), то вы получаете хэш-соединения со сбросом в tempdb, недостаточные гранты памяти, последовательное выполнение там, где нужен параллелизм, или вложенные циклы, которые миллионы раз обращаются к огромной таблице. Это одна из самых распространённых причин того, что «запрос раньше работал нормально, а теперь стал медленным».

Вы можете увидеть оценки CE и фактические количества строк непосредственно в плане выполнения:

SET STATISTICS XML ON;
GO
SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2026-01-01';
GO
SET STATISTICS XML OFF;

В полученном плане наведите курсор на любой оператор и сравните Estimated Number of Rows с Actual Number of Rows (или используйте SET STATISTICS PROFILE ON / представление «Фактический план выполнения» в SSMS). Большое расхождение между ними — классический симптом проблемы оценки кардинальности.

Устаревший CE и новый CE

Именно здесь возникает большая часть путаницы и большинство реальных проблем.

Устаревший оценщик кардинальности (CE версии 70)

С SQL Server 7.0 до SQL Server 2012 включительно SQL Server использовал одну и ту же фундаментальную модель оценки кардинальности, внутренне обозначаемую как CE версии 70. За почти два десятилетия у неё накопились известные слабости:

• Предположение о независимости: предполагалось, что фильтры по разным столбцам одной таблицы статистически независимы друг от друга, что часто неверно в реальных данных (например, Город = 'Москва' и Регион = 'Москва' сильно коррелированы, а не независимы).
• Проблемы оценки для восходящих ключевых столбцов (например, столбцов идентификаторов, столбцов дат, где новые строки всегда имеют значение больше максимального в статистике), поскольку значения за пределами максимума гистограммы оценивались плохо.
• Слабая обработка многоколоночных предикатов, соединений со сложными комбинациями фильтров и коррелированных столбцов в целом.

Новый оценщик кардинальности (CE версии 120+)

С выходом SQL Server 2014 Microsoft выпустила существенно переписанный оценщик кардинальности — первый крупный пересмотр за почти 20 лет. Он изменил несколько фундаментальных предположений:

• Экспоненциальное затухание для комбинирования селективности нескольких предикатов вместо чистой независимости, что обычно даёт более реалистичные оценки, когда фильтры коррелированы.
• Улучшенная обработка оценки соединений, особенно для сценариев с возрастающими/растущими ключами.
• Другие статистические алгоритмы для строковых предикатов, сценариев «нет совпадающей статистики» и подсчётов уникальных значений.

Новый CE — это не просто исправленная версия старого, а другая статистическая модель. Именно поэтому он опасен: для огромного числа запросов новая модель даёт лучшие оценки и лучшие планы. Но для значительного меньшинства запросов, особенно в старых, сильно завязанных на подсказки базах данных, где индексы и шаблоны запросов были построены с учётом особенностей старой модели, новый CE может давать планы хуже, чем раньше, иногда значительно хуже. Это стало одной из самых печально известных «ловушек» при обновлении до SQL Server 2014: организации обновлялись, в тестировании всё выглядело хорошо, а затем конкретные производственные запросы сильно регрессировали под нагрузкой.

Как версия CE связана с уровнем совместимости

Это ключевой момент в отношениях между этими двумя понятиями. Модель CE, используемая запросом, определяется уровнем совместимости базы данных (с некоторыми возможностями переопределения, описанными ниже):

Уровень совместимости	Используемая версия CE
≤ 110 (SQL Server 2012 и ранее)	Устаревший CE (CE 70)
120 (SQL Server 2014)	Новый CE (модель 2014)
130 (SQL Server 2016)	Новый CE (усовершенствование 2016)
140 (SQL Server 2017)	Новый CE (усовершенствование 2017)
150 (SQL Server 2019)	Новый CE (усовершенствование 2019)
160 (SQL Server 2022)	Новый CE (усовершенствование 2022)

Таким образом, когда вы повышаете уровень совместимости базы данных, скажем, с 110 до 150 в рамках проекта модернизации, вы одновременно переводите эту базу данных на совершенно другую модель оценки кардинальности, а также на все другие изменения синтаксиса/поведения, связанные с этим скачком уровня совместимости. Вот почему «просто поднять уровень совместимости» иногда вызывает неожиданные регрессии планов. Переключение CE — это «молчаливый пассажир», путешествующий вместе с изменением уровня совместимости.

Важный нюанс, начиная с SQL Server 2016: Microsoft вносила небольшие улучшения в CE в рамках семейства «нового CE» при каждом повышении версии (130, 140, 150, 160 ведут себя несколько по-разному), а не просто заморозила единый монолитный «новый CE» на модели 2014 года. Таким образом, уровень совместимости 130 и уровень совместимости 150 — оба «новый CE», но они не идентичны в каждом сценарии оценки.

Отвязка CE от уровня совместимости

Понимая, что принудительное изменение CE вместе со всеми другими изменениями поведения уровня совместимости рискованно, Microsoft предоставила администраторам возможность управлять CE независимо от уровня совместимости, начиная с SQL Server 2016 SP1.

Метод 1: Конфигурация на уровне базы данных (рекомендуется, SQL Server 2016+)

-- Принудительно использовать устаревший CE независимо от уровня совместимости
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

-- Вернуться к использованию CE, соответствующего уровню совместимости
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;

-- Проверить текущую настройку
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

Это мощный инструмент, потому что он позволяет использовать все возможности нового уровня совместимости (синтаксис, функции IQP), всё ещё работая со старой моделью CE для оценки кардинальности, — полезно как временное решение, пока вы исследуете и исправляете конкретные регрессировавшие запросы.

Метод 2: Флаги трассировки (на уровне экземпляра или запроса)

-- Принудительно использовать новый CE на уровне сессии или запроса
SELECT * FROM Orders OPTION (QUERYTRACEON 2312);

-- Принудительно использовать устаревший CE на уровне сессии или запроса
SELECT * FROM Orders OPTION (QUERYTRACEON 9481);

Флаг трассировки 2312 принудительно использует новую модель CE.
Флаг трассировки 9481 принудительно использует устаревшую модель CE.

Их также можно включить на уровне всего экземпляра как стартовые флаги трассировки, но управление на уровне запроса или базы данных почти всегда является лучшим, более точным подходом в современных версиях SQL Server.

Метод 3: Подсказка на уровне запроса (альтернативный синтаксис)

SELECT * FROM Orders
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Это даёт тот же эффект, что и флаг трассировки 9481, но использует более современный синтаксис USE HINT, появившийся в SQL Server 2016 SP1, который не требует знаний о флагах трассировки на уровне sysadmin и более нагляден/самодокументируем в тексте запроса.

Практический пример для демонстрации разницы

Вот упрощённая иллюстрация того, как изменение уровня совместимости может изменить форму плана исключительно за счёт CE, при неизменном запросе.

-- Сценарий: коррелированные столбцы, устаревший CE предполагает независимость
CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    Department VARCHAR(50),
    JobTitle VARCHAR(50),
    Country VARCHAR(50)
);
-- Предположим, что Department = 'Engineering' и JobTitle = 'Software Engineer'
-- сильно коррелированы (большинство Software Engineers находятся в Engineering)

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
SELECT * FROM dbo.Employees
WHERE Department = 'Engineering' AND JobTitle = 'Software Engineer';
-- Устаревший CE: умножает селективность каждого предиката независимо,
-- имеет тенденцию НЕДООЦЕНИВАТЬ строки для таких коррелированных предикатов

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
SELECT * FROM dbo.Employees
WHERE Department = 'Engineering' AND JobTitle = 'Software Engineer';
-- Новый CE: применяет экспоненциальное затухание вместо чистого умножения,
-- обычно даёт БОЛЕЕ ВЫСОКУЮ, более реалистичную оценку строк здесь

В реальной среде с миллионами строк эта разница в оценке может быть решающим фактором между выбором оптимизатором дешёвого вложенного цикла (корректно, когда оценка говорит «10 строк») и хэш-соединения с большим грантом памяти (корректно, когда фактическое число — «50 000 строк»). Ошибитесь с оценкой в любую сторону — и вы заплатите за это: либо чрезмерным количеством итераций цикла, либо сбросом гранта памяти в tempdb.

Практические рекомендации

Несколько проверенных на практике принципов при работе с CL и CE вместе:

• Никогда не повышайте уровень совместимости вслепую во время обновления. Относитесь к этому как к отдельному, проверяемому изменению, а не как к автоматическому побочному эффекту перехода на новое оборудование или новую версию SQL Server. Обновите экземпляр, оставьте уровень совместимости на прежнем месте, проверьте, затем повышайте его намеренно (часто сначала в среде нижнего уровня, под представительной нагрузкой).
• Используйте хранилище запросов (Query Store) до и во время любого изменения уровня совместимости. Query Store позволяет захватывать планы и метрики времени выполнения до изменения, затем сравнивать регрессировавшие запросы после изменения и даже принудительно применять старый (или новый) план через sp_query_store_force_plan, пока вы проводите расследование.
• Если после повышения уровня совместимости вы видите регрессию, изолируйте, является ли причиной CE или что-то другое. Используйте LEGACY_CARDINALITY_ESTIMATION на уровне базы данных или OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) на уровне запроса, чтобы проверить, исправляет ли регрессию возврат только CE (сохраняя другие возможности нового уровня совместимости). Если да, вы подтвердили, что виновник — CE, и можете принять решение между целевой подсказкой, обновлённой/отфильтрованной статистикой или постоянным переопределением устаревшего CE для этой базы данных.
• Держите статистику актуальной независимо от того, какой CE вы используете. Ни одна модель CE не может компенсировать устаревшую или отсутствующую статистику. UPDATE STATISTICS и правильные настройки автообновления/автосоздания статистики важнее, чем версия CE.
• База данных SQL Azure автоматически использует самую новую версию оценщика кардинальности (CE), которая соответствует её уровню совместимости. Microsoft иногда сначала опробует новые улучшения CE в Azure SQL Database, прежде чем выпускать их для обычной локальной версии SQL Server. Если вы управляете Azure SQL Database, вы всё равно можете проверить и установить уровень совместимости. Просто выполните тот же запрос к sys.databases, который вы использовали бы на обычном SQL Server. Это работает, даже если Azure постоянно обновляет базовый движок за кулисами.

Сводка

Критерий	Уровень совместимости	Оценщик кардинальности
Область действия	На базу данных	Определяется уровнем совместимости (или переопределяется независимо)
Что контролирует	Эмуляцию синтаксиса/поведения T-SQL, доступность функций оптимизатора (например, IQP)	Логику оценки количества строк для построения планов запросов
Внесённые изменения	Каждая версия SQL Server	Полная переработка в SQL Server 2014 (CE 120); дальнейшие улучшения в 2016, 2017, 2019, 2022
Можно изменить независимо?	Да, через ALTER DATABASE ... SET COMPATIBILITY_LEVEL	Да, начиная с SQL Server 2016 SP1, через LEGACY_CARDINALITY_ESTIMATION (конфигурация на уровне базы данных), флаги трассировки 2312/9481 или USE HINT

Уровень совместимости работает как главный переключатель. Оценщик кардинальности (CE) — это лишь одна из многих вещей, которые этот переключатель включает или выключает. Долгое время эти два параметра всегда менялись вместе, поэтому люди часто думают, что это одно и то же.

Но с SQL Server 2016 SP1 это уже не так. Вы можете изменить оценщик кардинальности самостоятельно, не затрагивая ничего остального, что контролирует уровень совместимости.

Почему это важно? Потому что оценщик кардинальности претерпел серьёзные изменения ещё в 2014 году. Если вы понимаете, что CE и уровень совместимости — это разные вещи, и что CE претерпел это большое изменение, вы часто можете исправить проблему с производительностью после обновления всего за пять минут. Без этих знаний та же проблема может превратиться в многодневную борьбу.