Poderá detectar uma diminuição no desempenho das consultas depois de efectuar determinadas operações de manutenção da base de dados ou operações de transacção normal no SQL Server 2005

No Microsoft SQL Server 2005, poderá detectar uma diminuição no desempenho das consultas depois de efectuar determinadas operações de manutenção da base de dados ou operações de transacção normal. Por exemplo, poderá notar uma diminuição no desempenho das consultas repentino depois de restaurar uma cópia de segurança da base de dados.

A partir do SQL Server 2005 Service Pack 2, irá notar que mensagens como se segue no registo de erros do SQL Server quando este problema ocorre: No entanto, pode executar o comando DBCC FREEPROCCACHE ou o comando DBCC FREESYSTEMCACHE para limpar a cache de procedimento. Se a cache de procedimento é limpa, executando um destes comandos, irá notar que mensagens ser semelhantes ao seguinte no registo de erros do SQL Server: Por conseguinte, pode examinar o registo de erros do SQL Server para determinar se o problema é causado pelo problema descrito neste artigo.

Nota Este comportamento não ocorre no Microsoft SQL Server 2008.

Este problema ocorre porque determinadas operações de manutenção da base de dados ou operações de transacção normal limpar a cache procedimento completo.

Este comportamento ocorre por predefinição.

A cache de procedimento completo é limpa quando são executadas determinadas operações de nível de base de dados nos seguintes cenários:

• Uma base de dados tem a opção de base de dados AUTO_CLOSE definido como ON. Quando nenhuma ligação de utilizador referencia ou utiliza a base de dados, a tarefa de segundo plano tenta fechar e encerrar automaticamente a base de dados.
• Executar várias consultas numa base de dados com opções predefinidas. Em seguida, a base de dados é interrompida.
• Um instantâneo da base de dados para uma base de dados de origem é interrompido.
  
  Nota Instantâneos de base de dados só estão disponíveis no Microsoft SQL Server 2005 Enterprise Edition.
• Alterar o estado da base de dados para OFFLINE ou ONLINE.
• Reconstruir o registo de transacções para uma base de dados com êxito.
• Restaurar uma cópia de segurança da base de dados.
• Executa a declaração DBCC CHECKDB.
  
  Nota Isto acontece apenas em versões do SQL Server 2005 anteriores ao SQL Server 2005 SP2. Depois de instalar o SQL Server 2005 SP2 ou versões posteriores, a procedimento completo não esvaziamento da cache quando executa a declaração DBCC CHECKDB.
• Desanexar uma base de dados.
• Especificar uma das seguintes opções quando executar a instrução ALTER DATABASE:
  • OFFLINE
  • ONLINE
  • MODIFICAR FILEGROUP PREDEFINIDO
  • MODIFY_NAME
  • MODIFICAR FILEGROUP READ_WRITE
  • AGRUPAR
  • MODIFICAR FILEGROUP READ_ONLY
  • READ_ONLY
  • READ_WRITE
• A cache de procedimento completo é limpa se uma das seguintes opções de servidor for alterada pela instrução RECONFIGURAÇÃO:
  • Cruz de encadeamento de propriedade de base de dados
  • índice criar memória (KB)
  • tempo limite de consulta remoto (s)
  • Opções do utilizador
  • texto máximo repl tamanho (B)
  • Limiar de custo para parallelism
  • máximo grau de parallelism
  • memória mínimo por consulta (KB)
  • espera de consulta (s)
  • memória do servidor min (MB)
  • memória do servidor máximo (MB)
  • consulta governor custo limite
  Nota Cache de procedimento não será eliminada se não alterar o valor real ou se o novo valor para a opção de servidor de memória do servidor máximo está definido para 0.

Se detectar este problema, repare as alterações seguintes valores quando utiliza o Monitor de desempenho para recolher dados de contadores de desempenho do SQL Server 2005:

• objecto de desempenho: Processo
  contador: % de tempo do processador
  instância: sqlservr
  
  O valor deste contador aumentará devido aumento de actividade da CPU. Essencialmente, a cache de procedimento completo é limpa se este problema ocorrer. Por conseguinte, os pedidos subsequentes deverá gerar novos planos devem ser colocados em cache. Este comportamento ligeiramente aumentará a actividade de CPU.
• objecto de desempenho: Cache SQLServer:Plan
  contador: Cache objecto números
  instância: _Total
  
  objecto de desempenho: Cache SQLServer:Plan
  contador: Páginas de cache
  instância: _Total
  
  Os valores destes contadores subitamente diminuirá.
  
  Nota Para uma instância nomeada do SQL Server 2005, o objecto de desempenho chama MSSQL $ InstanceName: plano de cache.
• objecto de desempenho: Estatísticas de SQLServer:SQL
  contador: SQLCompilations/seg
  
  O valor deste contador aumentará significativamente após este incidente.
  
  Nota Para uma instância nomeada do SQL Server 2005, o objecto de desempenho chama MSSQL $ InstanceName: estatísticas de SQL.

Se capturar um rastreio de Profiler SQL utilizando o evento SP:CacheRemove , repare que este evento é gerado juntamente com o seguinte valor de coluna TextData quando este problema ocorre: