Каталог решений - Parameter sniffing и генерация планов для разработчиков 1С

Введение

В документации находим определение термина: 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 можно встретить часто в вопросах и ответах специалистов.