При переходе на более современные версии SQL Server (например, с 2008 на 2012 или выше) технические специалисты часто сталкиваются с неприятным сюрпризом: стандартные отчеты, которые годами работали в файловом варианте или на PostgreSQL, начинают «обваливать» базу. Проблема сопровождается системными сообщениями «Достигнут предел служб выражений» или специфическими кодами ошибок SQL Server 8623 и 8632. Проанализируем, почему это происходит и как исправить ситуацию, не переписывая всю конфигурацию с нуля.
Рассмотрим техническую сторону вопроса. Ошибка 8623 («The query processor ran out of internal resources and could not produce a query plan») и 8632 («Internal error: An expression services limit has been reached») возникают на этапе компиляции запроса. Когда пользователь в 1С устанавливает отбор «В списке» или «В группе из списка», и этот список содержит тысячи элементов (например, перечень номенклатуры или контрагентов), платформа 1С транслирует этот список в SQL-запрос как гигантское перечисление в операторе IN.
Разберем, что происходит внутри СУБД. SQL Server пытается построить дерево операторов для оптимизации выполнения. Если количество констант в условии IN превышает определенный внутренний лимит (обычно это связано с глубиной стека или доступной памятью для парсера), оптимизатор «сдается», так как не может построить эффективный план выполнения для такого перегруженного дерева выражений. Особенно остро это проявляется в SQL Server 2012, где механизмы оценки кардинальности стали строже по сравнению с версией 2008.
Ситуация усугубляется, если используется платформа 1С 8.2 в режиме совместимости с 8.1. В этом режиме механизмы работы с СУБД менее интеллектуальны: они часто подставляют значения напрямую в текст запроса вместо использования параметров или временных таблиц. Именно поэтому «Построитель отчетов», используемый в старых версиях «Управления торговлей 10.3», так чувствителен к этой ошибке.
Посмотрим на самый надежный с точки зрения архитектуры способ — использование временных таблиц (ВТ). Проанализируем разницу в подходах. Вместо того чтобы передавать список значений напрямую в условие ГДЕ Ссылка В (&СписокЗначений), мы должны сначала поместить этот список во временную таблицу, а затем соединиться с ней.
Если отчет написан на СКД (Система компоновки данных), платформа во многих случаях делает это автоматически. Однако в старых отчетах на «Построителе отчетов» нам придется вмешаться в код. Рассмотрим пример модификации запроса:
// Вместо стандартного условия:
// ГДЕ Ссылка В (&СписокСсылок)
// Используем подход с временной таблицей:
МенеджерВТ = Новый МенеджерВременныхТаблиц;
ЗапросВТ = Новый Запрос;
ЗапросВТ.МенеджерВременныхТаблиц = МенеджерВТ;
ЗапросВТ.Текст =
"ВЫБРАТЬ
| ТекСписок.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТ_Фильтр
|ИЗ
| &СписокЗначений КАК ТекСписок";
ЗапросВТ.УстановитьПараметр("СписокЗначений", СписокДляОтбора);
ЗапросВТ.Выполнить();
// В основном запросе отчета делаем соединение:
// ИЗ Справочник.Номенклатура КАК Номенклатура
// ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Фильтр КАК ВТ_Фильтр
// ПО Номенклатура.Ссылка = ВТ_Фильтр.Ссылка
Этот метод полностью снимает ограничение «предела служб выражений», так как для SQL-сервера это становится обычным соединением двух таблиц, для которого легко построить план выполнения.
Если переписывать десятки типовых отчетов «Управления торговлей» слишком трудозатратно, проанализируем возможность настройки самого сервера. Существует несколько параметров, которые могут помочь «проглотить» тяжелые запросы:
IN.Max Degree of Parallelism = 1. При попытке распараллелить построение плана для огромного списка выражений, ресурсы стека исчерпываются значительно быстрее.Проанализируем ситуацию, когда серверу просто не хватает оперативной памяти для обработки дерева выражений. В настройках SQL Server есть параметр «min memory per query» (минимальный объем памяти на запрос). По умолчанию он равен 1024 КБ. Иногда его увеличение может помочь, но будьте осторожны: установка слишком большого значения (например, как пробовал автор темы — 15 ГБ) скорее навредит, так как ограничит количество одновременно выполняемых запросов и приведет к ошибкам ожидания ресурсов.
Рекомендуется также обратить внимание на опцию «Optimize for Ad hoc Workloads». Её включение позволяет серверу не кэшировать целиком планы для разовых, уникальных запросов (которыми и являются запросы с огромными списками констант), что освобождает память в Procedure Cache для более важных задач.
Выясним причину падения базы: это не «слабость» SQL Server по сравнению с файловой версией, а следствие более сложной и строгой архитектуры оптимизатора в новых версиях. Файловая версия и PostgreSQL обрабатывают предикаты IN иначе, не пытаясь строить столь глубокие деревья операторов.
Для стабильной работы системы в связке 1С 8.2 + SQL 2012 мы рекомендуем следующий алгоритм действий:
MAXDOP = 1.МенеджерВременныхТаблиц.Такой комплексный подход позволит избавиться от критических завершений работы пользователей и обеспечит стабильную работу даже на сильно переписанных конфигурациях прошлых лет.