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

PDF

Оптимизатор запросов, это компонент SQL Server, который отвечает за то, как именно будет выполняться запрос. Это довольно сложный механизм, и разработчики, которые его пишут, не застрахованы от ошибок. Сложность в исправлении ошибок оптимизатора заключается в том, что даже если найдена ошибка, приводящая к неэффективному плану, нельзя ее просто исправить. Исправление этой ошибки для какого-то проблемного запроса или типа запросов, может привести к смене плана выполнения в тех запросах, которые до это не испытывали проблем. По этой причине разработчики оптимизатора очень консервативны при внесении исправлений в оптимизатор. Очень часто, внеся исправления, они оставляют их по умолчанию выключенными, чтобы, если вы не испытываете проблем, вы их не заметили, и они никак не повлияли на производительность вашего приложения (исключение составляют серьезные ошибки, например, те, которые могут вести к неверным результатам). В то же время, если у вас есть проблемы, вы могли бы включить эти исправления и получить нужный эффект.

До введения хинта ENABLE_QUERY_OPTIMIZER_HOTFIXES, тумблером, включавшим эти исправления, был флаг трассировки 4199 (а до него, набор отдельных флагов трассировки). Не все исправления вносятся под флаг 4199, а только те, что в следующей версии сервера планируется оставить по умолчанию включенными. Исправления или изменение поведения оптимизатора для каких-то особенных, редких и частных случаев, как правило, контролируются другими флагами.

Начиная с SQL Server 2016 исправления оптимизатора также контролируются уровнем совместимости БД. Подробнее об этом можно почитать в документации, в статье SQL Server query optimizer hotfix trace flag 4199 servicing model.

Кроме того, в SQL Server 2016 появился параметр БД, при помощи которого можно не трогая уровень совместимости, не используя флаги трассировки или хинты включить исправления оптимизатора в определенной базе данных. Для этого нужно выполнить команду:

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

Также есть возможность установить этот параметр для secondary:

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = ON;

Давайте рассмотрим пример, который я выполню на версии SQL Server 2016 SP1 CU1. Пример включает себя запрос к секционированной таблице, состоящей из одной секции, секционирование будет по столбцу а. При этом, я создам индекс по столбцу b, и укажу в запросе сортировку по данному полю. Я использую тестовую БД opt, отсюда.

-- Переключимся в нужную БД
use opt;
go
alter database opt set compatibility_level = 130;
go
-- Создадм функцию и схему в которой будет всего одна секция
create partition function pf(int) as range for values()
create partition scheme ps as partition pf all to ([primary]);
go
-- Создадим тестовую таблицу
drop table if exists t;
create table t (a int identity primary key, b int, c int) on ps(a);
create nonclustered index ix_b on t(b) on ps(a)
insert t(b) values (1);
go

set showplan_xml on;
go
-- 1. Без хинтов и флагов
select (select b) from t where b < 10 order by b;
-- 2. Хинт ENABLE_QUERY_OPTIMIZER_HOTFIXES
select (select b) from t where b < 10 order by b option(use hint ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));
-- 3. Флаг трассировки 4199
select (select b) from t where b < 10 order by b option(querytraceon 4199)
go
set showplan_xml off;
go

-- Включим на уровне БД
alter database scoped configuration set query_optimizer_hotfixes = on;
go
set showplan_xml on;
go
-- 4. Set query_optimizer_hotfixes = on;
select (select b) from t where b < 10 order by b;
go
set showplan_xml off;
go
-- Очистим тестовые данные
drop table t;
drop partition scheme ps;
drop partition function pf;
go

Получившиеся планы запросов:

Вы можете видеть, что в запросе 1 есть ненужный оператор сортировки, т.к. нам нет необходимости сортировать по секции, когда секция одна, в запросах 2, 3 и 4, где мы так или иначе включили исправления оптимизатора (при помощи хинта ENABLE_QUERY_OPTIMIZER_HOTFIXES, флага 4199 или настройки БД) сортировка исчезла.

Также стоит сказать, что исправления оптимизатора могут в себя включать исправление оценок предполагаемого числа строк, поскольку компонент Cardinality Estimator – это часть оптимизатора. Подробнее про Cardinality Estimator и его версии читайте в предыдущей заметке.

Рассмотрим пример:

use opt;
go
-- Переключимся на уровень 2014
alter database opt set compatibility_level = 120;
-- Создадим многоколоночную статистику
create statistics s_bc on t1(b,c);
go
set showplan_xml on;
go
-- 1. По умолчаию оптимиазтор в 2014 не использует многоколоночную статистику для оценки AND предикатов равенства
select * from t1 where t1.b = 1 and t1.c = 1;
-- 2. Мы можем использовать хинт ENABLE_QUERY_OPTIMIZER_HOTFIXES, чтобы задействовать исправление, позволяюее использовать статистику
select * from t1 where t1.b = 1 and t1.c = 1 option(use hint ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));
-- 3. Даже если мы с хинтом FORCE_DEFAULT_CARDINALITY_ESTIMATION для 120 CE статистика продолжает использоваться
select * from t1 where t1.b = 1 and t1.c = 1 option(use hint ('FORCE_DEFAULT_CARDINALITY_ESTIMATION', 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'));
go
set showplan_xml off;
go
-- Переключимся обратно на уровень 2016
alter database opt set compatibility_level = 130;
-- Удалим статистику
drop statistics t1.s_bc;
go

При оценке кардинальности коррелированных предикатов оптимизатор, при определенных условиях, мог использовать многоколоночную статистику. В 2014 версии сервера, эту возможность убрали, однако вернули в 2016 (можете почитать подробнее в статье Cardinality Estimation for Correlated Columns in SQL Server 2016).

В примере, мы переключились на уровень совместимости с 2014 сервером, скомпилировали запрос 1 без всяких подсказок и получили оценку 1.41 строк, эта оценка не использует многоколоночную статистику. Запрос 2 был скомпилирован с подсказкой ENABLE_QUERY_OPTIMIZER_HOTFIXES. Оценка строк – 10 строк, использовалась многоколоночная статистика, при этом версия оценщика строк остается равной 120, т.е. соответствует серверу 2014, который не использует многоколоночную статистику. В третьем примере, даже если форсировать версию оценки 120 (которая и так 120), оценка не изменится, оптимизатор продолжит использовать многоколоночную статистику, т.к. у нас включены исправления оптимизатора.

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

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

PDF