От экспертов «1С-Рарус»: Очистка процедурного кэша SQL для 1C. Мифы, реальность, практика

Технологическое качество информационных систем: 1С:Предприятие, MS SQL, PostgreSQL

В настоящее время наблюдается существенный рост числа информационных систем, построенных с использованием технологической платформы «1С:Предприятие», систем управления базами данных MS SQL Server и PostgreSQL с большим числом одновременно работающих пользователей. Также постепенно появляются новые и развиваются существующие технологии построения крупных информационных систем. При эксплуатации таких крупных и архитектурно сложных систем на первый план выходят вопросы обеспечения технологического качества информационной системы.

Под технологическим качеством подразумеваются:

• Доступность системы. Т. е. время запланированных, а тем более незапланированных простоев должно быть минимально.
• Производительность — система должна работать быстро.
• Работоспособность системы и отсутствие ошибок. Т. е. система должна выполнять свои функции.
• Неухудшение технологических показателей работы системы в результате обновлений на новые версии. Технологические показатели — это счетчики производительности на уровнях «1С:Предприятия», СУБД и операционной системы, отображающие текущее состояние системы.

Технологическое качество складывается из качественных и количественных метрик. Качественные метрики — субъективное восприятие пользователей.

Под количественными понимается, например, SLA, загруженность оборудования и так далее.

Поддержание технологического качества — задача непростая. Для того, чтобы добиться технологического качества необходима слаженная работа команды, решающей задачи эксплуатации.

Для обеспечения технологического качества необходимо:

1. Администрировать информационную систему:
1. регламентное обслуживание СУБД и приложения;
2. регламентное обслуживание серверов.
2. Проводить мониторинг оборудования:
1. сервера СУБД и приложения;
2. операционная система;
3. сетевое оборудование.
3. Вести разработку и тюнинг текущей системы:
1. оптимизировать медленно работающие операции;
2. добавлять новый функционал и его тестировать.

Регламентные операции СУБД

Тестовый стенд

Для описанных в статье тестов используется:

• Microsoft SQL Server 2019 Developer Edition.
• Параллелизм выключен.
• Автообновление статистики выключено.
• Прослушивание параметров включено.

MS SQL Server

Для поддержания работоспособности любой информационной системы необходимо выполнять ряд регламентных операций на уровне СУБД для того, чтобы обеспечить безопасность, надежность, производительность системы.

Полный список возможных регламентных операций на примере MS SQL Server выглядит следующим образом.

Обновление статистики

Статистика — объект, на основе которого SQL Server строит наиболее оптимальные планы. Соответственно, если статистика неактуальная, то план запроса может быть неоптимальным и запрос может работать медленно и потреблять лишние ресурсы. Для обновления статистики существует масса методов — это скрипты, планы обслуживания, стороннее ПО, в общем, методов много и ими пестрит интернет.

Дефрагментация индексов

Индексы в базе данных существует для того чтобы ускорить доступ к данным. Индексы также нужно обслуживать.

Обслуживать можно 2 способами:

1. Реорганизация (reorganize). Упорядочивание на листовом уровне.
2. Перестроение (rebuild). Можно перестраивать полностью, то есть удалять старый и создавать новые индекс.

У каждого из методов существуют свои плюсы и минусы.

Реорганизация — это онлайн операция, которая дефрагментирует страницы на листовом уровне кластерного или некластерного индекса и использует немного дополнительного рабочего пространства для этого. При этом данный процесс может быть приостановлен и начат с места, в котором был остановлен.

Перестроение — это онлайн операция в версии Enterprise, оффлайн в других версиях и использует количество дополнительного рабочего пространства равное размеру индекса. При этом создается новая копия индекса и удаляется старая, таким образом фрагментация сводится к нулю. По умолчанию также пересчитывается статистика.

Резервное копирование

Резервные копии нужно делать. Всегда должны быть бэкапы, чтобы в случае каких-то сбоев мы могли спокойненько базу данных из резервной копии достать. Даже если есть репликация, кластеризация, группы высокой доступности — это не отменяет необходимость создания резервных копий. Реплики — это не замена резервной копии.

Проверка на целостность

Мы делаем резервные копии для того, чтобы их в какой-то момент развернуть. Но есть ещё одна очень важная деталь, которую, на нашем опыте, 90% компаний у себя не делают. Для понимания, что бэкапы могут быть восстановлены в случае сбоя — их нужно регулярно восстанавливать на тестовом сервере и проверять на целостность командой CheckDB.

