Каждый разработчик 1С при оптимизации сложных, «многоэтажных» запросов сталкивается с проблемой влияния кэширования. Вы запускаете тяжелый запрос в консоли — он выполняется 15 секунд. Вы запускаете его повторно — и он отрабатывает мгновенно, за 0,2 секунды. СУБД сохранила данные в оперативной памяти, и повторное чтение произошло из кэша (Buffer Pool), а не с диска. Но как в таких условиях объективно сравнить два варианта запроса и понять, какой из них действительно эффективнее в реальных многопользовательских условиях?
В этой статье мы подробно разберем, почему популярные методы тотальной очистки кэша могут навредить серверу, как безопасно управлять памятью СУБД на уровне конкретной базы данных и почему лучшая метрика для сравнения запросов — это не время выполнения, а количество логических чтений.
Часто в статьях для разработчиков рекомендуют использовать связку команд для полной очистки кэша данных перед каждым тестовым запуском запроса:
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
Давайте проанализируем, как работает этот механизм и почему применять его на работающем сервере (продакшене) категорически запрещено:
CHECKPOINT принудительно записывает все измененные («грязные») страницы данных из оперативной памяти на жесткий диск. Это безопасная операция, которая подготавливает буферный пул к очистке.DBCC DROPCLEANBUFFERS полностью удаляет все «чистые» страницы данных из оперативной памяти (Buffer Pool) всего SQL-сервера.Важно понимать: эта операция выполняется глобально для всего экземпляра MS SQL Server. Если на вашем сервере крутится несколько баз данных 1С (например, рабочая, бухгалтерия, зуп и парочка тестовых), выполнение этой команды мгновенно сотрет из памяти данные всех этих баз. В ту же секунду все пользователи ощутят резкое падение производительности: серверу придется заново перечитывать гигабайты данных с физических дисков, что создаст колоссальную нагрузку на дисковую подсистему (IOPS) и процессор.
Аналогично работает и команда очистки планов запросов:
DBCC FREEPROCCACHE;
GO
Она полностью очищает процедурный кэш (Plan Cache) сервера. После этого СУБД будет вынуждена заново компилировать планы для абсолютно всех входящих запросов, что вызовет резкий скачок нагрузки на процессоры (CPU Spike).
Если вам все же крайне необходимо протестировать запрос на полностью «холодном» кэше, но вы не хотите мешать коллегам и другим базам данных на сервере, существуют локальные способы очистки.
Способ 1. Сброс кэша данных для конкретной базы (Buffer Pool)
В MS SQL Server нет прямой команды для точечной очистки буферного пула только одной базы. Однако мы можем использовать безопасный обходной путь — кратковременный перевод целевой базы в автономный режим (Offline) с последующим возвратом в рабочий режим (Online):
ALTER DATABASE [Имя_Вашей_БД] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [Имя_Вашей_БД] SET ONLINE;
GO
При переходе в Offline СУБД сбрасывает все грязные страницы этой базы на диск и полностью вычищает ее данные из оперативной памяти. Кэш других баз данных на сервере при этом остается нетронутым. Параметр WITH ROLLBACK IMMEDIATE мгновенно прерывает все активные транзакции пользователей в этой базе, поэтому выполняйте это только тогда, когда в вашей тестовой базе никто не работает.
Способ 2. Локальная очистка кэша планов запросов (начиная с SQL Server 2016)
Если вам нужно сбросить только планы запросов для конкретной базы, не затрагивая кэш данных и планы других баз, используйте современную замену устаревшей процедуре DBCC FLUSHPROCINDB:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
Эта команда безопасно удаляет планы выполнения запросов только для текущей базы данных, заставляя оптимизатор перестроить их при следующем обращении.
Часто разработчики пытаются поймать так называемый «теплый» или «полугорячий» кэш — состояние, когда часть данных уже в памяти, а часть считывается с диска. Однако в профессиональной среде администраторов баз данных попытка воссоздать такое состояние считается антипаттерном. Это абсолютно невоспроизводимая среда: скорость физического чтения с диска постоянно меняется в зависимости от текущей активности операционной системы, работы виртуализации, фрагментации диска и сотен других факторов.
Как же тогда объективно оценивать качество оптимизации? Ответ прост: **забудьте про время выполнения запроса на этапе разработки**. Время выполнения — это вторичная, нестабильная метрика.
Настоящей золотой метрикой эффективности запроса являются логические чтения (Logical Reads).
Если ваш первый вариант запроса делает 500 000 логических чтений, а второй (оптимизированный) — всего 10 000, то второй запрос гарантированно эффективнее. Да, на «горячем» кэше в монопольном режиме они оба могут выполниться за 0,1 секунды. Но в реальной многопользовательской базе первый запрос будет нещадно перегружать процессор и вытеснять полезные данные других пользователей из оперативной памяти, тогда как второй отработает легко и незаметно.
Давайте разберем по шагам, как получить детальную и объективную статистику работы вашего запроса. Перед запуском тестируемого запроса в SQL Server Management Studio (SSMS) всегда включайте вывод статистики ввода-вывода и времени выполнения:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- Сюда вставляем исследуемый запрос 1С, полученный из технологического журнала или консоли
SELECT * FROM _InfoReg12345 WHERE _Fld54321 = 'Значение';
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
После выполнения запроса перейдите на вкладку «Сообщения» (Messages) в нижней панели SSMS. Вы увидите детальный отчет, который выглядит примерно так:
Таблица "_InfoReg12345". Число просмотров 1, логических чтений 1450, физических чтений 12, упреждающих чтений 1438...
Проанализируем эти параметры:
Для того чтобы провести честное сравнение двух вариантов запроса, воспользуемся проверенным подходом:
logical reads и показатель CPU time для обоих вариантов. Тот вариант запроса, у которого эти показатели стабильно ниже, и является победителем. Он будет работать быстрее и стабильнее на реальной работающей базе данных под нагрузкой.Если вы хотите проверить влияние параметров и исключить использование старого сохраненного плана запроса без очистки кэша всего сервера, используйте специальное указание (hint) в конце вашего SQL-запроса:
SELECT * FROM _InfoReg12345 WHERE _Fld54321 = 'Значение'
OPTION (RECOMPILE);
GO
Указание OPTION (RECOMPILE) заставляет оптимизатор MS SQL Server полностью проигнорировать старый план выполнения в кэше, проанализировать переданные параметры заново и построить новый оптимальный план именно для этого запуска, не сохраняя его в кэше для последующих вызовов.
Используя этот подход, мы получаем точные, воспроизводимые и объективные результаты оптимизации без риска нарушить стабильность работы пользователей на рабочем сервере.