Заметки Дмитрия Пилюгина о Microsoft SQL Server 

PDF

Cardinality Estimation, СЕ (оценка кардинальности) – это оценка предполагаемого числа строк, которое будет обработано тем или иным оператором запроса. Оценка – один из ключевых факторов при построении плана запроса (более подробно я рассматривал эту тему в докладе кардинальность и планы выполнения). Оценку числа строк осуществляет компонент Cardinality Estimator.

До 2014 сервера, была всего одна версия этого компонента, разработанная для SQL Server 7.0, постепенно адаптируемая к новым версиям, но принципиально не меняющаяся. Со временем, разработчики сиквела поняли, что старую модель больше развивать нельзя – ее трудно расширять, трудно тестировать, любые изменения в одном месте могут приводить к поломкам в другом, кроме того, те предположения о реальности, которые были верны во времена SQL Server 7.0, сейчас устарели.

Начиная с SQL Server 2014 у сервера появилась новая модель оценки строк, адаптированная к современным рабочим нагрузкам. Эта модель оценки имеет новую архитектуру, расширяема и дополняема, версия этой модели получила номер 120 (по аналогии с уровнем совместимости БД, соответствующим серверу 2014 – 120). В 2016 сервере современная модель была расширена и получила номер версии 130, при этом версия 120 сохранилась. В еще не вышедшем, на момент написания статьи, RTM сервере vNext уже есть модель версии 140.

Самая первая модель оценки по-прежнему доступна и имеет версию 70 (поскольку впервые появилась в SQL Server 7.0), ее я буду называть «устаревшей» (legacy). Совокупность новых моделей (120, 130, 140) я буду называть «современными» (modern). Таким образом у нас на данный момент есть два поколения моделей оценки:

• Устаревшее поколение (legacy) – версия 70 – все сервера младше 2014
• Современное поколение (modern) – версии 120, 130, 140 – сервера 2014, 2016, vNext

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

Для переключения между моделями MS предлагает несколько вариантов.

Самый общий уровень управления – это уровень совместимости БД (compatibility level). По умолчанию, сервер использует ту версию модели оценки (Cardinality Estimation Model Version), которая соответствует уровню совместимости БД в контексте которой выполняется запрос.

Однако, уровень совместимости БД, как средство управления моделью СЕ имеет один большой недостаток. Понижая уровень совместимости, и отказываясь от новой модели, вы заодно отказываетесь от многих других функций новой версии, не связанных с оценкой.

Для решения этой проблемы MS в 2016-ой версии сервера добавил параметр уровня БД LEGACY_CARDINALITY_ESTIMATION, управляемый при помощи ALTER DATABASE SCOPED CONFIGURATION. Таким образом, выполнив команду ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON, вы можете переключиться на устаревшую, legacy модель оценки. Важно понимать, что, например, находясь на уровне совместимости 130 и выполнив эту команду, сервер будет использовать версию модели 70 (legacy), а не 120 (modern).

Если же вам нужно управлять моделью оценки глобально (на уровне сервера) или очень точечно (на уровне запроса), начиная с 2014 сервера были доступны флаги трассировки: флаг 9481 – форсировать модель оценки legacy, и флаг 2312 – форсировать модель оценки modern, конкретная версия modern определяется уровнем совместимости БД.

Хинты FORCE_LEGACY_CARDINALITY_ESTIMATION и FORCE_DEFAULT_CARDINALITY_ESTIMATION обладают схожими функциями.

• FORCE_LEGACY_CARDINALITY_ESTIMATION – форсирует legacy модель оценки версии 70, аналогично флагу
• FORCE_DEFAULT_CARDINALITY_ESTIMATION – форсирует модель оценки, соответствующую уровню совместимости БД, т.е. переопределяет действие настройки LEGACY_CARDINALITY_ESTIMATION и флага 9481, если последний включен на уровне сессии или сервера.

Отличие от флага 2312

Несмотря на то, что в одной из статей поддержи MS флаг трассировки 2312 упомянут как альтернатива FORCE_DEFAULT_CARDINALITY_ESTIMATION:

Флаг 2312 имеет одно существенное отличие в поведении. Описание хинта в этой статье приведено правильное, так, если вы остаетесь на уровне совместимости БД 110 (вспомним, что ему соответствует модель legacy версии 70) и используете этот хинт, то вы останетесь на соответствующей ему версии СЕ, т.е. 70. Однако, если вы используете флаг 2312, то сервер переключится на модель modern, версии 120 (следующий после 70), оставаясь на уровне совместимости БД 110. В документации MSDN Query Hints (Transact-SQL) этой неточности нет.

Пример

Это обилие настроек может показаться запутанным, поэтому, рассмотрим несколько примеров.

-- Используем БД Microsoft для примеров, включим сбор плана (Ctrl+M)
use AdventureworksDW2016CTP3;
go
------------------------------------------------------------------------
-- Пример 1 - уровень совместимости 2016, устаревшая модель оценки ВЫКЛЮЧЕНА
alter database AdventureworksDW2016CTP3 set compatibility_level = 130;
alter database scoped configuration set legacy_cardinality_estimation = off;
go
set showplan_xml on;
go
-- 1.1 - Cardinality Estimation Model version = 130 (соответствует версии 2016)
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber;
-- 1.2 - Cardinality Estimation Model version = 70 (соответствует версии <= 2012)
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber
option(use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
go
set showplan_xml off;
go

------------------------------------------------------------------------
-- Пример 2 - уровень совместимости 2016, устаревшая модель оценки ВКЛЮЧЕНА
alter database AdventureworksDW2016CTP3 set compatibility_level = 130;
alter database scoped configuration set legacy_cardinality_estimation = on;
go
set showplan_xml on;
go
-- 2.2 - Cardinality Estimation Model version = 70 (соответствует версии <= 2012)
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber;
-- 2.2 - Cardinality Estimation Model version = 130 (соответствует версии 2016)
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber
option(use hint('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));
go
set showplan_xml off;
go

------------------------------------------------------------------------
-- Пример 3 - уровень совместимости 2012, устаревшая модель оценки ВЫКЛЮЧЕНА
alter database AdventureworksDW2016CTP3 set compatibility_level = 110;
alter database scoped configuration set legacy_cardinality_estimation = off;
go
set showplan_xml on;
go
-- 3.1 - Cardinality Estimation Model version = 70 (соответствует версии <= 2012)
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber;
-- 3.2 - Cardinality Estimation Model version = 70 (соответствует версии <= 2012) 
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber
option(use hint('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));
-- 3.3 - Cardinality Estimation Model version = 120 (соответствует версии 2014) 
select * 
from
    dbo.FactInternetSales s
    join dbo.FactResellerSalesXL_CCI sr on
        s.SalesOrderNumber = sr.SalesOrderNumber and
        s.SalesOrderLineNumber > sr.SalesOrderLineNumber
option(querytraceon 2312);
go
set showplan_xml off;
go

Я скомбинировал все планы выполнения, полученные в Plan Explorer, на одной картинке, где Statement – запрос который мы выполняли, CE Mode – версия модели оценки, Est Rows – предполагаемое число строк в данной модели оценки:

Рассмотрим получившиеся результаты. У нас компилировался один и тот же запрос, но под семью разными условиями, разбитыми на три группы (пример 1, 2, 3), в результате мы получили четыре разных плана.

Пример 1

В первом примере, уровень совместимости БД равен 130 (SQL Server 2016) поэтому, запрос 1.1, без хинтов и флагов, по умолчанию выполняется с моделью оценки 130. Ему соответствует план A, и оценочное число строк в плане 422 299.

Запрос 1.2 выполнялся с хинтом FORCE_LEGACY_CARDINALITY_ESTIMATION форсирующим legacy модель оценки — 70, ему соответствует план B, и для него предполагаемое число строк 51 273.

Также можно обратить внимание, что и план A, и план B имеют одинаковую форму, не параллельные, а один из операторов доступа Columnstore Index Scan (сканирование колоночного индекса). Благодаря наличию колоночного индекса есть возможность использовать режим выполнения Batch, а благодаря уровню совместимости БД 130 этот режим возможен в последовательном плане (до 2016 сервера режим Batch поддерживался только для параллельных планов).

Это не показано на картинке, но, действительно, если вы скомпилируете запрос и посмотрите на свойство Estimated Execution Mode в операторе плана Hash Match вы увидите значение Batch.

Пример 2

Во втором примере мы остались на прежнем уровне совместимости 130, но включили переход на устаревшую legacy модель оценки на уровне базы при помощи настройки LEGACY_CARDINALITY_ESTIMATION.

В запросе 2.1 без хинтов, мы по умолчанию используем уровень совместимости 130, но модель оценки 70 из-за настройки LEGACY_CARDINALITY_ESTIMATION, и мы получаем план B, предполагаемое количество строк 51 273, также, как и в запросе 1.2.

В запросе 2.2 хинтом FORCE_DEFAULT_CARDINALITY_ESTIMATION мы отменяем действие настройки и переключаемся в модель оценки modern, которая соответствует уровню совместимости БД 130, план А, предполагаемое количество строк 422 299, также, как и в запросе 1.1.

Пример 3

В третьем примере мы понижаем уровень совместимости до 110 (SQL Server 2012) и отключаем LEGACY_CARDINALITY_ESTIMATION, бессмысленно оставлять эту опцию включённой, ведь для уровня совместимости с 2012 версия модели оценки и так равна 70.

Запрос 3.1 выполнялся без хинтов, модель оценки, которая соответствует уровню 110 это 70, поэтому мы получаем оценку также как в запросе 1.2 (или 2.1), но при этом получаем другой план – план C. Так произошло потому, что уровень совместимости БД 110 не поддерживает режим Batch в последовательных планах, и чтобы снизить стоимость плана сервер решил построить параллельный план и уже в нем задействовать режим Batch. Если вы посмотрите свойство Estimated Execution Mode параллельного Hash Join, вы увидите значение Batch.

Запрос 3.2 выполнялся с хинтом FORCE_DEFAULT_CARDINALITY_ESTIMATION, но поскольку для уровня совместимости 110 «дефолтной» (т.е. по умолчанию) моделью оценки является legacy версия 70, то мы получаем такой же план, как и для запроса 3.1, план C.

Запрос 3.3 выполнялся с флагом трассировки 2312, для него был получен план D. Теперь вы видите, что хинт FORCE_DEFAULT_CARDINALITY_ESTIMATION и флаг 2312 не являются альтернативой друг другу в чистом виде. Дело в том, что в отличие от хинта, флаг переключает версию модели не на ту, что соответствует текущему уровню БД, а на первую modern версию которая больше или равна текущему уровню совместимости.

Так, если бы уровень совместимости был 130, но включена legacy модель оценки (как в примере 2), то этот флаг переключил бы на модель 130, т.к. первая modern версия, которая больше или равна уровню совместимости 130, это модель версии 130.

В примере 3, уровень совместимости 110, и первая modern версия – это версия 120. Также обратите внимание, что оценка строк для плана D – более 11 миллионов строк. Для этой оценки сервер не видит выгоды использовать параллельный план, а т.к. последовательный план в режиме Batch не доступен для уровня совместимости 110, то мы получаем последовательный план в режиме Row Mode. Этот план самый медленный из всех четырех, хотя в реальности вы вряд ли будете выбирать все столбцы и строки большой таблицы с колоночным индексом.

Заключение

Я надеюсь, что при помощи примеров, я в некоторой степени прояснил ситуацию, но на мой взгляд, это все еще довольно запутано. Если мы можем управлять уровнем совместимости при помощи конкретных значений (110, 120, 130 и т.д.), почему бы не сделать такую же настройку уровня базы, например, «ALTER DATABASE <mydb> SET CARDNILITY_ESTIMATION_MODEL_VERSION = 70/120/130/140» или ее аналог в DATABASE SCOPED CONFIGURATION?

Вместе с тем, можно было бы добавить такой же хинт OPTION (USE HINT (‘CARDNILITY_ESTIMATION_MODEL_VERSION = 70’)) и установить четкое правило, что более частная настройка переопределяет более общую. Это бы снизило число вопросов и объяснений на порядок.

Еще до того, как вышел SP1, я завел предложение по поводу хинта CARDNILITY_ESTIMATION_MODEL_VERSION на connect, но судя по тому, какие хинты мы уже получили такой хинт вряд ли добавят. Представьте, что наряду с имеющимися настройками у нас был бы еще один хинт, а ведь удалять уже выпущенное нельзя из-за проблем обратной совместимости, поэтому шансов на то, что это предложение пройдет мало, но, если вам нравится эта идея, вы можете проголосовать.

В следующей заметке мы поговорим про хинт ENABLE_QUERY_OPTIMIZER_HOTFIXES.

PDF