Normalmente, crio guias de plano construindo primeiro uma consulta que usa o plano correto e copiando-a para a consulta semelhante que não usa. No entanto, isso às vezes é complicado, especialmente se a consulta não for exatamente a mesma. Qual é a maneira correta de criar guias de plano do zero?
O SQLKiwi mencionou a elaboração de planos no SSIS, existe uma maneira ou ferramenta útil para auxiliar na elaboração de um bom plano para o SQL Server?
A instância específica em questão é esta CTE: SQLFiddle
with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;
Existe ALGUMA maneira de fazer com que o resultado apareça com exatamente 3 guids distintos e não mais? Espero poder responder melhor às perguntas no futuro, incluindo guias de plano com consultas do tipo CTE que são referenciadas várias vezes para superar algumas peculiaridades do SQL Server CTE.
Hoje nao. Expressões de tabela comuns (CTEs) não recursivas são tratadas como definições de exibição em linha e expandidas na árvore de consulta lógica em cada local em que são referenciadas (assim como as definições de exibição regulares são) antes da otimização. A árvore lógica para sua consulta é:
Observe as duas View Anchors e as seis chamadas para a função intrínseca
newidantes que a otimização seja iniciada. No entanto, muitas pessoas consideram que o otimizador deve ser capaz de identificar que as subárvores expandidas eram originalmente um único objeto referenciado e simplificar de acordo. Também houve várias solicitações de conexão para permitir a materialização explícita de uma CTE ou tabela derivada.Uma implementação mais geral faria com que o otimizador considerasse a materialização de expressões comuns arbitrárias para melhorar o desempenho (
CASEcom uma subconsulta é outro exemplo em que os problemas podem ocorrer hoje). A Microsoft Research publicou um artigo (PDF) sobre isso em 2007, embora ainda não tenha sido implementado. Por enquanto, estamos limitados à materialização explícita usando coisas como variáveis de tabela e tabelas temporárias.Isso foi apenas uma ilusão da minha parte e foi muito além da ideia de modificar os guias do plano. É possível, em princípio, escrever uma ferramenta para manipular o show plan XML diretamente, mas sem a instrumentação específica do otimizador, usar a ferramenta provavelmente seria uma experiência frustrante para o usuário (e para o desenvolvedor que pensa nisso).
No contexto específico desta questão, tal ferramenta ainda seria incapaz de materializar o conteúdo CTE de uma forma que pudesse ser usada por vários consumidores (para alimentar ambas as entradas para a junção cruzada neste caso). O otimizador e o mecanismo de execução suportam spools de vários consumidores, mas apenas para fins específicos - nenhum dos quais pode ser aplicado a este exemplo específico.
Há uma quantidade razoável de flexibilidade aqui. A forma ampla do plano XML é usada para guiar a busca por um plano final (embora muitos atributos sejam completamente ignorados, por exemplo, tipo de particionamento em trocas) e as regras normais de busca também são consideravelmente relaxadas. Por exemplo, a remoção antecipada de alternativas com base em considerações de custo é desativada, a introdução explícita de junções cruzadas é permitida e as operações escalares são ignoradas.
Há muitos detalhes para aprofundar, mas a colocação de filtros e escalares de computação não pode ser forçada e os predicados do formulário
column = valuesão generalizados para que um plano contendoX = 1ouX = @Xpossa ser aplicado a uma consulta contendoX = 502ouX = @Y. Essa flexibilidade específica pode ajudar muito a encontrar um plano natural para forçar.No exemplo específico, a constante Union All sempre pode ser implementada como um Constant Scan; o número de entradas para o Union All não importa.
Não há como (versões do SQL Server até 2012) reutilizar um único spool para ambas as ocorrências do CTE. Os detalhes podem ser encontrados na resposta do SQLKiwi. Mais abaixo estão duas formas de materializar o CTE duas vezes, o que é inevitável pela natureza da consulta. Ambas as opções resultam em uma contagem líquida distinta de guid de 6.
O link do comentário de Martin para o site de Quassnoi em um blog sobre o planejamento de um CTE foi uma inspiração parcial para esta pergunta. Ele descreve uma maneira de materializar um CTE para fins de uma subconsulta correlacionada, que é referenciada apenas uma vez, embora a correlação possa fazer com que ela seja avaliada várias vezes. Isso não se aplica à consulta na pergunta.
Opção 1 - Guia do Plano
Pegando dicas da resposta do SQLKiwi, reduzi o guia a um mínimo que ainda fará o trabalho, por exemplo, os
ConstantScannós listam apenas 2 operadores escalares que podem se expandir suficientemente para qualquer número.Opção 2 - Varredura Remota
Ao aumentar o custo da consulta e introduzir um Remote Scan, o resultado é materializado.
Com toda a seriedade, você não pode cortar planos de execução xml do zero. Criá-los usando o SSIS é ficção científica. Sim, é tudo XML, mas são de universos diferentes. Olhando para o blog de Paul sobre esse assunto , ele está dizendo "muito da maneira que o SSIS permite ..." então possivelmente você entendeu mal? Não acho que ele esteja dizendo "use o SSIS para criar planos", mas sim "não seria ótimo poder criar planos usando uma interface de arrastar e soltar como o SSIS". Talvez, para uma consulta muito simples, você possa gerenciar isso, mas é um exagero, possivelmente até uma perda de tempo. Trabalho ocupado, você pode dizer.
Se estou criando um plano para uma dica ou guia de plano USE PLAN, tenho algumas abordagens. Por exemplo, posso remover registros de tabelas (por exemplo, em uma cópia do banco de dados) para influenciar as estatísticas e encorajar o otimizador a tomar uma decisão diferente. Também usei variáveis de tabela em vez de toda a tabela na consulta para que o otimizador pense que cada tabela contém 1 registro. Em seguida, no plano gerado, substitua todas as variáveis da tabela pelos nomes originais da tabela e troque-os como o plano. Outra opção seria usar a opção WITH STATS_STREAM de UPDATE STATISTICS para falsificar estatísticas, que é o método usado ao clonar cópias de bancos de dados somente estatísticas, por exemplo
Passei algum tempo mexendo com planos de execução xml no passado e descobri que, no final, o SQL apenas diz "Não estou usando isso" e executa a consulta como deseja de qualquer maneira.
Para o seu exemplo específico, tenho certeza de que você pode usar set rowcount 3 ou TOP 3 na consulta para obter esse resultado, mas acho que esse não é o seu ponto. A resposta correta seria realmente: use uma tabela temporária. Eu votaria a favor disso :) Não é uma resposta correta seria "passar horas até dias cortando seu próprio plano de execução XML personalizado, onde você tenta enganar o otimizador para fazer um spool preguiçoso para o CTE que pode nem funcionar de qualquer maneira, pareceria inteligente mas também seria impossível de manter".
Não estou tentando ser construtivo, apenas minha opinião - espero que ajude.
Finalmente no SQL 2016 CTP 3.0 existe uma forma, mais ou menos : )
Usando o sinalizador de rastreamento e eventos estendidos conforme detalhado por Dmitry Pilugin aqui , você pode (um tanto arbitrariamente) pescar três guids exclusivos dos estágios intermediários da execução da consulta.
NB Este código NÃO se destina à produção ou uso sério no que diz respeito à imposição do plano CTE, apenas uma visão despreocupada de um novo sinalizador de rastreamento e uma maneira diferente de fazer as coisas:
Testado na versão (CTP3.2) - 13.0.900.73 (x64), apenas por diversão.
Descobri que o traceflag 8649 (plano paralelo de força) induziu esse comportamento para a coluna guid à esquerda em minhas instâncias de 2008, R2 e 2012. Não precisei usar o flag no SQL 2005 onde o CTE se comportou corretamente. Tentei usar o plano gerado no SQL 2005 nas instâncias superiores mas não validava.
Usando a dica, usando um guia de plano incluindo a dica ou usando o plano gerado pela consulta com a dica em um USE PLAN etc, tudo funcionou.