В практике администрирования систем на базе 1С:Предприятие и SQL Server 2016 часто встречается странный феномен: когда база данных начинает существенно «тормозить», администраторы меняют Compatibility Level (уровень совместимости) со значения 110 (SQL Server 2012) на 130 (SQL Server 2016) и тут же возвращают его обратно. После этой нехитрой манипуляции база данных «оживает», и пользователи перестают жаловаться на низкую скорость работы. Разберем подробно, почему эта «магия» работает, какие скрытые процессы происходят в СУБД и как настроить систему так, чтобы не прибегать к подобным методам.
Проанализируем ситуацию: когда вы меняете уровень совместимости базы данных, SQL Server расценивает это как критическое изменение конфигурации, которое может повлиять на логику построения запросов. В результате СУБД выполняет автоматический сброс Procedure Cache (кэша планов выполнения) для данной конкретной базы. Рассмотрим подробнее, почему это помогает.
Основная проблема 1С заключается в эффекте, называемом Parameter Sniffing (подглядывание параметров). Суть его в том, что при первом выполнении запроса SQL Server строит план его выполнения, ориентируясь на текущие значения параметров. Если первый пользователь запустил отчет по «пустой» папке или редкому товару, сервер создаст план, оптимизированный именно под эти данные. Однако для всех последующих пользователей с огромными выборками этот план окажется крайне неэффективным. Когда вы меняете уровень совместимости, вы принудительно очищаете этот «плохой» план, и при следующем обращении сервер строит его заново, что временно решает проблему.
Вместо того чтобы «передергивать» настройки всей базы, мы можем использовать более точечную команду для очистки кэша планов именно для текущей базы данных, не затрагивая настройки совместимости:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Выясним причину, почему многие базы 1С годами работают на уровне 110 (SQL Server 2012), даже если установлен сервер 2016 года. В SQL Server 2014 и 2016 был внедрен новый механизм оценки количества строк — Cardinality Estimator. Для сложных запросов 1С, которые содержат множество соединений и временных таблиц, новый оптимизатор иногда ошибается гораздо сильнее, чем старый.
Если на уровне 130 тормоза возникают чаще, чем на 110, мы можем оставить современный уровень совместимости 130, но принудительно заставить SQL Server использовать «старый» алгоритм оценки. Это делается следующей командой:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
Это позволит пользоваться преимуществами SQL Server 2016, сохраняя стабильность планов, характерную для старых версий.
Разберем мнение о том, что при смене уровня совместимости происходит пересчет статистики. На самом деле, полная статистика не пересчитывается автоматически, но планы запросов перестраиваются на основе имеющихся данных. Если статистика устарела, SQL Server будет строить неверные планы. В 1С таблицы меняются очень динамично, и стандартные механизмы SQL Server не всегда успевают за этими изменениями.
Посмотрим на пример команды, которая позволяет обновить статистику для конкретной таблицы вручную, если вы точно знаете «узкое место»:
UPDATE STATISTICS [Имя_Таблицы] WITH FULLSCAN;
Однако на больших базах лучше использовать автоматизированные планы обслуживания. Проанализируем настройки флагов трассировки, которые критически важны для корректного обновления статистики в связке с 1С:
T2371 делает этот порог динамическим: чем больше таблица, тем меньше процент изменений нужен для старта обновления. В SQL 2016 при уровне 130 он включен по умолчанию, но на уровне 110 его нужно включать вручную.Рассмотрим правильную последовательность действий для системного администратора, чтобы база 1С работала стабильно без ручного вмешательства. Необходимо настроить Maintenance Plan (план обслуживания) в SQL Server Management Studio. Типовой регламент должен включать:
CLEAR PROCEDURE_CACHE в ночной регламент.Посмотрим, как выглядит скрипт для интеллектуального обновления статистики, который можно включить в РегламентныеЗадания или запустить через SQL Agent:
-- Обновление статистики только для тех таблиц, где данные изменились
EXEC sp_updatestats;
Проанализируем ситуацию: что делать, если база начала тормозить прямо сейчас? В SQL Server 2016 появился мощный инструмент — Query Store. Он позволяет отслеживать историю выполнения запросов и видеть, какой план был «хорошим», а какой стал «плохим». Вместо того чтобы сбрасывать весь кэш и заставлять сервер пересчитывать тысячи планов, мы можем зайти в Query Store, найти проблемный запрос 1С и «закрепить» (Force Plan) для него заведомо успешный план выполнения.
Для включения Query Store выполните команду:
ALTER DATABASE [Имя_Вашей_Базы] SET QUERY_STORE = ON;
Резюмируем: «Передергивание» уровня совместимости — это лишь способ экстренно сбросить кэш планов. Для долгосрочного решения проблемы необходимо настроить регулярное обновление статистики, использовать правильные флаги трассировки (T2371), рассмотреть возможность включения LEGACY_CARDINALITY_ESTIMATION и использовать Query Store для мониторинга производительности.