При работе с информационными системами на базе 1С:Предприятие 8.3, особенно при интенсивном обмене данными между конфигурациями, такими как 1С:Бухгалтерия 3.0 и 1С:Розница Аптека 2.3, работающими на MS SQL Server, пользователи могут столкнуться с критической ошибкой: "The query processor ran out of stack space during query optimization. Please simplify the query. HRESULT=80040E14, SQLSrvr: SQLSTATE=42000, state=1, Severity=11, native=8621, line=1". Эта ошибка указывает на то, что оптимизатор запросов SQL Server не смог обработать слишком сложный запрос из-за нехватки ресурсов стека. Как правило, такие ситуации возникают в больших базах данных, где типовые правила обмена могут генерировать чрезмерно объемные и сложные SQL-запросы.
Давайте подробно разберем, почему возникает эта ошибка, и какие шаги мы можем предпринять для её устранения и предотвращения в будущем. Мы рассмотрим как диагностические методы, так и конкретные подходы к оптимизации запросов 1С и настройке SQL Server.
Ошибка "The query processor ran out of stack space during query optimization" сигнализирует о том, что SQL Server, пытаясь найти наиболее эффективный способ выполнения запроса, столкнулся с ограничением по объему стека. Процесс оптимизации запроса является рекурсивным: оптимизатор перебирает множество возможных планов выполнения, и каждый такой перебор "потребляет" часть стека. Если запрос содержит большое количество соединений, подзапросов, сложных условий или обращений к полям составных типов, количество комбинаций может стать астрономическим, что приводит к переполнению стека оптимизатора. В контексте 1С это часто связано с автоматически генерируемыми запросами, особенно при использовании стандартных правил обмена, которые могут быть недостаточно оптимальными для больших объемов данных или специфической структуры метаданных.
В нашем случае, проблема была зафиксирована при обмене между Бухгалтерией 3.0.147.18 и Розницей Аптека 2.3.17.18 на платформе 8.3.23.1865. Опыт показал, что проблема появилась после обновления Бухгалтерии в диапазоне релизов с 3.0.143.42 по 3.0.146.24, что является важной зацепкой. Это указывает на то, что изменения в типовых правилах обмена или в структуре метаданных самой конфигурации 1С могли привести к генерации более сложных SQL-запросов, которые и перегружают оптимизатор SQL Server.
Первым и самым важным шагом является точное определение запроса, который вызывает ошибку. Без этого любые попытки "починить" систему будут носить случайный характер. Мы используем следующие методы:
Технологический журнал 1С (ТЖ)
Технологический журнал 1С — это мощный инструмент для мониторинга и отладки работы системы. Мы можем настроить его для сбора информации о запросах, выполняемых к СУБД, и их планах выполнения. Это позволит нам увидеть конкретный "тяжелый" SQL-запрос, который вызывает проблему.
Для нашей задачи нас интересует тег <plansql/> в файле настроек ТЖ. Добавим его в файл logcfg.xml, чтобы 1С начала логировать планы выполнения SQL-запросов:
<?xml version="1.0" encoding="UTF-8"?>
<log>
<event>
<eq property="name" value="DBMSSQL"/>
<action type="File">
<p:fileLocation value="C:\1C_Logs\"/>
<p:fileName value="dbmssql"/>
<p:writeMode value="0"/>
<p:level value="4"/>
<p:size value="102400"/>
<p:historySize value="50"/>
<p:format value="%date%, %thread%, %event%, %level%, %context%, %processname%, %processid%, %usr%, %application%, %connection%, %session%, %trans%, %exception%,%description%"/>
</action>
</event>
<event>
<eq property="name" value="SQL"/>
<action type="File">
<p:fileLocation value="C:\1C_Logs\"/>
<p:fileName value="sql"/>
<p:writeMode value="0"/>
<p:level value="4"/>
<p:size value="102400"/>
<p:historySize value="50"/>
<p:format value="%date%, %thread%, %event%, %level%, %context%, %processname%, %processid%, %usr%, %application%, %connection%, %session%, %trans%, %exception%,%description%"/>
</action>
</event>
<event>
<eq property="name" value="DBMSSQL"/>
<eq property="plansql" value="1"/>
<action type="File">
<p:fileLocation value="C:\1C_Logs\"/>
<p:fileName value="dbmssql_plans"/>
<p:writeMode value="0"/>
<p:level value="4"/>
<p:size value="102400"/>
<p:historySize value="50"/>
<p:format value="%date%, %thread%, %event%, %level%, %context%, %processname%, %processid%, %usr%, %application%, %connection%, %session%, %trans%, %exception%,%description%"/>
</action>
</event>
</log>
После включения ТЖ, воспроизведите проблему. В файлах логов мы сможем найти SQL-запрос, который предшествует ошибке. Часто это будет очень длинный и сложный запрос.
Анализ планов выполнения запросов SQL Server
Используя SQL Server Management Studio (SSMS), мы можем проанализировать план выполнения запроса, найденного в ТЖ. Для этого скопируем проблемный запрос и выполним его в SSMS с опцией "Display Actual Execution Plan" (или "Отобразить фактический план выполнения"). Это позволит нам увидеть, как SQL Server собирается выполнить запрос, какие индексы используются (или не используются) и где возникают "узкие места". Особенно важно обратить внимание на "Full Table Scan" (полное сканирование таблицы), что часто указывает на отсутствие или неэффективность индексации, а также на большое количество операций соединения (JOIN).
Мониторинг производительности SQL Server
Инструменты мониторинга, такие как Performance Monitor или Extended Events в SQL Server, позволяют нам отслеживать наиболее ресурсоемкие запросы в реальном времени. Мы можем настроить счетчики для отслеживания времени выполнения запросов (Duration), количества логических чтений (Reads), потребления CPU и памяти. Запросы с аномально высокими значениями этих показателей будут кандидатами на оптимизацию. Высокие значения Reads (тысячи и более) или длительное выполнение (дольше секунды при небольшом объеме данных) явно указывают на запрос, требующий внимания.
После выявления проблемного запроса, следующим шагом является его упрощение. Если мы имеем дело с типовыми правилами обмена, это может потребовать доработки этих правил или изменения конфигурации 1С. В любом случае, принципы оптимизации остаются общими:
Упрощение запросов с составными типами данных
Одной из наиболее частых причин генерации сложных SQL-запросов в 1С является обращение к полям составного типа через "точку" (например, Субконто1.Код или Документ.Контрагент.Партнер). Внутренне 1С разворачивает такие обращения в множество левых соединений (LEFT JOIN) со всеми возможными таблицами, которые входят в составной тип. Это может привести к сотням соединений в одном запросе, что катастрофически усложняет его для оптимизатора.
Решение: Если это возможно, следует избегать таких "двойных разыменований" в запросах. Вместо этого, предварительно выбирайте ссылки или явно указывайте тип данных. Например, если в запросе есть условие Субконто1.Код в (&Список), которое приводило к ошибке, можно преобразовать его следующим образом:
// Плохой вариант, приводящий к множеству LEFT JOIN:
ВЫБРАТЬ
РегистрБухгалтерии.Субконто1.Код
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК РегистрБухгалтерии
ГДЕ
РегистрБухгалтерии.Субконто1.Код В (&СписокКодовСубконто)
// Оптимизированный вариант с использованием подзапроса для явного указания ссылки:
ВЫБРАТЬ
РегистрБухгалтерии.Субконто1
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК РегистрБухгалтерии
ГДЕ
РегистрБухгалтерии.Субконто1 В
(ВЫБРАТЬ
Ссылка
ИЗ
Справочник.КакойТоСправочник,
Справочник.ДругойСправочник
ГДЕ
(Код В (&СписокКодовСубконто))
ИЛИ
(Код В (&СписокКодовДругогоСправочника)))
Здесь мы используем подзапрос для предварительной выборки ссылок на элементы справочников, а затем фильтруем по этим ссылкам. Это убирает из сгенерированного запроса все те множества левых соединений, которые там были при первоначальном варианте.
В других случаях, если поле имеет составной тип, но нас интересует конкретный тип, используйте функцию ВЫРАЗИТЬ(<Поле> КАК <Тип>) для приведения к нужному типу. Это позволяет оптимизатору СУБД работать только с нужной таблицей, не разворачивая все возможные варианты.
Избегание логической связки "ИЛИ" в условиях отбора
Применение оператора ИЛИ в условиях ГДЕ может значительно усложнить оптимизацию запроса, поскольку он часто мешает СУБД эффективно использовать индексы. Оптимизатор может быть вынужден выполнять полное сканирование нескольких таблиц или создавать сложный план выполнения, чтобы учесть все ветви условия.
Решение: По возможности, разбивайте запрос на два отдельных запроса и объединяйте результаты через оператор ОБЪЕДИНИТЬ ВСЕ (UNION ALL в SQL). Если условия относятся к одному и тому же полю, используйте конструкцию Поле В (Значение1, Значение2, ...), что для оптимизатора часто более эффективно.
Работа с виртуальными таблицами
Виртуальные таблицы (например, ОстаткиИОбороты, СрезПоследних) в 1С могут быть достаточно ресурсоемкими, особенно если они строятся на большом объеме данных и к ним применяются сложные соединения.
Решение: Если виртуальная таблица долго строится, а ее результат затем соединяется с другими таблицами, попробуйте помещать результат виртуальной таблицы во временную таблицу. Обязательно создавайте индексы на временной таблице по тем полям, по которым в дальнейшем будет происходить соединение или отбор. Кроме того, всегда старайтесь передавать все возможные отборы непосредственно в параметры виртуальной таблицы, чтобы она сразу строилась на меньшем объеме данных.
Избегание избыточных подзапросов и SELECT *
Использование SELECT * приводит к выборке всех колонок таблицы, даже если они не нужны, что увеличивает нагрузку на сервер, объем передаваемых данных и сложность запроса. Избыточные и вложенные подзапросы также могут затруднять оптимизацию.
Решение: Всегда выбирайте только те поля, которые действительно необходимы. Старайтесь разделять сложные соединения (JOIN) на несколько более мелких, используя временные таблицы для промежуточных результатов. Это упрощает каждый шаг для оптимизатора.
Настройки среды выполнения также играют ключевую роль. Мы рассмотрим как параметры SQL Server, так и действия с платформами 1С.
Обновление платформы 1С и конфигураций
Начнем с самого простого, но часто эффективного шага. Убедитесь, что ваша платформа 1С обновлена до последней стабильной версии. В данном случае, была попытка обновления до 8.3.24.1342, что является верным направлением. Также необходимо обновить конфигурации Бухгалтерия 3.0 и Розница Аптека 2.3 до самых актуальных релизов. Разработчики 1С постоянно работают над оптимизацией генерируемых запросов и правил обмена, и новые версии могут содержать исправления, направленные на подобные проблемы.
Дополнительно: Учитывая, что проблема появилась после обновления Бухгалтерии (релизы с 3.0.143.42 по 3.0.146.24), целесообразно сравнить правила обмена между старой, рабочей версией конфигурации и новой. Возможно, 1С внесла изменения в структуру метаданных или алгоритмы выгрузки/загрузки, которые привели к усложнению запросов. Это можно сделать, выгрузив правила обмена из старой и новой конфигураций и сравнив их с помощью специализированных инструментов.
Увеличение размера стека (ограниченные возможности)
Несмотря на то, что сообщение об ошибке прямо просит увеличить стек, в большинстве случаев для 64-битного MS SQL Server размер стека для потока оптимизатора фиксирован и составляет около 2Мб. Прямого и простого способа его увеличить обычно нет. Поэтому основной фокус должен быть на упрощении запросов, а не на попытке обойти ограничение стека.
Параметр Max degree of parallelism (MAXDOP)
Этот параметр в SQL Server определяет, сколько потоков может быть использовано для выполнения одного запроса. По умолчанию он равен 0, что позволяет SQL Server автоматически определять количество потоков. Для баз 1С часто рекомендуется устанавливать MAXDOP = 1. Это заставляет SQL Server выполнять каждый запрос в один поток, что может предотвратить проблемы с распараллеливанием, снизить нагрузку на процессор и избежать некоторых проблем с оптимизацией, связанных с координацией множества потоков.
Мы можем изменить этот параметр через SQL Server Management Studio (SSMS): Server Properties -> Advanced -> Max degree of parallelism или с помощью T-SQL команды:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE WITH OVERRIDE;
Ограничение памяти SQL Server и 1С
Несмотря на то, что у сервера Xeon E3-1240 с 32 Гб оперативной памяти достаточно ресурсов для 20-гигабайтной базы, неправильное распределение памяти может вызвать проблемы. Рекомендуется явно ограничить максимальный объем памяти, который может использовать SQL Server, а также для процессов 1С.
Для MS SQL Server: Установите максимальное значение памяти (Maximum server memory (MB)) в SSMS. Например, если у вас 32 Гб ОЗУ, выделите 10-16 Гб для SQL Server. Это оставит достаточно памяти для операционной системы и для работы процессов 1С (rphost, rmngr, agent).
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)', 10240; -- 10 ГБ
RECONFIGURE WITH OVERRIDE;
Для сервера 1С:Предприятие: В настройках кластера серверов 1С также можно ограничить максимальный объем памяти для рабочих процессов rphost. Это предотвратит "пожирание" всей памяти одним процессом 1С. Однако, как правило, процесс rphost сам не "ест" слишком много, пока ему не дадут. Основной потребитель — SQL Server.
Правильное распределение памяти предотвратит ситуации, когда один компонент занимает всю оперативную память, вызывая своппинг и общее замедление системы, что косвенно может влиять на скорость оптимизации запросов.
Индексы и статистика
Отсутствие или неэффективное использование индексов является одной из основных причин медленных запросов и, как следствие, их сложной оптимизации. Убедитесь, что для часто используемых полей в условиях WHERE, JOIN и ORDER BY созданы соответствующие индексы. Регулярно выполняйте операции дефрагментации/реиндексации индексов и обновления статистики на всех базах данных. Устаревшая статистика может ввести оптимизатор в заблуждение, заставляя его выбирать неоптимальные планы выполнения.
Внешние ключи (Foreign Keys)
В некоторых случаях, избыточное количество внешних ключей на таблице (более рекомендованного Microsoft значения в 253) может усложнять работу оптимизатора. Хотя это редкая проблема для типовых баз 1С, если вы сталкивались с доработками на уровне СУБД, стоит проверить и этот аспект.
Если после выполнения всех вышеперечисленных шагов проблема остается или если мы ищем временное решение, мы можем рассмотреть следующие подходы:
Перевод базы в файловый вариант
Как показал опыт, при переводе базы в файловый вариант (.dt) обмен данными может проходить, хоть и очень долго. Это указывает на то, что проблема действительно связана со взаимодействием 1С и MS SQL Server, а не с фундаментальными ошибками в данных или правилах обмена как таковых. Файловый вариант использует другой механизм хранения и выполнения запросов (собственный внутренний движок 1С), который не сталкивается с ограничениями стека SQL Server. Это может быть временным решением для срочного обмена, но для постоянной работы с большой базой 20Гб это неэффективно. При этом базу следует размещать на SSD-накопителе для максимальной производительности.
Мы можем попробовать следующий цикл: сделать тестирование и исправление файловой базы стандартными средствами 1С (Администрирование -> Обслуживание -> Тестирование и исправление), а затем повторно загрузить её в MS SQL Server. Иногда это помогает исправить логические ошибки, которые могут усложнять запросы.
Выгрузка и загрузка базы данных через .dt
Иногда простая выгрузка базы данных в файл .dt и последующая загрузка в новую, чистую базу на MS SQL Server может решить проблему. Это может "пересобрать" структуру базы данных на физическом уровне, убрав возможные фрагментации или другие скрытые проблемы, которые могли влиять на оптимизатор. Однако, как было отмечено, даже после этого повторная синхронизация может опять привести к ошибке, что подтверждает системный характер проблемы с запросами, а не одноразовый сбой.
Поэтапная миграция или выборочный обмен
Если полный объем данных, участвующих в обмене, слишком велик и вызывает проблему, мы можем рассмотреть возможность поэтапной миграции или настройки выборочного обмена. Это означает, что мы будем обмениваться данными не полностью, а частями, с использованием фильтров по периодам, организациям или типам документов. Это уменьшит объем данных в каждом запросе и снизит нагрузку на оптимизатор.
Перенос проблемной базы в PostgreSQL
Если все меры по оптимизации запросов и настройке MS SQL Server не помогают, как крайний вариант, можно рассмотреть перенос проблемной базы данных на другую СУБД, например, PostgreSQL. Оптимизаторы разных СУБД работают по-разному, и то, что является "тяжелым" для MS SQL Server, может быть обработано более эффективно в PostgreSQL. Однако, это серьезное инфраструктурное решение, требующее тщательного планирования и тестирования.
Проверка оборудования
Хотя сервер Xeon E3-1240 с 32 Гб оперативной памяти является адекватным для большинства средних баз 1С, в условиях очень высокой нагрузки или специфических узких мест (например, медленная дисковая подсистема, если база не на SSD) это может усугублять проблему. Убедитесь, что дисковая подсистема, на которой расположен SQL Server, имеет достаточную производительность (лучше использовать SSD с высокой скоростью IOPS). Также проверьте утилизацию CPU и I/O во время обмена данными.
В заключение, проблема "The query processor ran out of stack space during query optimization" при обмене данными в 1С чаще всего является следствием генерации слишком сложных SQL-запросов самой платформой 1С. Ключевые шаги к решению включают детальную диагностику с помощью технологического журнала, целенаправленную оптимизацию этих запросов, точную настройку MS SQL Server и обновление всех компонентов системы. Не бойтесь экспериментировать и тестировать различные подходы, последовательно устраняя потенциальные причины.