Сжатие базы данных

Существует рекомендация, которую мы считаем неоправданной — на постоянной основе сжимать базу данных для того чтобы освобождать место внутри файлов базы данных и отдавать их обратно операционной системе.

Дело в том, что сжатие файла данных приводит к сильной фрагментации, что порождает необходимость перестроения индексов. А при перестроении индексов вновь образуется пустое место внутри файла данных. Это порочный круг и на регулярной основе делать этого точно не нужно, разве что в критических случаях, когда места действительно нет. Еще одна сторона медали — диски SSD. Для SSD фрагментация может быть не столь критичной

Сжатие логов транзакций

Существует еще более курьезная рекомендация — сжимать логи транзакций регулярно, чего делать совершенно точно не нужно, разве что опять же в каких-то критических случаях. Файл журнала транзакций растет, когда имеют место, например, длительные транзакции с изменением данных или не происходят регулярные бэкапы журнала или по ряду других причин. Размер, до которого вырос файл в условиях нормальной работы системы, скорее всего, является необходимым размером для нормального функционирования данной конкретной системы.

Журнал транзакций является оборачиваемым файлом, а запись в него осуществляется последовательно. Это означает, что при достижении физического конца файла новые записи журнала будут размещаться в начале физического файла журнала при условии, что журнал был усечен, т. е. неактивная часть журнала освобождена. Поэтому свободное место внутри журнала будет использовано повторно, если оно имеется.

В случае же регулярного сжатия журнала файл вынужден будет постоянно расти до того размера, который необходим для нормального функционирования. Оптимальный вариант — назначать файлам журнала размер, который был бы максимально близким к окончательному требуемому размеру, чтобы избежать постоянных операций увеличения файла

Очистка журналов заданий

Таблички с историческими данными истории выполнения джобов. Нужно очищать папку Log в директории установки SQL, в которой формируются файлы с результатами выполнения заданий. Со временем записей в этих табличках и файлов в директории становится много, они занимают лишнее место. Поэтому, если нет специальных требований хранить историю выполнения джобов какое-то длительное время, то ее лучше чистить.

Очистка процедурного кэша

Одна из часто встречающихся в интернете рекомендаций — регулярно очищать процедурный кэш после обновления статистики командой DBCC FREEPROCCACHE для того, чтобы избежать использования плохого плана запроса после обновления статистики. В реальности к данной рекомендации, как и ко всем остальным, следует прислушиваться далеко не всегда.

PostgreSQL

Если говорить о регламентных операциях над СУБД PostgreSQL, то тут будет немного другой список. Здесь на первое место выходит вакуумизация таблиц. Так как PostgreSQL — это версионная СУБД, есть необходимость вычищать старые версии строк, то есть версии строк, которые никогда не будут нужны никаким транзакциям.

Не забываем в PostgreSQL делать резервное копирование и, конечно же, восстанавливать резервные копии для того, чтобы убедиться в их пригодности.

Процесс выполнения запросов SQL Server

Обработка инструкции SELECT

При обработке задач SQL Server’ом используется следующая последовательность:

1. Новая задача (запрос) поступает от приложения по сети (либо локально, через shared memory) и становится в очередь задач.
2. Свободный worker, или рабочий поток, (если такой имеется) берет задачу из очереди.
3. Далее происходит:
1. Парсинг — SQL Server просматривает инструкцию и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы.
2. Компиляция — строится дерево запроса, иногда называемое деревом последовательности, с описанием логических шагов, необходимых для преобразования исходных данных в требуемый формат.
3. Оптимизация — оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. Затем он выбирает ряд шагов, которые возвращают результаты быстрее всего и используют меньше ресурсов. Дерево запроса обновляется для записи этого точного ряда шагов. Конечную, оптимизированную версию дерева запроса называют планом выполнения.
4. Выполнение плана запроса. SQL Server начинает реализовывать план выполнения.
5. Возврат данных клиенту. SQL Server преобразует данные в заданный для результирующего набора формат и возвращает результирующий набор клиенту.

Выполнение запроса PostgreSQL

В целом, процесс выполнения запроса в PostgreSQL похож. Ключевое отличие — в PostgreSQL нет глобального кэша планов:

1. Прикладная программа устанавливает подключение к серверу Postgres. Эта программа передаёт запрос на сервер и ждёт от него результатов.
2. На этапе разбора запроса сервер выполняет синтаксическую проверку запроса, переданного прикладной программой, и создаёт дерево запроса.
3. Система правил принимает дерево запроса, созданное на стадии разбора, и ищет в системных каталогах правила для применения к этому дереву. Обнаружив подходящие правила, она выполняет преобразования, заданные в теле правил.
   Одно из применений системы правил заключается в реализации представлений. Когда выполняется запрос к представлению (т. е. виртуальной таблице), система правил преобразует запрос пользователя в запрос, обращающийся не к представлению, а к базовым таблицам из определения представления.
4. Планировщик/оптимизатор принимает дерево запроса (возможно, переписанное) и создаёт план запроса, который будет передан исполнителю.
   Он выбирает план, сначала рассматривая все возможные варианты получения одного и того же результата. Например, если для обрабатываемого отношения создан индекс, прочитать отношение можно двумя способами. Во-первых, можно выполнить простое последовательное сканирование, а во-вторых, можно использовать индекс. Затем оценивается стоимость каждого варианта и выбирается самый дешёвый. Затем выбранный вариант разворачивается в полноценный план, который сможет использовать исполнитель. Важно отметить, что в PostgreSQL нет глобального кэша планов, но об этом чуть позже.
5. Исполнитель рекурсивно проходит по дереву плана и получает строки тем способом, который указан в плане. Он сканирует отношения, обращаясь к системе хранения, выполняет сортировку и соединения, вычисляет условия фильтра и, наконец, возвращает полученные строки.

План запроса

План выполне́ния запро́са — последовательность операций, необходимых для получения результата SQL-запроса. Процесс выбора одного плана выполнения из множества потенциально возможных планов называется оптимизацией.

Оптимизатор запросов является одним из самых важных компонентов SQL Server. Хотя для анализа запроса и выбора плана оптимизатору запросов требуются некоторые накладные расходы, эти накладные расходы обычно многократно окупаются, когда оптимизатор запроса выбирает эффективный план выполнения.

План запроса в MS SQL Server

План запроса в MS SQL Server может выглядеть следующим образом.

Оптимизатор запросов SQL Server основан на оценке стоимости. Оптимизатор запросов должен проанализировать возможные планы и выбрать один с самой низкой предполагаемой стоимостью. Для некоторых сложных инструкций SELECT есть тысячи возможных планов выполнения.

В этих случаях оптимизатор запросов не анализирует все возможные комбинации. Вместо этого он использует сложные алгоритмы поиска плана выполнения, имеющего стоимость, близкую к минимальной возможной стоимости.

Оптимизатор запросов SQL Server не выбирает план выполнения только на основе самой низкой стоимости ресурсов. Он выбирает такой план, который возвращает результаты пользователю при разумной стоимости ресурсов и делает это быстрее по сравнению с другими планами.

План запроса в PostgreSQL

План запроса в PostgreSQL, как правило, представлен в текстовом виде:

Limit   (cost=0.00..0.07 rows=1 width=398) (actual time=0.498..0.499 rows=1  loops=1)

  ->  Seq Scan on  _accumrg13936  (cost=0.00..1552.88  rows=23688 width=398) (actual time=0.497..0.497 rows=1 loops=1)

  Planning time: 20.496 ms

  Execution time: 0.587 ms  

(4 rows) 

Графическое представление:

Процедурный кэш

Операция компиляции или создания плана запроса является достаточно ресурсоемкой с точки зрения ресурсов процессора. Для того, чтобы при втором и последующих выполнениях одинаковых запросов или процедур не компилировать план повторно, план сохраняется в специальной области памяти, которую называют кэш планов или процедурный кэш.

Получить состояние процедурного кэша можно из динамических представлений при помощи, например, такого запроса:

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan

  FROM sys.dm_exec_cached_plans AS  cplan

  CROSS APPLY  sys.dm_exec_sql_text(plan_handle) AS qtext

 CROSS APPLY  sys.dm_exec_query_plan(plan_handle) AS qplan

 ORDER BY cplan.usecounts DESC;

Результат запроса может выглядеть так:

Как долго планы остаются в кэше

