Quão determinista é a criação do plano de execução no SQL Server?

Dadas essas constantes, o SQL Server sempre produzirá o mesmo plano para uma determinada consulta? Se não, há outras considerações? Existe também um elemento de não determinismo a ser considerado?

A compilação de consultas é determinística, tanto quanto sei. Um dos objetivos originais do projeto de QO era que fosse possível reproduzir os planos de execução em um sistema diferente usando uma cópia somente estatística do banco de dados. Existem algumas sutilezas nisso, em relação aos parâmetros de configuração, como a quantidade de memória disponível e o número de processadores lógicos, mas eles são cobertos por sua lista de itens a serem sincronizados.

Advertência: Isso é verdade, desde que a palavra 'mesmo' em sua lista seja entendida como exatamente o mesmo em todos os aspectos . Por exemplo, as estatísticas 'mesmas' podem existir nos dois sistemas, mas elas são exatamente as mesmas se as etapas do histograma e as informações de densidade forem idênticas .

Dito isso, o processo de otimização também é extremamente complexo , o que significa que pode ser difícil garantir que todas as entradas desse processo determinístico sejam idênticas e que todos os estados internos sejam semelhantes o suficiente para garantir que o mesmo caminho de código seja seguido pelo otimizador para um determinado compilação. Se a consulta contiver acesso fora do banco de dados (para outro banco de dados ou instância), esses ambientes também devem ser idênticos.

Uma coisa que eu acrescentaria à sua lista é verificar se também existem guias de plano no segundo banco de dados.

O uso de funções não determinísticas, como GETDATE()em consultas, pode significar que você também obtém um plano diferente. Embora o otimizador principal não use o valor diretamente, a estimativa de cardinalidade pode (consulte Dobragem constante e avaliação de expressão durante a estimativa de cardinalidade ). Não tenho certeza se essa classe de diferença se enquadra no escopo da questão, porque ambos os sistemas produziriam o mesmo plano se executados ao mesmo tempo (ou, mais geralmente, com as mesmas variáveis ​​de entrada, parâmetros e valores de função).