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

Обновлено: 05.07.2024

первый раз, когда я выполняю запрос, может занять > 15 secs . Последующие выполнения возвращаются в < 1 sec .

как заставить SQL Server 2005 не использовать кэшированные результаты? Я пробовал бежать

но это, похоже, не влияет на скорость запроса (все еще < 1 sec ).

вот некоторые хорошие объяснения. зацени.

из связанной статьи:

Если все тестирование производительности проводится в SQL Server, лучшим подходом может быть выдача контрольной точки, а затем команда DBCC DROPCLEANBUFFERS. Хотя процесс контрольных точек является автоматическим внутренним системным процессом в SQL Server и происходит регулярно, важно, чтобы эту команду писать все "грязные" страницы в текущей базе данных на диск и очистки буферов. Затем можно выполнить команду DBCC DROPCLEANBUFFERS для удаления всех буферов из пула буферов.

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

тогда ваш запрос будет выглядеть следующим образом

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

кстати, то, что вызывает ускорение, - это не кэш запросов, а кэш данных.

восемь различных способов очистки кэша плана

1. Удалите все элементы из кэша плана для всего экземпляра

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

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

4. Очистите специальный и подготовленный кэш плана для одного пула ресурсов

5. Очистите весь кэш плана для одного пула ресурсов

6. Удаляет все элементы из кэша планов для одной базы данных (не работает в SQL Azure)

7. Очистить кэш плана для текущей базы данных

8. Удалить один план запроса из кэша

обратите внимание, что ни DBCC DROPCLEANBUFFERS; , ни DBCC FREEPROCCACHE; поддерживается в хранилище данных SQL Azure / SQL.

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

Я лично делаю это как способ тестирования производительности запросов без необходимости иметь дело с кэшированных планов.

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

DBCC FREEPROCCACHE не очищает статистику выполнения для хранимых процедур, скомпилированных в собственном коде. Кэш процедур не содержит сведения о хранимых процедурах, скомпилированных в собственном коде. Все статистические данные выполнения, полученные при выполнении процедур, появятся в динамических административных представлениях (DMV) статистики выполнения: sys.dm_exec_procedure_stats (Transact-SQL) и sys.dm_exec_query_plan (Transact-SQL).

Синтаксис

Синтаксис для SQL Server:

Синтаксис для Azure Synapse Analytics и :Система платформы аналитики (PDW)

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

( < plan_handle | sql_handle | pool_name > )
plan_handle уникально идентифицирует план запроса для запущенного пакета, план которого хранится в кэше планов. Аргумент plan_handle имеет тип varbinary(64), и его можно получить из следующих объектов DMO:

sql_handle представляет дескриптор SQL очищаемого пакета. Аргумент sql_handle имеет тип varbinary(64), и его можно получить из следующих объектов DMO:

pool_name представляет имя пула ресурсов Resource Governor. Аргумент pool_name имеет тип sysname и может быть получен с помощью запроса к динамическому административному представлению sys.dm_resource_governor_resource_pools.
Чтобы связать группу рабочей нагрузки Resource Governor с пулом ресурсов, запросите динамическое административное представление sys.dm_resource_governor_workload_groups. Чтобы получить сведения о группе рабочей нагрузки для сеанса, запросите динамическое административное представление sys.dm_exec_sessions.

COMPUTE
Очистка кэша планов запросов в каждом вычислительном узле. Это значение по умолчанию.

ALL
Очистка кэша планов запросов в каждом вычислительном узле и в управляющем узле.

Начиная с версии SQL Server 2016 (13.x);, для очистки кэша процедур (планов) для базы данных в области действия служит инструкция ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE .

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

SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

Следующие операции по перенастройке также очищают кэш процедур:

доступ к счетчику контейнеров проверки кэша
доступ к квоте кэша проверки
clr enabled
стоимостный порог для параллелизма
cross db ownership chaining
память для создания индекса
максимальная степень параллелизма
max server memory
max text repl size
максимальное количество рабочих потоков
min memory per query
min server memory
ограничение стоимости регулятора запросов
ожидание запроса
remote query timeout
user options

Результирующие наборы

Разрешения

Применимо к: SQL Server, Система платформы аналитики (PDW)

Требует разрешения ALTER SERVER STATE на сервере.

Применимо к: Azure Synapse Analytics

Необходимо членство в предопределенной роли сервера DB_OWNER.

Общие замечания касательно Azure Synapse Analytics и Система платформы аналитики (PDW)

Несколько команд DBCC FREEPROCCACHE могут выполняться одновременно. В Azure Synapse Analytics или Система платформы аналитики (PDW) очистка кэша планов может приводить к временному снижению производительности обработки запросов, так как для входящих запросов компилируется новый план, а не используется существующий план из кэша.

Команда DBCC FREEPROCCACHE (COMPUTE) приводит к перекомпиляции запросов сервером SQL Server только в том случае, если они выполняются в вычислительных узлах. Она не приводит к тому, что Azure Synapse Analytics или Система платформы аналитики (PDW) перекомпилируют план параллельных запросов, созданный в управляющем узле. Команду DBCC FREEPROCCACHE можно отменить во время выполнения.

Ограничения для Azure Synapse Analytics и Система платформы аналитики (PDW)

Команда DBCC FREEPROCCACHE не может выполняться в рамках транзакции. Команда DBCC FREEPROCCACHE не поддерживается в инструкции EXPLAIN.

Метаданные для Azure Synapse Analytics и Система платформы аналитики (PDW)

При выполнении команды DBCC FREEPROCCACHE в системное представление sys.pdw_exec_requests добавляется новая строка.

Примеры: SQL Server

A. Очистка плана запроса из кэша планов

В следующем примере план запроса очищается из кэша планов путем указания дескриптора плана запроса. Чтобы обеспечить наличие запроса-образца в кэше планов, сначала выполните следующий запрос. Динамические административные представления sys.dm_exec_cached_plans и sys.dm_exec_sql_text запрашиваются для возврата дескриптора плана соответствующего запроса.

Затем значение дескриптора плана из результирующего набора вставляется в инструкцию DBCC FREEPROCACHE для удаления из кэша планов именно этого плана.

Б. Очистка всех планов из кэша планов

В. Очистка всех записей кэша, связанных с пулом ресурсов

В следующем примере очищаются все записи кэша, связанные с указанным пулом ресурсов. Сначала запрашивается представление sys.dm_resource_governor_resource_pools для получения значения аргумента pool_name.

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Г. Примеры базового синтаксиса DBCC FREEPROCCACHE

Д. Предоставление разрешения на выполнение DBCC FREEPROCCACHE

В приведенном ниже примере имени для входа David предоставляется разрешение на выполнение DBCC FREEPROCCACHE.

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

Возможна ситуация, при которой MS SQL Server, ориентируясь на устаревшую статистическую информацию, построит неоптимальный план запроса. Этот план будет сохранен в процедурном КЭШе и использован при повторном вызове такого же запроса. Если Вы обновили статистику, но не очистили процедурный кэш, то SQL Server может выбрать старый (неоптимальный) план запроса из КЭШа вместо того, чтобы построить новый (более оптимальный) план.

Таким образом, рекомендуется всегда после обновления статистик очищать содержимое процедурного КЭШа.

Для очистки процедурного КЭШа MS SQL Server необходимо выполнить следующий SQL запрос:

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

Настройка очистки процедурного КЭШа (MS SQL 2005)

Поскольку процедурный КЭШ необходимо очищать при каждом обновлении статистики, данную операцию рекомендуется добавить в уже созданный субплан «Обновление статистик». Для этого следует открыть субплан и добавить в его схему задачу Execute T-SQL Statement Task. Затем следует соединить задачу Update Statistics Task стрелочкой с новой задачей.

В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»:

В следующей статье напишу о дефрагментации индексов.

Затвитить пост!

Использование 1С Предприятие в клиент-серверном режиме дает очень много плюсов, к примеру, скорость и надежность работы. Но так же не надо забывать, что СУБД - это отдельная информационная система, которая требует определенного внимания и обслуживания. Мы подготовили основные рекомендации по созданию плана обслуживания баз данных Microsoft SQL Server в которых хранятся информационные базы 1С.

Рекомендуемые регламентные операции СУБД для работы «1С: Предприятие 8» клиент-серверного варианта при использовании СУБД MS SQL Server:

Регулярное резервное копирование баз данных
Обновление статистики
Очистка процедурного КЭШа
Реорганизация индекса
Перестроение индекса

Создание плана обслуживания

