DBCC FREEPROCCACHE (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Remove todos os elementos do cache do plano, remove um plano específico do cache do plano, por meio da especificação de um identificador de plano ou identificador SQL ou remove todas as entradas de cache com um pool de recursos especificado.

Observação

DBCC FREEPROCCACHE não desmarca as estatísticas de execução para procedimentos armazenados compilados de modo nativo. O cache de procedimentos não contém informações sobre procedimentos armazenados compilados de modo nativo. Todas as estatísticas de execução coletadas de execuções de procedimento serão exibidas nas DMVs de estatísticas de execução: sys.dm_exec_procedure_stats (Transact-SQL) e sys.dm_exec_query_plan (Transact-SQL).

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe do SQL Server e do Banco de Dados SQL do Azure:

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

Sintaxe para Azure Synapse Analytics e PDW (Analytics Platform System):

DBCC FREEPROCCACHE [ ( COMPUTE | ALL ) ]
     [ WITH NO_INFOMSGS ]
[;]

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

( { plan_handle | sql_handle | pool_name } )

plan_handle identifica exclusivamente um plano de consulta de um lote que foi executado e cujo plano reside no cache de planos. plan_handle é varbinary(64) e pode ser obtido dos seguintes objetos de gerenciamento dinâmico:

sql_handle é o identificador SQL do lote a ser apagado. sql_handle é varbinary(64) e pode ser obtido dos seguintes objetos de gerenciamento dinâmico:

pool_name é o nome de um pool de recursos do Resource Governor. pool_name é sysname e pode ser obtido por meio da consulta da exibição de gerenciamento dinâmico sys.dm_resource_governor_resource_pools.

Para associar um grupo de carga de trabalho do Resource Governor a um pool de recursos, consulte a exibição de gerenciamento dinâmico sys.dm_resource_governor_workload_groups. Para obter informações sobre o grupo de carga de trabalho durante uma sessão, consulte a exibição de gerenciamento dinâmico sys.dm_exec_sessions.

WITH NO_INFOMSGS

Suprime todas as mensagens informativas.

COMPUTE

Limpe o cache de planos de consulta de cada nó de Computação. Este é o valor padrão.

ALL

Limpe o cache de planos de consulta de cada nó de Computação e do nó de Controle.

Observação

Começando pelo SQL Server 2016 (13.x), o ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE pode ser usado para apagar o cache (plano) de procedimento do banco de dados atual.

Comentários

Use DBCC FREEPROCCACHE para limpar o cache de planos cuidadosamente. A limpeza do cache (plano) de procedimento faz com que todos os planos sejam removidos e as execuções de consulta de entrada compilarão um novo plano, em vez de reutilizar um plano anteriormente armazenado em cache.

Isso pode causar uma queda repentina e temporária no desempenho da consulta conforme o número de novas compilações aumenta. Para cada armazenamento em cache limpo no cache de planos, o log de erros do SQL Server conterá a seguinte mensagem informativa:

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.

Essa mensagem é registrada a cada cinco minutos, contanto que o cache seja liberado dentro desse intervalo de tempo.

As seguintes operações de reconfiguração também são limpas no cache de procedimento:

  • contagem de bucket do cache de verificação de acesso
  • cota de cache de verificação de acesso
  • clr enabled
  • limite de custo para paralelismo
  • cross db ownership chaining
  • memória para criar índice
  • grau máximo de paralelismo
  • max server memory
  • max text repl size
  • máximo de threads de trabalho
  • min memory per query
  • min server memory
  • limite de custo do administrador de consulta
  • espera da consulta
  • remote query timeout
  • opções de usuário

No Banco de Dados SQL do Azure, DBCC FREEPROCCACHE atua na instância do mecanismo de banco de dados que hospeda o banco de dados ou o pool elástico atual. A execução de DBCC FREEPROCCACHE em um banco de dados de usuário limpa o cache de planos desse banco de dados. Se o banco de dados estiver em um pool elástico, ele também limpará o cache de planos em todos os outros bancos de dados nesse pool elástico. Executar o comando no banco de dados master não tem nenhum efeito em outros bancos de dados no mesmo servidor lógico. Executar esse comando em um banco de dados usando o objetivo de serviço Básico, S0 ou S1 pode limpar o cache de planos em outros bancos de dados usando esses objetivos de serviço no mesmo servidor lógico.

Conjuntos de resultados

Quando a cláusula WITH NO_INFOMSGS não é especificada, DBCC FREEPROCCACHE retorna o seguinte:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissões

Aplica-se a: SQL Server, PDW (Analytics Platform System)

  • Exige a permissão ALTER SERVER STATE no servidor.

Aplica-se ao: Banco de Dados SQL do Azure

  • Exige a associação na função de servidor ##MS_ServerStateManager##.

Aplica-se a: Azure Synapse Analytics

  • Exige a associação na função de servidor fixa db_owner.

Comentários sobre o Azure Synapse Analytics e o Analytics Platform System (PDW)

Vários comandos DBCC FREEPROCCACHE podem ser executados simultaneamente.

No Azure Synapse Analytics ou PDW (Analytics Platform System), a limpeza do cache de planos pode causar uma queda temporária no desempenho da consulta conforme as consultas de entrada compilam um novo plano, em vez de reutilizar um plano previamente armazenado em cache.

DBCC FREEPROCCACHE (COMPUTE) apenas faz com que o SQL Server recompile consultas quando elas são executadas nos nós de computação. Ele não faz com que o Azure Synapse Analytics ou o Analytics Platform System (PDW) recompile o plano de consulta paralela gerado no nó de controle.

DBCC FREEPROCCACHE pode ser cancelado durante a execução.

Limitações e restrições do Azure Synapse Analytics e do Analytics Platform System (PDW)

DBCC FREEPROCCACHE não pode ser executado em uma transação.

DBCC FREEPROCCACHE não tem suporte em uma instrução EXPLAIN.

Metadados de Azure Synapse Analytics e PDW (Analytics Platform System)

Uma nova linha é adicionada à exibição do sistema sys.pdw_exec_requests quando DBCC FREEPROCCACHE é executado.

Exemplos: SQL Server

a. Apagar um plano de consulta do cache do plano

O exemplo a seguir apaga um plano de consulta do cache do plano especificando o identificador do plano de consulta. Para assegurar que a consulta de exemplo esteja no cache do plano, a consulta será executada primeiro. As exibições de gerenciamento dinâmico sys.dm_exec_cached_plans e sys.dm_exec_sql_text são consultadas para retornar o identificador de plano da consulta.

O valor do identificador do plano do conjunto de resultados é inserido na instrução DBCC FREEPROCACHE para remover apenas o plano em questão do cache do plano.

USE AdventureWorks2022;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

Este é o conjunto de resultados.

plan_handle                                         text
--------------------------------------------------  -----------------------------
0x060006001ECA270EC0215D05000000000000000000000000  SELECT * FROM Person.Address;
  
(1 row(s) affected)

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
GO

B. Limpar todos os planos do cache de planos

O exemplo a seguir limpa todos os elementos do cache do plano. A cláusula WITH NO_INFOMSGS é especificada para impedir a exibição da mensagem informativa.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

C. Limpar todas as entradas do cache associadas a um pool de recursos

O exemplo a seguir limpa todas as entradas do cache associadas a um pool de recursos especificado. A exibição sys.dm_resource_governor_resource_pools é consultada primeiro para obter o valor de pool_name.

SELECT * FROM sys.dm_resource_governor_resource_pools;
GO
DBCC FREEPROCCACHE ('default');
GO

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

D. Sintaxe básica de DBCC FREEPROCCACHE

O exemplo a seguir remove todos os caches de planos de consulta existentes dos nós de Computação. Embora o contexto seja definido como UserDbSales, os caches de planos de consulta do nó de computação de todos os bancos de dados serão removidos. A cláusula WITH NO_INFOMSGS impede a exibição de mensagens informativas nos resultados.

USE UserDbSales;
DBCC FREEPROCCACHE (COMPUTE) WITH NO_INFOMSGS;

O exemplo a seguir traz os mesmos resultados do exemplo anterior, exceto que as mensagens informativas serão exibidas nos resultados.

USE UserDbSales;
DBCC FREEPROCCACHE (COMPUTE);

Quando mensagens informativas forem solicitadas e a execução for bem-sucedida, os resultados da consulta terão uma linha por nó de Computação.

E. Conceder permissão para executar DBCC FREEPROCCACHE

O exemplo a seguir concede a permissão David de logon para a execução de DBCC FREEPROCCACHE.

GRANT ALTER SERVER STATE TO David;
GO

Confira também