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

Обновлено: 21.11.2024

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

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

Синтаксис

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

Синтаксис для Azure Synapse Analytics и Analytics Platform System (PDW):

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

Аргументы

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

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

pool_name — это имя пула ресурсов регулятора ресурсов. pool_name — это sysname, и его можно получить, запросив представление динамического управления sys.dm_resource_Governor_resource_pools.
Чтобы связать группу рабочей нагрузки регулятора ресурсов с пулом ресурсов, запросите представление динамического управления sys.dm_resource_Governor_workload_groups. Для получения информации о группе рабочей нагрузки для сеанса запросите представление динамического управления sys.dm_exec_sessions.

WITH NO_INFOMSGS
Подавляет все информационные сообщения.

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

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

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

Примечания

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

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

SQL Server обнаружил %d случаев сброса хранилища кэша для хранилища кэша '%s' (часть кэша плана) из-за операций 'DBCC FREEPROCCACHE' или 'DBCC FREESYSTEMCACHE'.

< /цитата>

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

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

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

Наборы результатов

Если предложение WITH NO_INFOMSGS не указано, DBCC FREEPROCCACHE возвращает: "Выполнение DBCC завершено. Если DBCC выводит сообщения об ошибках, обратитесь к системному администратору".

Разрешения

Применяется к: SQL Server, Analytics Platform System (PDW)

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

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

  • Требуется членство в фиксированной серверной роли DB_OWNER.

Общие замечания по Azure Synapse Analytics and Analytics Platform System (PDW)

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

DBCC FREEPROCCACHE (COMPUTE) заставляет SQL Server перекомпилировать запросы только тогда, когда они выполняются на вычислительных узлах. Это не приводит к тому, что Azure Synapse Analytics или Analytics Platform System (PDW) перекомпилируют план параллельного запроса, созданный на узле управления. DBCC FREEPROCCACHE можно отменить во время выполнения.

Ограничения и ограничения для Azure Synapse Analytics и Analytics Platform System (PDW)

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

Метаданные для Azure Synapse Analytics and Analytics Platform System (PDW)

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

Примеры: SQL Server

А. Удаление плана запроса из кеша планов

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

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

Вот набор результатов.

Б. Удаление всех планов из кеша планов

В следующем примере удаляются все элементы из кеша планов. Предложение WITH NO_INFOMSGS указано для предотвращения отображения информационного сообщения.

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

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

Примеры: Azure Synapse Analytics и Analytics Platform System (PDW)

Д. Основные примеры синтаксиса DBCC FREEPROCCACHE

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

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

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

Е. Предоставление разрешения на запуск DBCC FREEPROCCACHE

В следующем примере логину Дэвиду дается разрешение на запуск DBCC FREEPROCCACHE.

Используйте DBCC FREEPROCCACHE, чтобы очистить кэш процедур. Освобождение кэша процедур приведет, например, к повторной компиляции специальной инструкции SQL, а не к повторному использованию из кэша. При наблюдении с помощью SQL Profiler можно наблюдать, как происходят события Cache Remove, когда DBCC FREEPROCCACHE начинает работать. Команда DBCC FREEPROCCACHE сделает недействительными все планы хранимых процедур, кэшированные оптимизатором в памяти, и заставит SQL Server скомпилировать новые планы при следующем запуске этих процедур. Давайте узнаем, как очистить кеш.

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

Надеюсь, эта запись в блоге об очистке кэша и буфера будет вам полезна.

Если у вас есть какие-либо вопросы, вы можете связаться со мной через Twitter.

Вот несколько моих последних видео, которые могут быть вам интересны.

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

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

Копировать (CTRL + C) и вставлять (CTRL + V) — друзья администраторов баз данных и разработчиков. В SQL Server вполне возможно использовать циклическое кольцо буфера обмена.

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

Существует два основных инструмента: 1) план выполнения и 2) статистика ввода-вывода. В этом сообщении блога мы увидим, как можно получить полноэкранный план выполнения.

Каждый раз, когда запрос запускается в SQL Server впервые, он компилируется, и для него создается план запроса. Для каждого запроса требуется план запроса перед его фактическим выполнением. Этот план запроса хранится в кэше планов запросов SQL Server. Таким образом, когда этот запрос выполняется снова, SQL Server не нужно создавать другой план запроса; вместо этого он использует кешированный план запроса, который повышает производительность базы данных.

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

В этой статье мы рассмотрим:

  • Как просмотреть кэш планов запросов
  • Как очистить кеш плана
  • Как использовать параметризованные запросы для повторного использования планов запросов.

Как просмотреть кеш планов запросов SQL Server

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

  • sys.dm_exec_cached_plans
  • sys.dm_exec_sql_text
  • sys.dm_exec_query_plan

Первый dm_exec_cached_plans — это представление динамического управления, а два оставшихся — функции динамического управления.

