Usamos algumas visualizações 'agregadas' para selecionar várias tabelas usando um discriminador (observação: essas visualizações não são visualizações particionadas, porque o discriminador não está nas tabelas base). Isso normalmente funciona bem ao usar option(recompile), pois o planejador de consulta eliminaráunion all os caminhos não alcançáveis antes da seleção de um plano de consulta.
No entanto, essa otimização de dobra constante parece derrotada ao selecionar o resultado em uma variável escalar. Selecionar o resultado em uma variável de tabela temporária não desotimiza a recompilação.
Aqui está um caso de reprodução no SQL Server 2017:
-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1));
-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table
Consulta normal, que resulta em um plano de consulta otimizado tocando apenas uma das union allramificações:
declare @descrim int = 2;
select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"
No entanto, assim que uma "selecionar na variável escalar" é usada, o plano se torna desotimizado , pois não elimina a união não utilizada. (O plano ainda é otimizado corretamente ao usar um valor literal no texto da consulta.)
declare @descrim int = 2;
declare @brokeit int;
select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!
1. Essa desotimização é "esperada"?
2. Onde esse comportamento significativo de desotimização em relação a option(recompile)e/ou seleção em uma variável escalar está documentado ou discutido em profundidade?
3. Existe uma maneira simples de obter um plano otimizado para recompilação select @x = ..sem usar uma tabela temporária (variável)?
Embora durante a execução da consulta union allisso impeça o acesso de E/S real ao artefato secundário, esse ainda é um problema com a geração do plano de consulta. No caso de erro específico que gera essa pergunta, deixar várias tabelas para consideração impede o SQL Server de escolher um plano de busca apropriado e as opções de plano resultantes são escolhas muito ruins no domínio fornecido.
O primeiro plano "bom":
O segundo e "ruim" plano:
Esse plano "ruim" também tem um aviso de conversão implícito, me fazendo suspeitar que a seleção em uma variável escalar pode estar ignorando muitas otimizações diferentes - ou até mesmo ignorando a option(recompile)dica completamente.
A dobra constante tem um significado particular no SQL Server. Não está diretamente envolvido em sua pergunta. Os recursos que se combinam para produzir uma ampla simplificação do plano de execução para você são a otimização de incorporação de parâmetros (PEO) e a detecção de contradição .
PEO incorpora o valor literal de, por exemplo, um parâmetro ou variável local no texto da consulta, em circunstâncias em que é seguro fazê-lo. Um dos requisitos é que
OPTION (RECOMPILE)é especificado. Isso garante que o plano gerado nunca será reutilizado, portanto, pode ser seguro substituir não literais por literais sniffados.A
OPTION (RECOMPILE)dica em si fornece apenas que um novo plano será gerado em cada execução, os valores rastreados de quaisquer parâmetros serão usados para estimativa de cardinalidade e o plano único gerado não será armazenado em cache para reutilização após a execução.O PEO foi adicionado ao produto pela primeira vez no SQL Server 2008, embora tenha sido desabilitado logo em seguida devido à possibilidade de resultados incorretos. Ele foi reativado no SQL Server 2008 SP1 CU5 ( postagem no blog da Microsoft ).
Uma consulta otimizada com PEO aplicado aparece para o otimizador de consulta exatamente como se a consulta tivesse sido escrita com literais em vez de parâmetros ou variáveis. A detecção de contradição pode remover cláusulas inteiras ou subárvores de operadores relacionais onde
WHERE 0 = 1aparecem expressões literais como. Esse recurso existe porque as ferramentas automatizadas geralmente geram esse SQL.Nem sempre é seguro aplicar PEO, mas as exceções não são documentadas oficialmente. Uma exceção é onde ocorre a atribuição de variável (existem outras, como onde o parâmetro aparece em uma
OPTIMIZE FORcláusula). Meu entendimento é que a atribuição de variáveis envolve uma boa quantidade de comportamento legado complexo, com semântica ocasionalmente estranha, preservada por motivos de compatibilidade com versões anteriores. Seria impraticável garantir que o PEO funcionaria corretamente em todas as circunstâncias, então ele está desabilitado para esse caso.PEO é uma facilidade oportunista que vai além do comportamento documentado do
OPTION (RECOMPILE). Ele pode oferecer benefícios de desempenho significativos em muitos casos, mas não é documentado oficialmente. Pode-se considerá-lo útil como um recurso de bônus - bom quando você o obtém, mas não há reembolso em caso de decepção.No seu exemplo, onde o PEO não pode ser aplicado, a eliminação da execução da subárvore é fornecida por filtros de inicialização (que estão documentados). Os operadores de filtro mostrados no plano "não otimizado" são filtros de inicialização que só executam sua subárvore se o predicado de inicialização for avaliado como true .
A falta de um "plano de busca", no contexto PEO, muitas vezes se deve a otimizações que só podem ser realizadas (por questões de segurança ou limitações de implementação) quando um valor literal está presente. Este literal pode aparecer no texto original, ou pode ter sido substituído via PEO. Um exemplo disso é a regra de otimização
SelOnSeqPrj, que permite que um predicado passe por uma função de sequência comoROW_NUMBERquando seguro, mas somente quando um valor literal estiver disponível .Uma reprodução SQL Server 2017 do código na pergunta não produz a conversão Compute Scalar e implícita extra mencionada na pergunta. Parece que a consulta usada para produzir esse plano era diferente daquela fornecida na pergunta. Ou talvez a instância ou banco de dados tenha alguma configuração ou opção importante que não foi especificada. De qualquer forma, não consegui reproduzi-lo.
A
OPTION (RECOMPILE)dica nunca é ignorada. A única conversão implícita na consulta é dobigintresultado interno deCOUNT(*)paraintegerconforme exigido porCOUNT(em oposição aCOUNT_BIG).Dependendo dos requisitos e restrições do aplicativo real por trás da pergunta, pode ser necessário empregar SQL dinâmico ou alguma outra solução. Sinta-se à vontade para fazer uma nova pergunta sobre possíveis soluções para o problema subjacente, se puder ser expressa de maneira adequada ao nosso formato de perguntas e respostas.
As breves respostas às suas perguntas são:
Você pode SELECT INTO em uma tabela temporária e atribuir sua variável a partir dela como uma solução alternativa: