При работе в конфигурации «1С:Управление торговлей 10.3» (или других конфигурациях на обычных формах, таких как УПП 1.3 или КА 1.1) разработчики и администраторы часто сталкиваются с критической ошибкой СУБД при использовании стандартной обработки «Групповая обработка справочников и документов». При попытке наложить фильтр по свойствам и категориям система аварийно завершает работу, а в логах SQL Server фиксируется ошибка:
«Для выполнения этого запроса недостаточно системной памяти в пуле ресурсов "internal"» (в английской версии: There is insufficient system memory in resource pool 'internal' to run this query, код ошибки 701 или 8623).
В этой статье мы подробно разберем технические причины возникновения этой проблемы и рассмотрим эффективные практические способы ее решения.
Многие ошибочно полагают, что серверу СУБД физически не хватает оперативной памяти (ОЗУ). Однако в данном случае это не так. Проблема кроется в особенностях работы встроенного оптимизатора запросов MS SQL Server (Cost-Based Optimizer) и механизмах построения запросов платформой 1С.
Давайте проанализируем ситуацию по шагам:
internal. Когда соединений слишком много, лимиты этого пула мгновенно исчерпываются, и СУБД принудительно прерывает компиляцию запроса еще до начала его фактического выполнения.ЗначенияСвойствОбъектов измерение Объект имеет составной тип данных (включающий сотни типов справочников и документов). Если запрос не ограничивает этот тип явно, СУБД на физическом уровне вынуждена выполнять соединения со всеми возможными таблицами БД, которые входят в составной тип. Из-за этого 200 явных соединений в 1С превращаются в тысячи скрытых соединений на уровне SQL Server.Самый надежный способ оптимизировать динамический запрос на уровне платформы 1С — это принудительное ограничение составных типов данных. Для этого мы должны использовать конструкцию ВЫРАЗИТЬ.
Рассмотрим пример. Вместо прямого обращения к объекту в соединении:
ГДЕ
ЗначенияСвойств.Объект = Номенклатура.Ссылка
Необходимо явно указать СУБД, к какому именно типу приводится объект. Это исключит лишние неявные соединения с таблицами других документов и справочников:
ГДЕ
ВЫРАЗИТЬ(ЗначенияСвойств.Объект КАК Справочник.Номенклатура) = Номенклатура.Ссылка
Внедрить это непосредственно в типовой конструктор стандартной групповой обработки крайне сложно, так как запрос там собирается программно. Однако этот принцип критически важно соблюдать при написании собственных отчетов и обработок.
Если стандартный инструмент отказывается работать из-за сложности запроса, лучшим выходом будет написание собственной мини-обработки. Чтобы обойти ограничение СУБД, мы должны отказаться от одного гигантского запроса с сотней левых соединений в пользу пакета простых запросов с использованием временных таблиц.
Разберем по шагам алгоритм создания такой обработки:
Посмотрим на пример кода 1С, который решает эту задачу быстро и не нагружает оптимизатор СУБД:
// Создаем запрос с пакетом и временными таблицами
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Шаг 1: Помещаем исходный список номенклатуры во временную таблицу
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТ_ИсходныеОбъекты
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| НЕ Номенклатура.ЭтоГруппа";
Запрос.Выполнить();
// Шаг 2: Последовательно фильтруем по свойствам
// Допустим, у нас есть МассивСвойствИЗначений, содержащий структуру с отборами
Для Каждого СтрокаОтбора Из МассивСвойствИЗначений Цикл
Запрос.УстановитьПараметр("ТекущееСвойство", СтрокаОтбора.Свойство);
Запрос.УстановитьПараметр("ТекущееЗначение", СтрокаОтбора.Значение);
Запрос.Текст =
"ВЫБРАТЬ
| ВТ_ИсходныеОбъекты.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТ_ФильтрованныеОбъекты
|ИЗ
| ВТ_ИсходныеОбъекты КАК ВТ_ИсходныеОбъекты
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойств
| ПО (ВЫРАЗИТЬ(ЗначенияСвойств.Объект КАК Справочник.Номенклатура) = ВТ_ИсходныеОбъекты.Ссылка)
|ГДЕ
| ЗначенияСвойств.Свойство = &ТекущееСвойство
| И ЗначенияСвойств.Значение = &ТекущееЗначение;
|
|////////////////////////////////////////////////////////////////////////////////
|УНИЧТОЖИТЬ ВТ_ИсходныеОбъекты;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ФильтрованныеОбъекты.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТ_ИсходныеОбъекты
|ИЗ
| ВТ_ФильтрованныеОбъекты КАК ВТ_ФильтрованныеОбъекты;
|
|////////////////////////////////////////////////////////////////////////////////
|УНИЧТОЖИТЬ ВТ_ФильтрованныеОбъекты";
Запрос.Выполнить();
КонецЦикла;
// Шаг 3: Получаем итоговый список
Запрос.Текст =
"ВЫБРАТЬ
| ВТ_ИсходныеОбъекты.Ссылка КАК Ссылка
|ИЗ
| ВТ_ИсходныеОбъекты КАК ВТ_ИсходныеОбъекты";
РезультатТаблица = Запрос.Выполнить().Выгрузить();
// Теперь РезультатТаблица содержит только те элементы, которые удовлетворяют всем критериям
Такой подход позволяет СУБД выполнять простейшие и очень быстрые соединения один на один. Нагрузка на память сервера при этом стремится к нулю, а скорость выполнения вырастает в десятки раз.
Если у вас нет времени на написание собственного кода, можно воспользоваться уже готовыми и отлично зарекомендовавшими себя инструментами сообщества 1С:
Если проблему нужно локализовать "прямо сейчас", а менять программный код или устанавливать сторонний софт нет возможности, системный администратор может предпринять следующие шаги непосредственно на СУБД:
internal, если она забита старыми неоптимальными планами. Выполняется командой:
DBCC FREEPROCCACHE;
Внимание! Эта операция временно снизит производительность всей базы данных, так как все последующие запросы будут компилироваться заново.
max server memory (MB) имеет жесткое ограничение (не оставлен по умолчанию). Если СУБД съедает всю доступную физическую память системы, внутренние пулы начинают урезаться операционной системой, что провоцирует ошибку 701 намного раньше.Помните, что административные меры на стороне СУБД — это лишь «костыли». Единственным полноценным решением проблемы является оптимизация структуры запроса в 1С путем отказа от гигантских соединений в пользу пакетов временных таблиц и приведения типов.