Планы остаются в кэше пока есть достаточно памяти для них и при условии, что не было определенных изменений в базе данных. А также, чем чаще планы используются, тем дольше они потенциально остаются в кэше. Если имеет место нехватка оперативной памяти, движок SQL Server начинает удалять планы из кэша, определяя на основе специальных механизмов какие именно планы удалять.

Кэширование планов — важный механизм производительности системы.

План может стать неактуальным в следующих случаях, но не ограничиваясь ими:

• Изменены таблица или представления, на которые ссылается запрос (ALTER TABLE или ALTER VIEW).
• Изменена одна процедура, которая удалит все планы для этой процедуры из кэша (ALTER PROCEDURE).
• Изменены индексы, используемые планом выполнения.
• Обновлена статистика, которая используется планом выполнения и сформирована либо явным образом по UPDATE STATISTICS, либо автоматически.
• Удалены индексы, используемые планом выполнения.
• Явный вызов sp_recompile.
• Частое изменение ключей (инструкциями INSERT или DELETE от пользователей, изменяющих таблицу, на которую ссылается запрос).
• Для таблиц с триггерами: значительный рост числа строк в таблицах inserted и deleted.
• Выполнение хранимой процедуры с помощью параметра WITH RECOMPILE.

Изменение следующих параметров конфигурации приведет к полной очистке процедурного кэша:

• access check cache bucket count;
• access check cache quota;
• clr enabled;
• cost threshold for parallelism;
• cross db ownership chaining;
• index create memory;
• max degree of parallelism;
• max server memory;
• max text repl size;
• max worker threads;
• min memory per query;
• min server memory;
• query governor cost limit;
• query wait;
• remote query timeout;
• user options.

Статистика

Статистика в MS SQL Server

Оптимизатор запросов SQL Server полагается на статистику при оценке затрат на ресурсы для различных методов извлечения сведений из таблицы или индекса. Статистика хранится для столбцов и индексов и содержит сведения о плотности данных. Она указывает селективность значений в определенном индексе или столбце.

Например, в таблице, представляющей автомобили, много автомобилей имеют одного производителя, но каждый автомобиль имеет уникальный идентификационный номер транспортного средства (VIN). Индекс по VIN является более селективным, чем индекс по производителям, так как VIN с меньшей плотностью, чем производитель. Если статистика индекса не является текущей, оптимизатор запросов, возможно, не сделает лучший выбор для текущего состояния таблицы.

Получить статистику для объекта можно из представления sys.stats.

Статистика в PostgreSQL

Статистическая информация в PostgreSQL похожа на MS SQL, получить информацию можно при помощи запроса:

SELECT attname, 

         inherited, 

         n_distinct, 

          array_to_string(most_common_vals, E '\n') AS most_common_vals

  FROM pg_stats

  WHERE tablename = '<имя_таблицы>';

На скриншоте видим часть определенной таблицы со статистической информацией PostgreSQL, где:

• attname — имя столбца;
• null_frac — доля записей, в которых этот столбец содержит NULL;
• avg_width — cредний размер элементов в столбце, в байтах;
• n_distinct — количество различных значений в столбце;
• most_common_vals — cписок самых частых значений в столбце.

Команда DBCC FREEPROCCACHE

Инструкция DBCC FREEPROCCACHE используется для очистки кэша планов. Очистка кэша процедур (планов) приводит к исключению всех планов. В результате, при выполнении входящих запросов будет компилироваться новый план, а не использоваться существующий план из кэша.

Это может стать причиной внезапного временного снижения производительности обработки запросов из-за увеличения числа компиляций.

При помощи данной команды можно удалять определенный план из кэша, передав plan_handle: DBCC FREEPROCCACHE (plan_handle).

Какие рекомендации по обслуживанию SQL Server для 1С можно часто встретить в интернете?

На просторах интернета можно часто встретить рекомендацию, которая выглядит примерно так: «очищайте процедурный кэш ежедневно после обновления статистики командой DBCC FREEPROCCACHE».

Как показывает опыт, рекомендаций на все случаи жизни нет и быть не может.

С чем связана данная рекомендация?

Часто в качестве аргументов приводится что-то вроде:

На первый взгляд, выглядит логично. Почему бы, от греха подальше, не очистить кэш и не начать все с чистого листа?

Но разобравшись в теме глубже, появляются несколько вопросов:

• Всегда ли верны аргументы, которые приводятся в рекомендациях?
• Зачем же SQL Server кэширует планы, если это может навредить?
• Почему система автоматом не удаляет старые планы из кэша при обновлении статистики?

Попытаемся на эти вопросы ответить.

Кейс: компиляция неоптимального плана после сброса процедурного кэша

В качестве основного аргумента рекомендаций по очистке процедурного кэша приводится утверждение, что в кэше может остаться старый неоптимальный план, в результате чего запрос будет выполнен по неоптимальному плану и его выполнение потребует более значительных ресурсов. Но всегда ли старый план неоптимален?

В реальности возможна и обратная ситуация — в кэше был оптимальный план, а после очистки кэша хранимая процедура (запрос) скомпилировалась с плохим планом.

Разберемся подробнее на примере.

Возьмем реальный запрос из реально работающей системы — конфигурация «Розница» со следующим контекстом:

'Форма.Вызов:Обработка.
РМКУправляемыйРежим.Форма.ФормаМеню.
Модуль.ПроверитьВозможность
РегистрацииПродажОбработка.
РМКУправляемыйРежим.Форма.
ФормаМеню.Форма:695:СтруктураСостояние
КассовойСмены=РозничныеПродажиСервер.
ПолучитьСостояниеКассовойСмены(
ПроверяемаяКасса.Значение);ОбщийМодуль.
РозничныеПродажиСервер.Модуль : 362 : Результат = Запрос.Выполнить();'

Запрос MS SQL:

EXEC sp_executesql 

      N'SELECT TOP 1 T1._Number, 

      T1._IDRRef, 

      T1._Fld6285RRef, 

      T1._Fld6272RRef, 

      T1._Fld6281RRef, 

      T1._Fld6289RRef, 

      T1._Fld6288RRef,

      CASE

      WHEN(T1._Fld6285RRef =    @P1)

      THEN 0x01

      ELSE 0x00

      END,

      CASE

      WHEN(T1._Fld6285RRef =    @P2)

      THEN T1._Fld6277

      ELSE T1._Fld6280

      END, 

      ISNULL(CAST(T2.Fld9252Balance_ AS NUMERIC(27, 2)), @P3), 

      T1._Date_Time

      FROM dbo._Document293 T1

      LEFT OUTER JOIN

      (

      SELECT T3._Fld9250RRef AS Fld9250RRef, 

      CAST(SUM(T3._Fld9252) AS NUMERIC(27, 2)) AS Fld9252Balance_

      FROM dbo._AccumRgT9253 T3

      WHERE((T3._Fld548 = @P4))

      AND (T3._Period = @P5

      AND ((T3._Fld9250RRef =    @P6)

      AND (T3._Fld9251RRef = 0x00000000000000000000000000000000))

      AND (T3._Fld9252    <> @P7)

      AND (T3._Fld9252    <> @P8))

      GROUP BY T3._Fld9250RRef

      HAVING(CAST(SUM(T3._Fld9252) AS NUMERIC(27, 2))) <> 0.0

      ) T2 ON(T1._Fld6272RRef =    T2.Fld9250RRef)

      WHERE((T1._Fld548 = @P9))

      AND (T1._Posted = 0x01

      AND (T1._Fld6272RRef =    @P10))

      ORDER BY(T1._Date_Time) DESC, 

      (T1._IDRRef) DESC', 

    N'@P1 varbinary(16),@P2 varbinary(16),@P3 numeric(10),@P4 numeric(10),@P5 datetime2(3),@P6 varbinary(16),@P7 numeric(10),@P8 numeric(10),@P9 numeric(10),@P10 varbinary(16)' 

Возьмем первый набор параметров, выполним запрос и посмотрим на статистику ввода-вывода, CPU и план запроса.

Параметры:

@P1=0xBC2DEF8D4F19698647B9333481AF33F2,

      @P2=0xBC2DEF8D4F19698647B9333481AF33F2,

      @P3=0, 

      @P4=0, 

      @P5='5999-11-01 00:00:00', 

      @P6=0x80F30050569B4EBB11EABD6B970139B4,

      @P7=0, 

      @P8=0, 

      @P9=0, 

    @P10=0x80F30050569B4EBB11EABD6B970139B4;

При этом статистика ввода-вывода и использования процессора следующая:

