Dbcc freeproccache для чего

Технохрень

Опросы

Рубрики

Еще по теме

Почему dbcc freeproccache помогает?

Бывает что MS SQL на нагруженных проектах загружает процессор под сотку. А выполнение dbcc freeproccache неожиданно решает эту проблему на пару часов. А потом снова CPU 100%. Разберемся?

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

Тогда все хорошо. Создается план выполнения, добавляется в кеш, потом в него подставляется переменная @var и все работает быстро и хорошо. Но как только дело доходит до запросов типа

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

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

И если такой фигни больше 50% от всего кеша, с этим надо что-то делать. Есть пара вариков решения данной проблемы.

Parameterization forced

То есть для своей базы надо выполнить запрос

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

И если включить PARAMETERIZATION FORCED то Sql server начнет сам автоматически эти самые параметры везде втыкать. безусловно это разгрузит кеш планов, но может получиться, что сам процесс автоматической параметризации будет занимать еще больше процессорного времени. В общем надо тестить. Кстати отключить этот режим можно командой

Optimize for ad hoc workloads по-русски

И к 2008 версии ребята из Microsoft осознали проблему — и сделали у Sql Server в настройках пункт Optimize for ad hoc workloads или Оптимизировать для нерегламентированных рабочих нагрузок если по-нашему.

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

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

По мотивам: sqlmag.com/sql-server/fine-tuning-plan-reuse

1 Comment

ShoGUN

Можно улучшить, при заполнении #work_to_do не надо делать лишнюю работу, лучше сделать доп. условие:
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 128

Источник: MS советует перестраивать только большие индексы(>128 страниц), т.к. маленькие индексы хранятся вместе, вперемешку с индексами других объектов(при условии, что такие объекты есть, конечно), и перестраивай их, не перестраивай — фрагментация не уменьшится. Можете сами поэкспериментировать.

Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are sometimes stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

Источник

DBCC FREEPROCCACHE (Transact-SQL)

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