Все перечисленные выше операции возможно автоматизировать при использовании Плана обслуживания в Microsoft SQL Server.

Для начала необходимо подключиться к серверу используя Microsoft SQL Server Management Studio (устанавливается отдельно от MS SQL Server); перейти во вкладку «Управление» - «Планы обслуживания» - ПКМ – «Создать план обслуживания…» и задать имя плана.

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

Теперь в наш план необходимо добавить непосредственно сами задачи по обслуживанию. Для этого используем «Панель элементов» (слева от Обозревателя объектов) и добавляем нашу первую задачу «Проверка целостности базы данных».

В задаче «Проверка целостности базы данных» выберем необходимые для обслуживания базы: ПКМ по объекту данной задачи – «Изменить», в открывшемся окне из списка «Базы данных» выбираем необходимые нам базы (все кроме системных или конкретные)

Последующие задание необходимо выбирать исходя из нагрузки и времени выполнения регламентного задания: «Перестроение индекса» или «Реорганизация индекса».

«Перестроение индекса» - включает полное перестроение индексов таблиц базы данных, однако в версии MS SQL Server Standard происходит отключение всех клиентов от базы на время выполнения операции.

«Реорганизация индекса» - исправление уже имеющихся индексов, не требует отключение клиентов от базы

«Реорганизацию индекса» имеет делать смысл каждый день. В то время как полное «Перестроение индекса» лишь раз в неделю.

Добавляем необходимую нам задачу через «Панель элементов» и выбираем необходимые для этой задачи базы.

Далее нам необходимо установить между ними связь: выбираем нашу первую задачу «Проверка целостности базы данных» и кликнув (ЛКМ) на зеленую стрелку вниз (под объектом) протянем её до объекта нашей следующей задачи.

Открыв «Редактор управления очередностью» (2х ЛКМ по появившейся линии связи) мы можем задавать значение выполнения:

Успешное выполнение ( зеленая линия ) – последующие задание будет выполняться только в случае успешного выполнения предыдущего.

Ошибка ( красная линия ) – последующие задание будет выполняться только в случае ошибки выполнения предыдущего задания.

Завершение ( темно-синяя линия ) – последующие задание будет выполняться после предыдущего независимо от результатов выполнения предыдущего.

В данном случае, после успешного выполнения задачи «Проверка целостности базы данных» начнётся выполнение задачи «Реорганизация индекса», если проверка выявила повреждение базы данных, то последующие задачи обслуживания выполнятся не будут.

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

Далее добавим задачу по «Очистке процедурного КЭШа», однако такая задача отсутствует в «Панели элементов», поэтому мы добавляем задачу «Выполнение инструкции T-SQL».

Изменим объект «Выполнение инструкции T-SQL» (2х ЛКМ) и в появившемся окне в поле «Инструкция T-SQL» пропишем:

После чего не забываем создать связь для вновь добавленной задачи.

Теперь перейдём непосредственно к созданию резервной копии базы, добавляем соответствующий элемент на «Панели элементов». Открываем параметры задачи (2х ЛКМ по объекту).

На вкладке «Общее» мы можем выбирать необходимый нам тип резервной копии (Полное, Разностное, Журнал транзакций), базы и компонент резервного копирования (базу данных целиком или отдельные её компоненты).

На вкладке «Целевой объект» есть возможность разбить резервную копию на несколько файлов, создавать файл резервной копии и каталоги для каждой базы отдельно, а также указывать путь хранения резервной копии.

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

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

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

Помимо самих бэкапов баз, место на жестком диске могут занимать журналы регламентных заданий MS SQL Server, их мы тоже будем периодически очищать, добавив задачу «Очистка журнала».

В итоге наш «План обслуживания» будет выглядеть следующим образом:

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

Просмотреть журнал выполнения «Плана обслуживания» на наличие ошибок возможно через «Просмотр журнала»: «Обозреватель объектов» - «Управление» - «Планы обслуживания» - ПКМ (по плану обслуживания) – «Просмотр журнала».

Отдельно стоит обратить внимание на запуск в системе службы «Агент SQL Server». Данная служба отвечает за выполнение «Планов обслуживания», соответственно, в свойствах службы необходимо проверить чтобы стоял «Тип запуска: Автоматический».

График обслуживания

Периодичность выполнения регламентных операций, рекомендуемая разработчиками «1С: Предприятие 8».

Читайте также: