• Artigo

Reutilização e armazenamento em cache do plano de execução

O SQL Server tem um pool de memória usado para armazenar planos de execução e buffers de dados. A porcentagem do pool alocada a planos de execução ou buffers de dados flutua dinamicamente, dependendo do estado do sistema. A parte do pool de memória usada para armazenar os planos de execução é conhecida como cache de procedimento.

Os planos de execução do SQL Server têm os componentes principais a seguir:

  • Plano de consulta

    A maior parte do plano de execução é uma estrutura de dados somente leitura reentrante usada por qualquer número de usuários. Isso é conhecido como plano de consulta. Nenhum contexto de usuário é armazenado no plano de consulta. Nunca há mais de uma ou duas cópias do plano de consulta na memória: uma cópia para todas as execuções em série e outra para todas as execuções paralelas. A cópia paralela cobre todas as execuções paralelas, independentemente do grau de paralelismo.

  • Contexto de execução

    Cada usuário que está executando a consulta atualmente tem uma estrutura de dados que retém os dados específicos para a sua execução, como valores de parâmetro. Esta estrutura de dados é conhecida como contexto de execução. As estruturas de dados de contexto de execução são reutilizadas. Se um usuário executar uma consulta e uma das estruturas não estiver sendo usada, ela será reinicializada com o contexto do usuário novo.

Contexto de execução, mesma consulta, literais diferentes

Quando alguma instrução SQL for executada no SQL Server, o mecanismo relacional examinará primeiro o cache de procedimento para verificar se há um plano de execução para a mesma instrução SQL. O SQL Server reutiliza qualquer plano existente que encontrar, diminuindo as despesas de recompilação da instrução SQL. Se não houver nenhum plano de execução, o SQL Server gerará um plano de execução novo para a consulta.

O SQL Server tem um algoritmo eficiente para localizar qualquer plano de execução existente de qualquer instrução SQL específica. Na maioria dos sistemas, os recursos mínimos usados por esta varredura são inferiores aos recursos salvos graças à reutilização de planos existentes em vez da compilação de cada instrução SQL.

Os algoritmos para corresponder as instruções SQL novas a planos de execução existentes não utilizados no cache requerem que todas as referências de objeto sejam qualificadas completamente. Por exemplo, a primeira dessas instruções SELECT não corresponde a um plano existente e a segunda corresponde:

SELECT * FROM Person;

SELECT * FROM Person.Person;

Removendo planos de execução do cache de procedimento

Os planos de execução permanecem no cache de procedimento enquanto houver memória suficiente para armazená-los. Quando há pressão de memória, o Mecanismo de Banco de Dados usa uma abordagem baseada em custo para determinar quais planos de execução remover do cache de procedimento. Para tomar uma decisão baseada em custo, o Mecanismo de Banco de Dados aumenta e reduz uma variável de custo atual para cada plano de execução de acordo com os fatores a seguir.

Quando um processo de usuário insere um plano de execução no cache, esse processo define o custo atual igual ao custo de compilação da consulta original. Para planos de execução ad hoc, o processo de usuário define o custo atual como zero. Depois disso, cada vez que um processo de usuário faz referência a um plano de execução, ele redefine o custo atual como igual ao custo de compilação original; para planos de execução ad hoc, o processo de usuário aumenta o custo atual. Para todos os planos, o valor máximo para o custo atual é o custo de compilação original.

Quando há pressão de memória, o Mecanismo de Banco de Dados responde removendo planos de execução do cache de procedimento. Para determinar quais planos remover, o Mecanismo de Banco de Dados examina repetidamente o estado de cada plano de execução e remove planos quando seu custo atual é igual a zero. Um plano de execução com custo atual igual a zero não é removido automaticamente quando há pressão de memória. Ele é removido apenas quando o Mecanismo de Banco de Dados examina o plano e o custo atual é igual a zero. Ao examinar um plano de execução, o Mecanismo de Banco de Dados pressiona o custo atual em direção a zero reduzindo o custo atual se uma consulta não estiver usando o plano no momento.

O Mecanismo de Banco de Dados examina repetidamente os planos de execução até que o suficiente tenha sido removido para atender às necessidades de memória. Embora haja pressão de memória, o custo de um plano de execução pode ser aumentado e reduzido mais de uma vez. Quando não houver mais pressão de memória, o Mecanismo de Banco de Dados para de reduzir o custo atual de planos de execução não utilizados e todos os planos de execução permanecem no cache de procedimento, mesmo que seu custo seja igual a zero.

O Mecanismo de Banco de Dados usa o monitor de recursos e os threads de usuário para liberar memória do cache de procedimento em resposta à pressão de memória. O monitor de recursos e os threads de usuário podem examinar planos em execução simultânea para reduzir o custo de cada plano de execução não utilizado. O monitor de recursos remove planos de execução do cache de procedimento quando há pressão de memória global. Ele libera memória para aplicar políticas para a memória do sistema, a memória do processo, a memória do pool de recursos e o tamanho máximo de todos os caches.

O tamanho máximo para todos os caches é uma função do tamanho do pool de buffers e não pode exceder a memória máxima do servidor. Para obter mais informações sobre como configurar os parâmetros da memória máxima do servidor em sp_configure (Transact-SQL).

Os threads de usuário removem planos de execução do cache de procedimento quando há pressão de memória de cache único. Eles aplicam políticas de tamanho máximo de cache único e do número máximo de entradas do cache único.

Os exemplos a seguir ilustram quais planos de execução são removidos do cache de procedimento:

  • Um plano de execução é referenciado frequentemente para que seu custo nunca seja zerado. O plano permanece no cache de procedimento e não é removido a menos que haja pressão de memória e o custo atual seja zero.

  • Um plano de execução ad hoc é inserido e não é referenciado novamente até que haja pressão de memória. Como os planos ad hoc são inicializados com um custo atual igual a zero, quando o mecanismo do banco de dados examina o plano de execução, ele vê o custo atual igual a zero e remove o plano do cache de procedimento. O plano de execução ad hoc permanece no cache de procedimento com um custo atual igual a zero quando não há pressão de memória.

Para remover manualmente um único plano ou todos os planos do cache, use DBCC FREEPROCCACHE (Transact-SQL).

Recompilando planos de execução

Determinadas alterações em um banco de dados podem tornar um plano de execução ineficiente ou inválido, com base no novo estado do banco de dados. O SQL Server detecta as alterações que invalidam um plano de execução e marca o plano como inválido. Um plano novo deve ser recompilado para a próxima conexão que executar a consulta. As condições que invalidam um plano incluem o seguinte:

  • Alterações feitas em uma tabela ou exibição referenciadas pela consulta (ALTER TABLE e ALTER VIEW).

  • Alterações em quaisquer índices usadas pelo plano de execução.

  • Atualizações em estatísticas usadas pelo plano de execução, geradas explicitamente de uma instrução, como UPDATE STATISTICS ou geradas automaticamente.

  • Cancelando um índice usado pelo plano de execução.

  • Uma chamada explícita para sp_recompile.

  • Números grandes de alterações para chaves (gerados por instruções INSERT ou DELETE de outros usuários que modificam a tabela referenciada pela consulta).

  • Para tabelas com disparadores, se o número de linhas nas tabelas inseridas ou excluídas aumentar significativamente.

  • Executando um procedimento armazenado usando a opção WITH RECOMPILE.

A maioria das recompilações é necessária para exatidão da instrução ou para obter planos de execução de consulta mais rápidos potencialmente.

No SQL Server 2000, sempre que uma instrução dentro de um lote causar recompilação, o lote inteiro será recompilado, mesmo que enviado por um procedimento armazenado, disparador, lote ad hoc ou instrução preparada. No SQL Server 2005 e posterior, apenas a instrução dentro do lote que causa recompilação é recompilada. Por causa dessa diferença, as contagens de recompilação das versões SQL Server 2000 e posteriores não são comparáveis. Além disso, há mais tipos de recompilações no SQL Server 2005 e posterior devido ao seu conjunto de recursos expandido.

A recompilação em nível de instrução beneficia o desempenho porque, na maioria dos casos, um número pequeno de instruções causa recompilações e as penalidades associadas, em termos de bloqueios e tempo de CPU. Essas penalidades são evitadas nas outras instruções do lote que não precisa ser recompilado.

O evento de rastreamento SQL Server ProfilerSP:Recompile apresenta recompilações em nível de instrução. Esse evento de rastreamento informa apenas recompilações de lote no SQL Server 2000. Mais adiante, a coluna TextData desse evento é populada. Portanto, a prática do SQL Server 2000 de ter que rastrear SP:StmtStarting ou SP:StmtCompleted para obter o texto Transact-SQL que causou a recompilação não será mais necessária.

O evento de rastreamento SQL:StmtRecompile informa as recompilações em nível de instrução. Esse evento de rastreamento pode ser usado para controlar e depurar recompilações. Considerando que SP:Recompile é gerado apenas para procedimentos armazenados e disparadores, o SQL:StmtRecompile é gerado para procedimentos armazenados, disparadores, lotes ad hoc, lotes que são executados usando sp_executesql, consultas preparadas e SQL dinâmico.

A coluna EventSubClass de SP:Recompile e SQL:StmtRecompile contém um código inteiro que indica o motivo da recompilação. A tabela a seguir contém o significado de cada número de código.

Valor EventSubClass

Descrição

1

Esquema alterado.

2

Estatísticas alteradas.

3

Compilação adiada.

4

Opção SET alterada.

5

Tabela temporária alterada.

6

Conjunto de linhas remoto alterado.

7

Permissão FOR BROWSE alterada.

8

Ambiente de notificação de consulta alterado.

9

Exibição particionada alterada.

10

Opções de cursor alteradas.

11

OPTION (RECOMPILE) solicitada.
ObservaçãoObservação

Quando a opção do banco de dados AUTO_UPDATE_STATISTICS estiver definida como ON, as consultas serão recompiladas quando destinadas a tabelas ou exibições indexadas cujas estatísticas foram atualizadas ou cujas cardinalidades foram alteradas significativamente desde a última execução. Esse comportamento se aplica a tabelas padrão definidas pelo usuário, tabelas temporárias e tabelas inserted e deleted criadas por disparadores de DML. Se o desempenho de consulta for afetado por recompilações excessivas, considere a alteração dessa configuração para OFF. Quando a opção do banco de dados AUTO_UPDATE_STATISTICS SET for definida como OFF, não ocorrerá nenhuma recompilação com base em estatísticas ou alterações de cardinalidade, com exceção das tabelas inserted e deleted criadas por disparadores DML INSTEAD OF. Como essas tabelas são criadas em tempdb, a recompilação de consultas que as acessam depende da configuração de AUTO_UPDATE_STATISTICS em tempdb. Observe que no SQL Server 2000, as consultas continuam a recompilação com base nas alterações de cardinalidade para as tabelas inserted e deleted do disparador de DML, mesmo quando essa configuração estiver definida como OFF. Para obter mais informações sobre como desabilitar AUTO_UPDATE_STATISTICS, consulte Usando estatísticas para melhorar o desempenho de consultas.