DBCC FREEPROCCACHE does not clear the execution statistics for natively compiled stored procedures. The procedure cache does not contain information about natively compiled stored procedures. Any execution statistics collected from procedure executions will appear in the execution statistics DMVs: sys.dm_exec_procedure_stats (Transact-SQL) and sys.dm_exec_query_plan (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

Syntax for SQL Server:

Syntax for Azure Synapse Analytics and Analytics Platform System (PDW):

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

( < plan_handle | sql_handle | pool_name > )
plan_handle uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. plan_handle is varbinary(64) and can be obtained from the following dynamic management objects:

sql_handle is the SQL handle of the batch to be cleared. sql_handle is varbinary(64) and can be obtained from the following dynamic management objects:

pool_name is the name of a Resource Governor resource pool. pool_name is sysname and can be obtained by querying the sys.dm_resource_governor_resource_pools dynamic management view.
To associate a Resource Governor workload group with a resource pool, query the sys.dm_resource_governor_workload_groups dynamic management view. For information about the workload group for a session, query the sys.dm_exec_sessions dynamic management view.

WITH NO_INFOMSGS
Suppresses all informational messages.

COMPUTE
Purge the query plan cache from each Compute node. This is the default value.

ALL
Purge the query plan cache from each Compute node and from the Control node.

Starting with SQL Server 2016 (13.x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

Remarks

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Clearing the procedure (plan) cache causes all plans to be evicted, and incoming query executions will compile a new plan, instead of reusing any previously cached plan.

This can cause a sudden, temporary decrease in query performance as the number of new compilations increases. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message:

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.

This message is logged every five minutes as long as the cache is flushed within that time interval.

The following reconfigure operations also clear the procedure cache:

Result Sets

When the WITH NO_INFOMSGS clause is not specified, DBCC FREEPROCCACHE returns: «DBCC execution completed. If DBCC printed error messages, contact your system administrator.»

Permissions

Applies to: SQL Server, Analytics Platform System (PDW)

Applies to: Azure Synapse Analytics

General Remarks for Azure Synapse Analytics and Analytics Platform System (PDW)

Multiple DBCC FREEPROCCACHE commands can be run concurrently. In Azure Synapse Analytics or Analytics Platform System (PDW), clearing the plan cache can cause a temporary decrease in query performance as incoming queries compile a new plan, instead of reusing any previously cached plan.

DBCC FREEPROCCACHE (COMPUTE) only causes SQL Server to recompile queries when they are run on the Compute nodes. It does not cause Azure Synapse Analytics or Analytics Platform System (PDW) to recompile the parallel query plan that is generated on the Control node. DBCC FREEPROCCACHE can be cancelled during execution.

Limitations and Restrictions for Azure Synapse Analytics and Analytics Platform System (PDW)

DBCC FREEPROCCACHE can not run within a transaction. DBCC FREEPROCCACHE is not supported in an EXPLAIN statement.

Metadata for Azure Synapse Analytics and Analytics Platform System (PDW)

A new row is added to the sys.pdw_exec_requests system view when DBCC FREEPROCCACHE is run.

Examples: SQL Server

A. Clearing a query plan from the plan cache

The following example clears a query plan from the plan cache by specifying the query plan handle. To ensure the example query is in the plan cache, the query is first executed. The sys.dm_exec_cached_plans and sys.dm_exec_sql_text dynamic management views are queried to return the plan handle for the query.

The plan handle value from the result set is then inserted into the DBCC FREEPROCACHE statement to remove only that plan from the plan cache.

Here is the result set.

B. Clearing all plans from the plan cache

The following example clears all elements from the plan cache. The WITH NO_INFOMSGS clause is specified to prevent the information message from being displayed.

C. Clearing all cache entries associated with a resource pool

The following example clears all cache entries associated with a specified resource pool. The sys.dm_resource_governor_resource_pools view is first queried to obtain the value for pool_name.

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

D. DBCC FREEPROCCACHE Basic Syntax Examples

The following example removes all existing query plan caches from the Compute nodes. Although the context is set to UserDbSales, the Compute node query plan caches for all databases will be removed. The WITH NO_INFOMSGS clause prevents informational messages from appearing in the results.

The following example has the same results as the previous example, except that informational messages will show in the results.

When informational messages are requested and the execution is successful, the query results will have one line per Compute node.

E. Granting Permission to run DBCC FREEPROCCACHE

The following example gives the login David permission to run DBCC FREEPROCCACHE.

Источник

DBCC (Transact-SQL)

Язык Transact-SQL предоставляет инструкции DBCC, которые выступают в качестве консольных команд базы данных для SQL Server.

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

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

Использование внутреннего моментального снимка базы данных в командах DBCC

Следующие команды DBCC выполняют операции с внутренним моментальным снимком базы данных, предназначенным только для чтения, который создает компонент Компонент Database Engine. Тем самым предотвращаются проблемы блокировки и параллелизма при выполнении этих команд. Дополнительные сведения см. в разделе Моментальные снимки базы данных (SQL Server).

При выполнении одной из этих команд DBCC компонент Компонент Database Engine создает моментальный снимок базы данных и приводит ее в согласованное состояние на уровне транзакций. Затем команда DBCC выполняет проверку этого моментального снимка. После завершения команды DBCC этот моментальный снимок удаляется.

Иногда внутренний моментальный снимок базы данных не требуется или его невозможно создать. В этом случае команда DBCC выполняется в отношении реальной базы данных. Если база данных находится в режиме в сети, команда DBCC использует блокировку таблиц для обеспечения согласованности проверяемых объектов. Это поведение то же самое, как если бы был указан параметр WITH TABLOCK.

Внутренний моментальный снимок базы данных не создается, если команда DBCC выполняется:

Команды DBCC используют блокировки таблиц, а не внутренние моментальные снимки базы данных, если выполняются:

Для запуска команды DBCC CHECKALLOC или эквивалентной части DBCC CHECKDB с параметром WITH TABLOCK требуется X-блокировка базы данных. Такую блокировку нельзя устанавливать для базы данных tempdb или master: это может привести к ошибкам во всех остальных базах данных.

Команда DBCC CHECKDB вызывает ошибку при выполнении в базе данных master, если невозможно создать внутренний моментальный снимок базы данных.

Формирование отчета о состоянии команд DBCC

В представлении каталога sys.dm_exec_requests содержатся сведения о ходе выполнения и текущем этапе выполнения команд DBCC CHECKDB, CHECKFILEGROUP и CHECKTABLE. В столбце percent_complete указывается процент выполнения команды, а в столбце command отображается текущий этап ее выполнения.

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

Информационные инструкции

Инструкции проверки

Инструкции обслуживания

Вспомогательные инструкции

Источник

Dbcc freeproccache для чего

Сегодня рассмотрим один из вариантов обслуживания баз 1С в СУБД MS SQL.

Содержание:

1. Немного теории по планам обслуживания
2. Постановка задачи по созданию планов обслуживания
3. Создание плана обслуживания (Полная копия)
4. Создание плана обслуживания (Разностная копия)
5. Создание плана обслуживания (Резервная копия журналов транзакций)
6. Мониторинг планов обслуживания

1. Немного теории по планам обслуживания

Может многие со мой не согласятся, но для меня главной целью использования Планов обслуживания в MS SQL является создание резервных копий. Местные ITишники либо еще не делают резервные копии, либо уже делают, после печальных последствий отсутствия резервных копий. Да, не спорю, Планы обслуживания также нужны для оптимизации БД и выгрузки журналов транзакций, в последнем случаи, если не выполнять выгрузку журналов транзакций, у вас может вырасти база данных и занять все пространство на диске, 1С встанет колом и пользователи не смогут работать с базой, а вам придется выполнять шринк (Shrink) базы, это наверно самое страшное для ITишники после поломки базы и отсутствии резервных копий. Но об шринке (Shrink) поговорим в другой раз.

MS SQL Server поддерживает три модели восстановления:
1) Simple (Простая) — хранится только необходимый для жизни остаток журнала транзакций.
2) Full (Полная) — хранится весь журнал транзакций с момента последнего резервного копирования журнала транзакций.
3) Bulk logged (С неполным протоколированием) — часть операций записываются в очень компактном формате. В остальном идентична Full.