Давайте воспользуемся этими функциями и представлениями, чтобы увидеть, что находится в кэшированном плане запроса SQL Server. Выполните следующий запрос в SSMS (SQL Server Management Studio):

Инструкция SELECT используется для выбора счетчиков использования, типа объекта, текста запроса и XML-представления плана запроса для всех запросов, которые в настоящее время находятся в кэше плана запроса. Обратите внимание, что оператор CROSS APPLY использовался для объединения выходных данных динамических представлений и функций управления. Наконец, набор результатов сортируется в порядке убывания количества использований. Вывод на моем компьютере выглядит следующим образом (на вашем компьютере он может отличаться в зависимости от запросов, которые находятся в вашем плане запроса):

Здесь столбец usecount содержит количество выполнений запроса. Столбец objtype содержит информацию об объекте, через который выполняется запрос. Важно отметить, что до SQL Server 6.5 в кэшированном плане хранились только запросы к хранимым процедурам. Начиная с SQL Server 7.0, динамические и специальные запросы также хранятся в кэшированном плане. Текстовый столбец содержит текст запроса и, наконец, столбец query_plan содержит XML-представление запроса. Щелкните любую строку в столбце query_plan, чтобы просмотреть подробное XML-представление плана.

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

Проблема

Иногда возникают проблемы из-за того, что SQL Server хранит в своем кэше. Вот несколько возможных причин, которые могут вызвать проблемы с производительностью кэширования.

  • Проблемы с рабочей нагрузкой Ad-hoc Query из-за раздувания кеша.
  • Чрезмерное использование динамического кода T-SQL
  • На сервере недостаточно памяти или неправильно назначены экземпляры SQL
  • Нехватка памяти из-за длительных транзакций.
  • На сервере часто происходят события перекомпиляции

При обнаружении подобных проблем может потребоваться очистить кеш плана или кеш буфера. Итак, в этом совете мы рассмотрим различные способы очистки кеша SQL Server.

Решение

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

Бесплатная кэш-память DBCC

Эта команда позволяет очистить кэш планов, определенный план или пул ресурсов SQL Server.

Синтаксис

  • дескриптор плана однозначно идентифицирует план запроса для выполненного пакета, план которого находится в кэше планов.
  • sql_handle — это дескриптор SQL очищаемого пакета. sql_handle — это varbinary(64).
  • pool_name — это имя пула ресурсов регулятора ресурсов.

Примеры

Очистить весь кэш планов для экземпляра SQL Server.

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

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

Затем с помощью приведенного выше вывода мы можем указать конкретный пул ресурсов для сброса следующим образом.

Мы также можем сбросить один план запроса. Для этого нам нужно сначала получить plan_handle из кеша планов следующим образом:

Затем мы можем использовать plan_handle следующим образом, чтобы сбросить этот план запроса.

DBCC FLUSHPROCINDB

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

Синтаксис

Пример

Очистить кеш плана базы данных для базы данных MyDB.

DBCC FREESYSTEMCACHE

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

Синтаксис

Примеры

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

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

Очистить все табличные переменные и временные таблицы из кеша.

Очистить для определенной базы данных пользователей.

Удалите кэш базы данных tempdb.

DBCC FREESESSIONCACHE

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

Синтаксис

Пример

DBCC FLUSHAUTHCACHE

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

Синтаксис

Использование sp_recompile

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

Синтаксис

Пример

Использование ALTER DATABASE

Вы также можете очистить кэш планов для текущей базы данных с помощью команды ALTER DATABASE, как показано ниже. Это новое в SQL Server 2016.

Синтаксис

DBCC DROPCLEANBUFFERS

Используйте DBCC DROPCLEANBUFFERS для тестирования запросов с холодным буферным кешем без выключения и перезапуска сервера.

В этой статье представлен обзор команды SQL Server DBCC FREEPROCCACHE и ее использование с различными примерами.

Чтобы узнать больше о командах DBCC в SQL Server, я бы порекомендовал прочитать эту подробную статью Концепция и основы команд DBCC в SQL Server

Введение в план выполнения и процедурный кеш

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

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

Возможно, мы захотим очистить весь кеш, чтобы решить проблемы с производительностью. Ниже приведены несколько примеров.

  • Из-за длительных запросов ваш сервер может столкнуться с нехваткой памяти
  • В случае большого количества повторных компиляций
  • Большое количество специальных запросов.
  • Динамический t-SQL

Давайте рассмотрим, как очистить буферный кеш без перезапуска служб SQL.

Обзор команды DBCC FREEPROCCACHE

Мы можем использовать команду DBCC FREEPROCCACHE для очистки процедурного кеша в SQL Server. Мы можем удалить один план выполнения или все планы из буферного кеша. SQL Server должен создавать новые планы выполнения после того, как пользователь повторно запустит запрос.

Давайте воспользуемся демонстрацией для создания хранимой процедуры и просмотра плана выполнения в кеше.

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