Quais são as principais diferenças entre opção(Otimizar para desconhecido) e opção(recompilar)?

A opção (Optimizar para o desconhecido) reutilizará a 'cache' em vez de a recompilar de cada vez?

OPTION(RECOMPILE) vai forçar uma recompensa em cada execução e é uma abordagem bastante pesada. Só faz sentido num ambiente analítico DW / BI onde cada consulta pode ser diferente, complexa e provavelmente com um tempo de execução significativo.

Também tem outras opções à sua disposição:

• Guias Do Plano
• Query Store (apenas em SQL 2016) , uma vez que lhe permite 'pin' um plano de execução favorecido

Ambos permitem-lhe obter o mesmo efeito que no seu post, mas de uma forma não invasiva (sem alterações no código do aplicativo/Consulta).

Irá OPTION(OPTIMIZE FOR UNKNOWN) reutilizar a 'cache' em vez de a recompilar todas as vezes?

Existem duas diferenças principais entre OPTION(OPTIMIZE FOR UNKNOWN) e {[3] } como se pode ver nesta citação de MSDN:

OPTIMIZE FOR UNKNOWN

Dá instruções ao optimizador da pesquisa para usar estatísticas dados em vez dos valores iniciais para todas as variáveis locais quando o consulta é compilado e otimizado, incluindo parâmetros criados com forcado parametrização.

RECOMPILE

Dá instruções ao motor de Base de dados do servidor SQL para descartar o plano gerado para a consulta após a execução, forçando o Optimizador da consulta para recompilar um plano de consulta da próxima vez que a mesma consulta for executada. Sem especificar RECOMPILE, os planos de pesquisa do motor da Base de dados caches e reutilizá-los. Ao compilar os planos de pesquisa, a dica da pesquisa RECOMPILE utiliza os valores atuais de quaisquer variáveis locais na consulta e, se o a consulta está dentro de um procedimento armazenado, passando os valores actuais para parametro.

1. Caching (ou não) do plano de consulta.

Normalmente o plano de consulta gerado é Cache e reutilizado. Não afecta Esta característica do motor. RECOMPILE suprime este recurso e diz ao motor para descartar o plano e não colocá-lo no cache.

1. utilizando (ou não) valores reais dos parâmetros durante o plano geracao.

Normalmente o Optimizador "fareja" os valores dos parâmetros e utiliza estes valores ao gerar o plano. OPTIMIZE FOR UNKNOWN suprime esta funcionalidade e diz ao motor para tratar todos os parâmetros como se os seus valores fossem desconhecidos. Optimizer tem regras embutidas e heurísticas como usar estatísticas disponíveis para vários critérios de filtragem. Vês Otimizar para ... medíocre? para mais detalhes. Normalmente, o sniffing de parâmetros é usado na primeira execução do procedimento de consulta / armazenamento e usa o valores dos parâmetros durante a primeira execução. O plano gerado é Cache e mais tarde pode ser reutilizado.

Uma coisa não óbvia a recordar aqui é que em ambos os casos (normal sem qualquer sugestão de consulta e com OPTIMIZE FOR UNKNOWN dica) o plano gerado tem de ser válido e produzir um resultado correcto para qualquer possível VALOR do parâmetro. Ele é adaptado para os valores sniffed que foram usados durante a primeira execução no caso normal/no-hint; ele não é adaptado a qualquer valor específico no caso OPTIMIZE FOR UNKNOWN, mas ainda é válido se o parâmetro mudar mais tarde de alguma forma.

OPTION(RECOMPILE) permite ao optimizer alinhar os valores reais dos parâmetros durante cada execução e o optimizer usa valores reais dos parâmetros para gerar um plano melhor. Não tem que se preocupar que o plano gerado não possa funcionar com algum outro valor de parâmetro, porque o plano não será cache e reaproveitar.

Este efeito é visível principalmente nas condições de pesquisa dinâmicas . Por exemplo:

SELECT ...
FROM T
WHERE
    (@ParamSomeID = 0)
    OR
    (
        @ParamSomeID = -1
        AND
        T.SomeID NOT IN
        (
            SELECT OtherTable.SomeID
            FROM OtherTable
        )
    )
    OR
    (
        T.SomeID IN
        (
            SELECT OtherTable.SomeID
            FROM OtherTable
            WHERE OtherTable.SomeID = @ParamSomeID
        )
    )
OPTION(RECOMPILE)

Se @ParamSomeID for 0 o Optimizador trataria a consulta como se não tivesse nenhuma cláusula WHERE. O plano não mencionaria nada.

Se @ParamSomeID for -1, o plano juntar-se-á T a OtherTable usando a semi-junção Anti esquerda e digitalizará todo o OtherTable.

Se @ParamSomeID é, digamos, 5, o plano faria uma pesquisa de índice em unique index on OtherTable e ler apenas uma linha de OtherTable.

Sem OPTION(RECOMPILE) Este tipo de simplificação e transformação não aconteceria.

Outra razão para usar OPTION(RECOMPILE) é quando a sua distribuição de dados está muito distorcida. Por exemplo, você tem uma tabela com linhas 1M. Uma coluna tem o valor 0 em 990K linhas e valores de 1 a 10 em 1K linhas. As consultas que filtram nesta coluna devem ter planos diferentes, dependendo do valor real do filtro.

Em ambos os exemplos acima OPTIMIZE FOR UNKNOWN seria gerar um plano medíocre.

Usei ambos. OPTION(OPTIMIZE FOR UNKNOWN) foi usado para um procedimento de pesquisa pesada armazenado que levou em uma variedade de parâmetros. Havia certas condições, desconhecidas para mim (estatísticas e o que não), que iriam desviar a otimização, a consulta Era mundana, no entanto, causaria sérios atrasos (e até mesmo tempo fora). Resolvi este problema, mas não foi o ideal.

As entranhas do procedimento armazenado impulsionaram uma solução "Resultado à medida que se digita" na qual cada três teclas desencadeariam uma pesquisa DB e os resultados iriam aparecer numa queda.