DBCC Freeproccache acelerou muito o tempo de execução sp, por quê?

Question

Usando o MSSQL 2005

Hoje fui chamado para ver um procedimento armazenado que começou a ser executado lentamente quando usado em nosso programa. Em torno da ordem de alguns segundos, onde ele foi executado instantaneamente. Eu executei o SQL Server Profiler nele e ele estava usando mais de 1000 na CPU e mais de 400.000 leituras. Copiei a linha Exec do SQL Server Profiler para o SQL Management Studio para examinar o plano de execução. O procedimento armazenado foi executado instantaneamente e retornou os resultados corretos. O plano de execução parecia correto, sem erros gritantes. Eu tentei executá-lo várias vezes a partir do nosso programa para ver se ele não estava em cache e eu estava vendo uma lentidão na primeira execução conforme o plano de execução era determinado, mas era consistente em 2-3 segundos por execução.

Para chutes, executei o DBCC FreeProcCache apenas para ver se isso faria com que minhas execuções do SQL Management Studio fossem executadas mais lentamente. Executei o procedimento armazenado no Management Studio e ele ainda funcionava instantaneamente. Depois, executei-o novamente a partir do programa com o profiler em execução, e ele também correu instantaneamente. O criador de perfil mostrou que a CPU havia caído para 0 e as leituras haviam caído para 40. Parece estar ficando consistentemente rápido agora.

Por que rodar o DBCC FreeProcCache acelera um procedimento armazenado tão drasticamente?

    

Answer 1

A resposta curta: sniffing de parâmetro.

A resposta longa: quando você executa pela primeira vez um procedimento armazenado, o otimizador de consulta examina os parâmetros transmitidos a ele e os metadados sobre os objetos que estão sendo consultados. Se esses metadados mudarem significativamente ao longo do tempo (por exemplo, o histograma estatístico muda) ou se os parâmetros iniciais não forem representativos de uma chamada típica, o plano que o otimizador armazena em cache pode se tornar insatisfatório. Ao fazer um freeproccache, você se livra do plano "ruim" e força o otimizador de consultas a executar outra tarefa.

Se você puder descobrir qual procedimento armazenado é o filho problemático, você pode adicionar "com recompilação" à definição do procedimento e ele não armazenará em cache o plano para esse procedimento. Se você puder encontrar a instrução dentro do procedimento que está causando o problema, poderá adicionar "option (recompile)" a ela e apenas essa instrução será recompilada quando o procedimento for executado.