SQL Server: Por que a recompilação de procedimentos armazenados é necessária?

Question

Eu estava executando o script que encontrei no site para marcar todas as minhas tabelas e procedimentos armazenados para recompilação.
(eu mudei para marcar também os procedimentos armazenados)

Eu tenho um aplicativo do Windows Forms (.net 3.5) que usa muitas tabelas de dados e depois de executar esse script, o tempo gasto para abrir um formulário pesado de dados passou de 8 segundos para apenas 4 segundos.

Eu tenho que executar esse script regularmente? Eu pensei que o SQL Server iria cuidar de procedimentos de compilação e tal.

BTW: Essa pergunta deveria estar em SO? Eu pensei que os DBAs estariam aqui no SF.

    

Answer 1

De Microsoft :

"Como um banco de dados é alterado por ações como adicionar índices ou alterar dados em colunas indexadas, os planos de consulta originais usados para acessar suas tabelas devem ser otimizados novamente, recompilando-os. Essa otimização ocorre automaticamente na primeira vez que um procedimento armazenado é Executar após o Microsoft SQL Server é reiniciado.Ele também ocorre se uma tabela subjacente usada pelo procedimento armazenado for alterada.Mas, se um novo índice é adicionado do qual o procedimento armazenado pode beneficiar, otimização não acontece até a próxima vez que o procedimento armazenado é executado depois que o Microsoft SQL Server for reiniciado.Nessa situação, pode ser útil forçar o procedimento armazenado a recompilar na próxima vez que ele for executado

Outro motivo para forçar um procedimento armazenado a recompilar é neutralizar, quando necessário, o comportamento "sniffing de parâmetro" da compilação de procedimento armazenado. Quando o SQL Server executa procedimentos armazenados, qualquer valor de parâmetro usado pelo procedimento quando ele é compilado é incluído como parte da geração do plano de consulta. Se esses valores representam os típicos com os quais o procedimento é chamado posteriormente, o procedimento armazenado se beneficia do plano de consulta toda vez que ele é compilado e executado. Caso contrário, o desempenho pode sofrer. "

    

Answer 2

Considere que o uso da palavra compilar difere no SQL Server do que, digamos, em um aplicativo normal. Em um aplicativo normal, o código é compilado em um estado pronto de linguagem de máquina para que não precise ser interpretado em tempo de execução. O uso da compilação pelo SQL Server é determinar o melhor plano de execução para executar a consulta. Como os dados têm o potencial de mudar constantemente, o SQL Server pode precisar fazer novas determinações com base nos índices disponíveis, na qualidade das estatísticas, etc., e todos eles podem afetar o plano de execução. Como resultado, sempre que um procedimento armazenado é executado, o SQL Server determina se ele pode reutilizar um plano de execução existente para essa consulta ou se precisa gerar um novo. É claro que, se não houver um plano, ele gerará um novo.

Isso, BTW, não é muito diferente do que os aplicativos .NET fazem, se você pensar sobre isso. Aplicativos .NET são compilados para um estado de idioma intermediário, mas somente totalmente compilados por padrão no primeiro tempo de execução.

    

Answer 3

Quando o SP é compilado, seu caminho de execução é fixo. Não é possível aproveitar novos índices ou estratégias diferentes com base nos tamanhos das tabelas. A recompilação permite reavaliar as condições de execução.

Eu não sou muito mais experiente aqui - mas uma regra prática é permitir que os SPs simples sejam recompilados a cada vez. Apenas não compile nos casos mais complexos