Таблица '_AccumRgT9253'. Scan count 0, logical reads 2, physical reads 0
Таблица '_Document293'. Scan count 1, logical reads 146, physical reads 0
 SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 22 ms.

Предположим, что мы следуем рекомендации и каждую ночь очищаем процедурный кэш.

При этом, первое выполнение запроса после сброса кэша будет с другими параметрами:

@P1=0xBC2DEF8D4F19698647B9333481AF33F2, 

      @P2=0xBC2DEF8D4F19698647B9333481AF33F2, 

      @P3=0, 

      @P4=0, 

      @P5='5999-11-01 00:00:00', 

      @P6=0x80F10050569B4EBB11EA70319DA7D140, 

      @P7=0, 

      @P8=0, 

      @P9=0, 

    @P10=0x80F10050569B4EBB11EA70319DA7D140;

План выполнения запроса:

При этом статистика ввода-вывода и использования процессора следующая:

Таблица '_AccumRgT9253'. Scan count 0, logical reads 2, physical reads 0
Таблица '_Document293'. Scan count 1, logical reads 2712, physical reads 0
SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 39 ms.

А затем, запрос вновь выполняется с первым набором параметров:

Видно, что это тот же план, который использовался для выполнения запроса со вторым набором параметров.

При этом статистика ввода-вывода и использования процессора следующая:

Таблица '_AccumRgT9253'. Scan count 0, logical reads 2, physical reads 0
Таблица '_Document293'. Scan count 1, logical reads 2712, physical reads 0 SQL Server Execution Times:
CPU time = 16 ms,  elapsed time = 47 ms.

В последнем случае было совершено в 18 раз больше логических чтений.

Таким образом, в результате очистки процедурного кэша мы получили неоптимальный план для последующих выполнений запроса с другими параметрами.

Зачем же SQL Server кэширует планы, если это может навредить?

Кэширование планов создано для производительности. Дело в том, что операция компиляции (построения) плана запроса — затратная операция с точки зрения процессорных ресурсов. И в нагруженной системе постоянные компиляции могут легко вызвать предельную загрузку процессора, вплоть до полной неработоспособности системы.

Сохраняя план в оперативной памяти SQL Server обеспечивает более быстрое выполнение запроса за счет того, что план не создается каждый раз заново, а просто берется из кэша при втором и последующих выполнениях запроса.
Отсюда напрашивается вывод, что очистка плана на регулярной основе, возможно, не самая лучшая идея.

Почему система автоматом не удаляет старые планы из кэша при обновлении статистики?

На самом деле SQL Server помечает планы запросов, как неактуальные, после обновления статистики (https://docs.microsoft.com/
en-us/sql/relational-
databases/query-processing-architecture-guide?view=sql-server-ver15). При следующем выполнении запроса план будет рекомпилирован.

Посмотрим на примере.

Практический пример: рекомпиляция планов после обновления статистики

Для отслеживания рекомпиляций будем использовать событие Extended Events sqlserver.sql_statement_recompile.

Возьмем все тот же запрос из конфигурации Розница. Выполним его. Затем обновим статистику на таблице _Document293 с опцией fullscan.

И выполним запрос еще раз. Судя по Extended Events рекомпиляций не было:

Но MSDN явно говорит о том, что инструкция UPDATE STATISTICS вызывает рекомиляцию плана при повторном выполнении запроса.

На самом деле, это не совсем так. В случае, если данные в таблице не были изменены, а статистика была обновлена, то рекомпиляции не будет. В этом есть логика, так как команда обновления статистики не изменила статистику, так как данные остались прежними.

Попробуем изменить данные в таблице:

и обновить статистику:

а затем выполнить запрос.Затем смотрим Extended Events.

В Extended Events появилась рекомпиляция по причине изменения статистики следующего вида:

Повторное использование планов в PostgreSQL

В PostgreSQL дела с кэшированием планов обстоят совсем иначе. В СУБД нет глобального кэша планов, как он есть в SQL Server. Соответственно, нет схожих рекомендаций по очистке кэша планов.

В PostgreSQL есть подготовленные операторы — объекты на стороне сервера, позволяющие оптимизировать производительность приложений. Подготовленные операторы создаются при помощи команды PREPARE.

Подготовленные операторы существуют только в рамках текущего сеанса работы с БД. Когда сеанс завершается, система забывает подготовленный оператор, так что его надо будет создать снова, чтобы использовать дальше. Это также означает, что один подготовленный оператор не может использоваться одновременно несколькими клиентами базы данных.

Подготовленные операторы потенциально дают наибольший выигрыш в производительности, когда в одном сеансе выполняется большое число однотипных операторов. Отличие в производительности особенно значительно, если операторы достаточно сложны для планирования или перезаписи, например, когда в запросе объединяется множество таблиц или необходимо применить несколько правил. Если оператор относительно прост в этом плане, но сложен для выполнения, выигрыш от использования подготовленных операторов будет менее заметным.

Подготовленный оператор может выполняться с использованием либо общего плана, либо специализированного.

Общий план не меняется при последующих выполнениях, тогда как специализированный план строится для определённого выполнения с учётом значений параметров, переданных при данном вызове. Использование общего плана снижает издержки планирования, но в ряде случаев специализированный план будет выполняться гораздо эффективнее, так как планировщик может подстроиться под значения параметров.

По умолчанию сервер автоматически выбирает, использовать ли для подготовленного оператора с параметрами общий или специализированный план. На данный момент это происходит по следующему принципу — первые пять выполнений производятся со специализированными планами и вычисляется средняя стоимость этих планов. Затем строится общий план и его примерная стоимость сравнивается со средней стоимостью специализированных.

При последующих выполнениях общий план будет использоваться, если его стоимость, по сравнению со стоимостью специализированных, не настолько велика, чтобы оправдать повторное планирование.

Эту логику можно переопределить, чтобы выбирались только общие или только специализированные планы, установив для параметра plan_cache_mode значение force_generic_plan или force_custom_plan, соответственно. Это полезно в первую очередь тогда, когда оценка стоимости общего плана по какой-то причине оказывается заниженной, и он выбирается даже когда фактически его использование обходится гораздо дороже, чем использование специализированных планов.

Ответ на вопрос: нужно ли чистить процедурный кэш MS SQL Server?

Всегда задавайте вопросы
Даже если известен ответ
Будьте сами себе несносны
Пусть ответ известен сто лет.

Собственно, статья ровно о том, что написано в четверостишье вверху.

К этому остается вспомнить еще строки Сергея Довлатова из его «Зоны»:

Подобным образом происходит и в жизни инженера, занимающегося вопросами экспертизы. Стоит посмотреть на проблематику немного под иным углом зрения и оказывается, что проблематика имеет совсем иной окрас и, конечно, совсем иное решение задачи, если задача после этого другого взгляда еще остается жива.

Давайте подрезюмируем, о чем мы рассуждали и к чему пришли.

Очистка процедурного кэша не несет смертельной опасности для сервера. Однако, в ряде случаев, операция может оказаться ненужной, избыточной и приводящей к деградации.

Нам удалось выяснить, что очистка процедурного кэша не всегда избавляет нас от неоптимальных планов, а, напротив, может послужить причиной появления таких планов в кэше.

После обновления статистики нетривиальные планы будут помечены как неактуальные и рекомпилированы при следующем выполнении запроса или процедуры в случае, если данные в таблице были изменены.

Полную очистку процедурного кэша может быть удобно проводить в качестве быстрой меры в случае, например, переполнения кэша планами, которые используются только один раз. Удалять один (или несколько) конкретный план из кэша может быть полезно, если нам известен конкретный запрос, который работает плохо в данный момент.

Полную очистку процедурного кэша в редких случаях удобно проводить в качестве реанимационной терапии.

Например, когда необходимо быстро решить проблему производительности и нет времени углубиться в поиски реальных причин деградации. Стоит при этом иметь в виду, что решение временное. Кроме того, на тестовом сервере в рамках исследований, для которых необходим пустой процедурный кэш. Удалять один или несколько конкретных планов из кэша может быть полезно, если нам известен конкретный запрос, который работает плохо в данный момент.

В общем случае, кэш планов на регулярной основе очищать нет необходимости, а проблемы, вследствие которых оптимизатор строит неоптимальные планы (неактуальная статистика, прослушивание параметров, сложные запросы и др.), должны быть адресованы конкретно.

В PostgreSQL глобальный кэш планов отсутствует, поэтому тут ничего чистить не нужно. Однако, стоит иметь в виду подготовленных операторов и помнить, что их поведением можно управлять.