Você pode ter uma diminuição no desempenho da consulta depois de você executar determinadas operações de manutenção de banco de dados ou operações de transação regulares no SQL Server 2005

No Microsoft SQL Server 2005, você pode enfrentar uma diminuição no desempenho da consulta depois de executar determinadas operações de manutenção de banco de dados ou operações de transação regular. Por exemplo, você notará uma diminuição repentina no desempenho da consulta após restaurar um backup do banco de dados.

Iniciando com o SQL Server 2005 Service Pack 2, você irá notar mensagens, como a seguir no log de erro do SQL Server quando esse problema ocorre: No entanto, você pode executar o comando DBCC FREEPROCCACHE ou o comando DBCC FREESYSTEMCACHE para liberar o cache de procedimento. Se o cache de procedimento é liberado executando um desses comandos, você irá notar mensagens que se assemelhem seguinte no log de erro do SQL Server: Portanto, você pode examinar o log de erro do SQL Server para determinar se o problema é causado pelo problema descrito neste artigo.

Observação Esse comportamento não ocorre no Microsoft SQL Server 2008.

Esse problema ocorre porque determinadas operações de manutenção de banco de dados ou operações de transação regular limpar o cache procedimento inteiro.

Esse comportamento é por design.

O cache de procedimento inteiro é limpo quando determinadas operações de nível de banco de dados são realizadas nas seguintes situações:

• Um banco de dados tem a opção de banco de dados AUTO_CLOSE definido como ON. Quando nenhuma conexão de usuário referencia ou usa o banco de dados, tarefa em segundo plano tenta fechar e desligar automaticamente o banco de dados.
• Você pode executar várias consultas em um banco de dados que tenha opções padrão. Em seguida, o banco de dados será interrompido.
• Um instantâneo de banco de dados para um banco de dados de origem é descartado.
  
  Observação Instantâneos de banco de dados só estão disponíveis no Microsoft SQL Server 2005 Enterprise Edition.
• Você alterar o estado do banco de dados para OFFLINE ou ONLINE.
• Recriar o log de transações para um banco de dados com êxito.
• Você restaurar um backup do banco de dados.
• Você executar a instrução DBCC CHECKDB.
  
  Observação Isso é verdade somente nas versões do SQL Server 2005 anteriores ao SQL Server 2005 SP2. Após instalar o SQL Server 2005 SP2 ou posterior, o cache de procedimento inteiro não é liberado quando você executa a instrução DBCC CHECKDB.
• Desanexar um banco de dados.
• Você especificar uma das seguintes opções ao executar a instrução ALTER DATABASE:
  • OFF-LINE
  • ON-LINE
  • MODIFICAR O GRUPO DE ARQUIVOS PADRÃO
  • MODIFY_NAME
  • MODIFICAR READ_WRITE DO GRUPO DE ARQUIVOS
  • AGRUPAR
  • MODIFICAR READ_ONLY DO GRUPO DE ARQUIVOS
  • READ_ONLY
  • READ_WRITE
• O cache de procedimento inteiro está desmarcado se uma das seguintes opções de servidor for alterada, a instrução RECONFIGURE:
  • entre o encadeamento de propriedade do banco de dados
  • índice criar memória (KB)
  • tempo limite de consulta remota (s)
  • Opções de usuário
  • tamanho do texto máximo repl (B)
  • limite de custo para paralelismo
  • grau máximo de paralelismo
  • min memory per query (KB)
  • espera de consulta (s)
  • memória do servidor min (MB)
  • memória máxima do servidor (MB)
  • limite de custo do Gerenciador de consulta
  Observação Cache de procedimento não será desmarcada se o valor real não é alterado ou se o novo valor para a opção de servidor de memória máxima do servidor é definido como 0.

Se você enfrentar esse problema, você notar alterações em valores a seguir ao usar o Monitor de desempenho para coletar dados de contadores de desempenho do SQL Server 2005:

• objeto de desempenho: Processo
  contador: % tempo do processador
  instância: sqlservr
  
  O valor desse contador aumentará devido a maior atividade da CPU. Essencialmente, o cache de procedimento inteiro está desmarcado se esse problema ocorre. Portanto, as solicitações subseqüentes devem gerar novos planos para ser armazenada em cache. Esse comportamento um pouco aumentará a atividade da CPU.
• objeto de desempenho: Cache SQLServer:Plan
  contador: Contagens de objeto do cache
  instância: _Total
  
  objeto de desempenho: Cache SQLServer:Plan
  contador: Páginas do cache
  instância: _Total
  
  Os valores desses contadores, de repente, diminuirá.
  
  Observação Para uma instância nomeada do SQL Server 2005, o objeto de desempenho é chamado de MSSQL $ InstanceName: planejar cache.
• objeto de desempenho: Estatísticas de SQLServer:SQL
  contador: SQLCompilations/s
  
  O valor desse contador aumentará significativamente após esse incidente.
  
  Observação Para uma instância nomeada do SQL Server 2005, o objeto de desempenho é chamado de MSSQL $ InstanceName: SQL estatísticas.

Se você capturar um rastreamento do Profiler SQL usando o evento SP:CacheRemove , você notará que esse evento é gerado junto com o seguinte valor de coluna TextData quando esse problema ocorre: