Введение
В документации находим определение термина: Parameter sensitivity, also known as "parameter sniffing", refers to a process whereby SQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.
Генерация SQL сервером плана выполнения запроса требует ресурсов. Поэтому при первом выполнении запроса, план сохраняется и используется для одинаковых запросов повторно. Параметры запроса не сохраняются, однажды составленный план применяется независимо от параметров. Но при неравномерном распределении данных, выбор оптимального плана запроса может зависеть от параметров (чувствительность к параметрам, parameter sensitivity).
Рассмотрим практический пример
непериодический регистр сведений «НеравномерноеРаспределениеДанных» (InfoRg48221)
незримо присутствует разделитель учета (Fld11560)
первое измерение «Счетчик» (Fld48223)
второе измерение «Организация», индексировано (Fld48222RRef)
ресурс – «Значение» (Fld48224)
Имена полей СУБД получены функцией ПолучитьСтруктуруДанных().
В данном случае, с регистром связано два индекса:
(1) Кластерный в порядке измерений регистра, который содержит все поля, в том числе значения ресурсов регистра (Fld48224).
(2) Индекс по организации не содержит значения ресурсов (Fld48224). Для запросов по ресурсам индекс будет заведомо непокрывающим, поскольку не содержит всех необходимых полей.
Внесем в регистр 99.9% записей ООО «Ромашка», 0.1% записей ООО «Рога и копыта»
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
НаборСчетчикиВыгрузок = РегистрыСведений.НеравномерноеРаспределениеДанных.СоздатьНаборЗаписей();
Для Счетчик = 0 По 100000 Цикл
ЗаписьСчетчикиВыгрузок = НаборСчетчикиВыгрузок.Добавить();
Если цел(Счетчик/1000) = Счетчик/1000 Тогда
ЗаписьСчетчикиВыгрузок.Организация = Организация1;
Иначе
ЗаписьСчетчикиВыгрузок.Организация = Организация99;
КонецЕсли;
ЗаписьСчетчикиВыгрузок.Счетчик = Счетчик;
ЗаписьСчетчикиВыгрузок.Значение = 100000-Счетчик;
КонецЦикла;
НаборСчетчикиВыгрузок.Записать();
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
В некоторых запросах значения ресурсов по ООО «Ромашка» (99.9%) выгоднее получить - просмотреть всю таблицу, использовать кластерный индекс (1) (Clustered index scan). Значения ресурсов по ООО «Рога и копыта» (0.1%) выгоднее получить - выбрать по индексу (2) значения организации и присоединить значения ресурсов (Index seek + Key Lookup).
Но в обоих случаях будет использоваться план, помещенный в кеш при первом выполнении, который соответствует только одному из вариантов.
Просматривать выполненные запросы на сервере СУБД будем с помощью динамических представлений (3).
-----------------------------------------------------------------------------------------------
SELECT
eqs.last_execution_time,
eqs.creation_time,
eqs.sql_handle,
eqs.plan_handle,
eqp.query_plan,
est.text
FROM sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est
CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) AS eqp
WHERE eqs. execution _time > '2021-26-05 13:40:00'
AND est.text like '%Rg48221%'
ORDER BY eqs.last_execution_time
-----------------------------------------------------------------------------------------------
Используем ограничение по времени выполнения и имени таблицы. Подробности в документации
Выполним запрос по ООО «Рога и копыта» (0.1%)
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
"ВЫБРАТЬ
| НеравномерноеРаспределениеДанных.Значение КАК Значение,
| НеравномерноеРаспределениеДанных.Организация КАК Организация
|ИЗ
| РегистрСведений.НеравномерноеРаспределениеДанных КАК НеравномерноеРаспределениеДанных
|ГДЕ
| НеравномерноеРаспределениеДанных.Организация = &Организация";
Запрос.УстановитьПараметр("Организация", Организация);
РезультатЗапроса = Запрос.Выполнить();
ВыборкаДетальная = РезультатЗапроса.Выбрать();
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Смотрим СУБД. Обратите внимание, информация в плане содержит только предполагаемые значения. С помощью dmv можно получить данные предварительного плана запроса. Данные из фактического плана можно увидеть например в трассировке.
Поиск в некластерном индексе (2), список столбцов совпадает с колонками индекса (2). Предполагаемое количество строк совпадает с фактическим (по способу заполнения).
Поиск по ключу в кластерном индексе (1), список столбцов содержит только значение ресурсов Fld48224.
creation_time 2021-05-27 11:22:35.810
last_execution_time 2021-05-27 11:22:35.810
sql_handle 0x02000000BC7EF3109836D5B073B04A84CBF3C7B5E3CA55F2
plan_handle 0x06001700BC7EF310404163F2050000000000000000000000
После выполнения запроса по ООО «Рога и Копыта» (0.1%) и заполнения кеша планов, выполним запрос по ООО «Ромашка» (99.9%). В dmv-запросе (3) добавляется строка, изменяется время последнего выполнения, визуальный план запроса не изменился.
creation_time 2021-05-27 11:22:35.810
last_execution_time 2021-05-27 11:58:29.600
sql_handle 0x02000000BC7EF3109836D5B073B04A84CBF3C7B5E3CA55F2
plan_handle 0x06001700BC7EF310404163F2050000000000000000000000
Очистим кеш планов командой с параметром plan_handle
DBCC FREEPROCCACHE(0x06001700BC7EF310404163F2050000000000000000000000)
Результат dmv-запроса (3) становится пустым. После очистки кешированных планов запросов, выполним запрос по ООО «Ромашка» (99.9%), sql_handle без изменения, время создания и время исполнения совпадают между собой, plan_handle изменился.
creation_time 2021-05-27 12:07:13.597
last_execution_time 2021-05-27 12:07:13.597
sql_handle 0x02000000BC7EF3109836D5B073B04A84CBF3C7B5E3CA55F2
plan_handle 0x06001700BC7EF3104041A0982F0000000000000000000000
Предполагаемое количество строк оптимизатор рассчитал, предполагая равномерное распределение данных, что в нашем случае неверно.
Обратите внимание, в среде выполнения запросов (3), выше плана запроса есть ремарка:
/*
Отсутствуют сведения об индексе из ExecutionPlan5.sqlplan
Обработчик запросов считает, что реализация следующего индекса может сократить стоимость запроса на 73.7991%.
*/
/*
USE [***********]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[_InfoRg48221] ([_Fld48222RRef],[_Fld11560])
INCLUDE ([_Fld48224])
GO
*/
Средствами 1С не удастся создать индекс, который включает разделитель учета (Fld11560), организацию (Fld48222RRef) и значение (Fld48224). Ремарка появилась, поскольку произошло сканирование кластерного индекса (1) (Cluster Index Seek с предикатом – разделитель учета выбирает все записи). Для ООО "Ромашка" (99.9%), это оправданно, поскольку необходимы почти все записи регистра, а полей в регистре немного. Для ООО "Рога и Копыта" (0.1%) это приведет к избыточному чтению.
Неоптимальный план может содержать и другие операторы, отличные от приведенных в примере. Если parameter sniffing становится проблемой, запрос работает медленно — его видно средствами мониторинга неоптимальных запросов. Как сделать, чтобы для разных наборов параметров, СУБД выбирала оптимальный план запроса?
Что делают разработчики СУБД?
Стандартные решения:
- добавить хинт/опцию в текст запроса Optimize @Pxx for unknown (использовать Plan Guide)
- отключить Parameter sniffing флагом 4136 или в настройках MS SQL 2016 и позже
- использовать в запросе локальные переменные
- использовать динамический текст запроса
упоминание о проблемах parameter sniffing можно встретить часто в вопросах и ответах специалистов.
Что доступно разработчикам 1С?
Если поле типа булево, то зачастую данные по нему распределены неравномерно. Например, флажок «Особенный товар» отсутствует у большинства товаров. Любопытно, что параметры запроса типа булево включаются в текст запроса на уровне платформы, один запрос 1С соответствует разным запросам СУБД.
Мы можем пойти по тому же пути, изменять текст запроса, чтобы СУБД выбирала для него уникальный план. Выполним код 1С:
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| НеравномерноеРаспределениеДанных.Счетчик КАК Счетчик,
|&МеткаЗапроса
|ИЗ
|РегистрСведений. НеравномерноеРаспределениеДанных КАК НеравномерноеРаспределениеДанных
|ГДЕ
| НеравномерноеРаспределениеДанных.Организация = &Организация";
Запрос.Текст = СтрЗаменить(Запрос.Текст, "&МеткаЗапроса", "Истина, Ложь, ""abc"", 123");
Запрос.УстановитьПараметр("Организация", Организация);
РезультатЗапроса = Запрос.Выполнить();
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Получим запрос в СУБД:
-----------------------------------------------------------------------------------------------
(@P1 nvarchar(4000),@P2 numeric(10),@P3 numeric(10),@P4 varbinary(16))SELECT T1._Fld48223, 0x01, 0x00, @P1, @P2 FROM dbo._InfoRg48221 T1 WHERE ((T1._Fld11560 = @P3)) AND ((T1._Fld48222RRef = @P4))
-----------------------------------------------------------------------------------------------
Этот пример показывает, что использование в качестве метки запроса числа или строки не приносит желаемого результата: они переходят в параметры. Поведение компилятора может изменяться, здесь платформа 1С:Предприятие 8.3 (8.3.17.1851). Для каждой организации метка запроса должна содержать уникальную последовательность логических значений, тогда запросы будут уникальны по каждой организации.
И еще одна близкая тема, связанная с генерацией планов, на которую есть намек на ИТС.
Не использовать в запросе функции от параметров.
Выполним код 1С в разные моменты времени
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Запрос = Новый Запрос ("ВЫБРАТЬ МИНИМУМ(&Период) КАК Период");
Запрос.УстановитьПараметр( "Период", ТекущаяДата() );
РезультатЗапроса = Запрос.Выполнить();
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Как Вы догадались, на стороне СУБД запросы разные.
-----------------------------------------------------------------------------------------------
sql_handle 0x020000005B8E5C00E3A31CE9CFD7C5776CCC32A71AA1E71D
text SELECT MIN({ts '4021-05-20 11:42:01'})
sql_handle 0x0200000054E6421B88E9D5DD77378ECB0B09EDD9C62AAECE
text SELECT MIN({ts '4021-05-20 11:42:09'})
-----------------------------------------------------------------------------------------------
Запрос, использующий функцию от параметра, компилируется как новый для каждого параметра, потребляя излишние ресурсы, заполняет кеш планов бесполезной информацией.
Если запрос содержит условие вхождения в список, например "ГДЕ НеравномерноеРаспределениеДанных.Организация В ( &ИзбранныеОрганизации )" и элементов списка меньше одной тысячи, то компилятор 1С преобразует список в логическое выражение, вставит в текст запроса все элементы списка через ИЛИ. Для разной длины списка, оптимизатор SQL Server генерирует разные планы запроса.
Заключение
Мне было интересно написать эту статью - посмотрим, насколько Вам будет интересно ее прочитать. Всем удачи !
