A conversão de uma coluna regular em uma coluna computada persistente está fazendo com que essa consulta não consiga fazer buscas de índice. Por quê?
Testado em várias versões do SQL Server, incluindo 2016 SP1 CU1.
Reprodução
O problema é com table1, col7.
As tabelas e consulta são uma versão parcial (e simplificada) dos originais. Estou ciente de que a consulta pode ser reescrita de maneira diferente e, por algum motivo, evitar o problema, mas precisamos evitar tocar no código, e a questão de por table1que não pode ser buscada ainda permanece.
Como Paul White mostrou (obrigado!), a busca está disponível se forçada, então a questão é: Por que a busca não é escolhida pelo otimizador, e se podemos fazer algo diferente para que a busca aconteça como deveria, sem alterar o código?
Para esclarecer a parte problemática, aqui está a varredura relevante no plano de execução ruim:
Por que a busca não é escolhida pelo otimizador
TL:DR A definição de coluna computada expandida interfere na capacidade do otimizador de reordenar inicialmente as junções. Com um ponto de partida diferente, a otimização baseada em custo segue um caminho diferente pelo otimizador e termina com uma escolha de plano final diferente.
Detalhes
Para todas as consultas, exceto as mais simples, o otimizador não tenta explorar nada como todo o espaço de planos possíveis. Em vez disso, ele escolhe um ponto de partida de aparência razoável e , em seguida, gasta um esforço orçado explorando variações lógicas e físicas, em uma ou mais fases de pesquisa, até encontrar um plano razoável.
A principal razão pela qual você obtém planos diferentes (com diferentes estimativas de custo final) para os dois casos é que existem diferentes pontos de partida. Começando de um lugar diferente, a otimização termina em um lugar diferente (após seu número limitado de iterações de exploração e implementação). Espero que isso seja razoavelmente intuitivo.
O ponto de partida que mencionei é um pouco baseado na representação textual da consulta, mas são feitas alterações na representação da árvore interna à medida que ela passa pelos estágios de análise, associação, normalização e simplificação da compilação da consulta.
É importante ressaltar que o ponto de partida exato depende muito da ordem de junção inicial selecionada pelo otimizador. Essa escolha é feita antes que as estatísticas sejam carregadas e antes que quaisquer estimativas de cardinalidade sejam derivadas. No entanto, a cardinalidade total (número de linhas) em cada tabela é conhecida, tendo sido obtida a partir de metadados do sistema.
A ordenação inicial de junção é, portanto, baseada em heurística . Por exemplo, o otimizador tenta reescrever a árvore de forma que as tabelas menores sejam unidas antes das maiores e as junções internas venham antes das junções externas (e junções cruzadas).
A presença da coluna computada interfere nesse processo, mais especificamente na capacidade do otimizador de enviar junções externas para baixo na árvore de consulta. Isso ocorre porque a coluna computada é expandida em sua expressão subjacente antes que ocorra a reordenação da junção, e mover uma junção após uma expressão complexa é muito mais difícil do que movê-la além de uma referência de coluna simples.
As árvores envolvidas são bastante grandes, mas, para ilustrar, a árvore de consulta inicial da coluna não computada começa com: (observe as duas junções externas na parte superior)
LogOp_Select LogOp_Apply (x_jtLeftOuter) LogOp_LeftOuterJoin LogOp_NAryJoin LogOp_LeftAntiSemiJoin LogOp_NAryJoin LogOp_Get TBL: dbo.table1(alias TBL: a4) LogOp_Select LogOp_Get TBL: dbo.table6(alias TBL: a3) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a3].col18 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) LogOp_Select LogOp_Get TBL: dbo.table1(alias TBL: a1) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a1].col2 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) LogOp_Select LogOp_Get TBL: dbo.table5(alias TBL: a2) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a2].col2 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a4].col2 ScaOp_Identifier QCOL: [a3].col19 LogOp_Select LogOp_Get TBL: dbo.table7(alias TBL: a7) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a7].col22 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a4].col2 ScaOp_Identifier QCOL: [a7].col23 LogOp_Select LogOp_Get TBL: table1(alias TBL: cdc) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [cdc].col6 ScaOp_Const TI(pequeno,ML=2) XVAR(pequeno,Não possui,Valor=4) LogOp_Get TBL: dbo.table5(alias TBL: a5) LogOp_Get TBL: table2(alias TBL: cdt) ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a5].col2 ScaOp_Identifier QCOL: [cdc].col2 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a4].col2 ScaOp_Identifier QCOL: [cdc].col2 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [cdt].col1 ScaOp_Identifier QCOL: [cdc].col1 LogOp_Get TBL: table3(alias TBL: ahcr) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [ahcr].col9 ScaOp_Identifier QCOL: [cdt].col1O mesmo fragmento da consulta de coluna computada é: (observe a junção externa muito mais abaixo, a definição de coluna computada expandida e algumas outras diferenças sutis na ordenação da junção (interna))
LogOp_Select LogOp_Apply (x_jtLeftOuter) LogOp_NAryJoin LogOp_LeftAntiSemiJoin LogOp_NAryJoin LogOp_Get TBL: dbo.table1(alias TBL: a4) LogOp_Select LogOp_Get TBL: dbo.table6(alias TBL: a3) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a3].col18 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) LogOp_Select LogOp_Get TBL: dbo.table1(alias TBL: a1 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a1].col2 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) LogOp_Select LogOp_Get TBL: dbo.table5(alias TBL: a2) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a2].col2 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a4].col2 ScaOp_Identifier QCOL: [a3].col19 LogOp_Select LogOp_Get TBL: dbo.table7(alias TBL: a7) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a7].col22 ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a4].col2 ScaOp_Identifier QCOL: [a7].col23 LogOp_Project LogOp_LeftOuterJoin LogOp_Join LogOp_Select LogOp_Get TBL: table1(alias TBL: cdc) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [cdc].col6 ScaOp_Const TI(pequeno,ML=2) XVAR(pequeno,Não possui,Valor=4) LogOp_Get TBL: table2(alias TBL: cdt) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [cdc].col1 ScaOp_Identifier QCOL: [cdt].col1 LogOp_Get TBL: table3(alias TBL: ahcr) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [ahcr].col9 ScaOp_Identifier QCOL: [cdt].col1 AncOp_PrjList AncOp_PrjEl QCOL: [cdc].col7 ScaOp_Convert char agrupamento 53256,Null,Trim,ML=6 ScaOp_IIF varchar collate 53256,Null,Var,Trim,ML=6 ScaOp_Comp x_cmpEq ScaOp_Intrinsic isnumeric ScaOp_Intrinsic right ScaOp_Identifier QCOL: [cdc].col4 ScaOp_Const TI(int,ML=4) XVAR(int,Não possui,Value=4) ScaOp_Const TI(int,ML=4) XVAR(int,Não possui,Value=0) ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=1) XVAR(varchar,Owned,Value=Len,Data = (0,)) Substring ScaOp_Intrinsic ScaOp_Const TI(int,ML=4) XVAR(int,Não possui,Value=6) ScaOp_Const TI(int,ML=4) XVAR(int,Não possui,Value=1) ScaOp_Identifier QCOL: [cdc].col4 LogOp_Get TBL: dbo.table5(alias TBL: a5) ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a5].col2 ScaOp_Identifier QCOL: [cdc].col2 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [a4].col2 ScaOp_Identifier QCOL: [cdc].col2As estatísticas são carregadas e uma estimativa inicial de cardinalidade é executada na árvore logo após a ordem de junção inicial ser definida. Ter as junções em ordens diferentes também afeta essas estimativas e, portanto, tem um efeito indireto durante a otimização posterior baseada em custos.
Finalmente, para esta seção, ter uma junção externa presa no meio da árvore pode impedir que algumas regras de reordenação de junção adicionais correspondam durante a otimização baseada em custo.
O uso de um guia de plano (ou, de forma equivalente, uma
USE PLANdica - exemplo para sua consulta ) altera a estratégia de pesquisa para uma abordagem mais orientada a objetivos, guiada pela forma geral e pelos recursos do modelo fornecido. Isso explica por que o otimizador pode encontrar o mesmotable1plano de busca em esquemas de coluna computados e não computados, quando um guia de plano ou dica é usado.Se podemos fazer algo diferente para fazer a busca acontecer
Isso é algo que você só precisa se preocupar se o otimizador não encontrar um plano com características de desempenho aceitáveis por conta própria.
Todas as ferramentas de ajuste normais são potencialmente aplicáveis. Você pode, por exemplo, dividir a consulta em partes mais simples, revisar e melhorar a indexação disponível, atualizar ou criar novas estatísticas... e assim por diante.
Todas essas coisas podem afetar as estimativas de cardinalidade, o caminho do código percorrido pelo otimizador e influenciar as decisões baseadas em custos de maneiras sutis.
Você pode finalmente recorrer ao uso de dicas (ou um guia de plano), mas essa geralmente não é a solução ideal.
Perguntas adicionais dos comentários
Não, não há sinalizador de rastreamento para realizar uma pesquisa exaustiva e você não deseja um. O espaço de busca possível é vasto, e tempos de compilação que excedem a idade do universo não seriam bem recebidos. Além disso, o otimizador não conhece todas as transformações lógicas possíveis (ninguém sabe).
As colunas computadas são expandidas (como as visualizações) para permitir oportunidades de otimização adicionais. A expansão pode ser correspondida de volta, por exemplo, a uma coluna ou índice persistente mais tarde no processo, mas isso acontece depois que a ordem de junção inicial é corrigida.