Модель восстановления базы можно посмотреть, в свойствах базы данных, на вкладке Параметры. Там же ее можно поменять. На практике я использую Full (Полная).

MS SQL поддерживает три типа формирования резервных копий:
1) Full (Полная копия)
2) Differential (Дифференциальная копия, Разностная копия)
3) Log (Резервная копия журналов транзакций)
Не путайте понятия: полная модель восстановления и полная резервная копия — разные вещи.

Рассмотрим подробно три типа формирования резервных копий.
1) Полная резервная копия
Позволяет восстановить состояние базы данных на некоторый момент времени. Состоит из копии файлов данных и журнала транзакций на момент завершения формирования резервной копии.

2) Разностная резервная копия
Хранит данных, изменившиеся с момента последней Полной резервной копии. При восстановлении нужно сначала восстановить Полную резервную копию в режиме NORECOVERY, потом можно применить любую из последующих Разностных копий. За счет этого можно значительно снизить объём дискового пространства для хранения резервной копии. Обратите внимание: без предыдущей Полной резервной копии Разностная копия бесполезна. Каждая последующая Разностная копия будет хранить все данные, входящие в предыдущую Разностную резервную копию, сделанную после предыдущей Полной копии. Поэтому каждая следующая Разностная копия больше предыдущих, пока снова не сделать Полную копию. Соответственно для восстановления на какой-то момент времени достаточно последней Полной резервной копии и последней Разностной копии. Промежуточные копии для восстановления не нужны.

3) Резервная копия журналов транзакций
Содержит копию журналов транзакций за некоторый период. Обычно с момента прошлой Резервной копии журналов транзакций до момента формирования текущей Резервной копии журналов транзакций. За счет этого Резервные копии журналов транзакций позволяют (с учетом Полной и Разностной копий) восстановить базу данных на любой момент времени. Резервная копия журналов транзакций высвобождает место в файле журнала транзакций, что позволяет ITишники избавиться от шринка базы данных.

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

2. Постановка задачи по созданию планов обслуживания

В организации N работают по шестидневке с 8:00 до 17:00. Обед с 12:00 до 13:00.
Имеется в MS SQL база данных с именем Moodle.
Что нужно сделать:
1) Проверить модель восстановления базы данных, должна быть Полная.
2) Создать план обслуживания, который будет создавать Полную резервную копию базы данных каждое воскресение в 17:00. Очищать хранилище от устаревших резервных копий старше 15 дней.
3) Создать план обслуживания, который будет создавать Разностную копию базы данных каждый день в 21:00 кроме воскресения.
4) Создать план обслуживания, который будет создавать Резервную копию журналов транзакций два раза в день, в 12:00 и в 17:00, кроме воскресения.

3. Создание плана обслуживания (Полная копия)

Источник

DBCC FREEPROCCACHE (Transact-SQL)

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

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

Синтаксические обозначения в 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.

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

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

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

Комментарии

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

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

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.

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

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

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

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

Разрешения

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

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

Общие замечания касательно 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 для удаления из кэша планов именно этого плана.

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

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

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

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

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

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

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

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

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

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

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

Источник