Otimizando seus planos de execução com o SQL Server 2014 Cardinality Estimator
Vitor Fava@sqlservermaniac| Vita Database [email protected]
PATROCINADORES
Me
Vitor Fava;
14 anos de experiência;
Princial Database Architect na Vita Database Solutions;
Pass Chapter Leader do SQLManiacs em SP;
Agenda
Processamento de uma consulta;
Otimização;
Importância da estimativa de cardinalidade;
Modelo de pressuposição;
Habilitar o novo CE;
O que mudou no SQL Server 2014;
O que fazer no caso de regressões de plano;
Como as pessoas veem o SQL Server
Como o SQL Server realmente é
Processamento de uma consulta
Parsing;
Binding;
Otimização;
Execução;
Otimização da consulta
1) O plano de execução já está em cache? Se não, continue;
2) É um plano trivial? Se não, continue;
3) Aplicar simplificação;
4) O plano possui um custo baixo? Se não, continue;
5) Iniciar a otimização baseada em custo;
A) Regras basicas, comparar operações de join;
B) O plano tem um custo < 0.2? Se não, continue;
C) Explorar mais regras, alternar a ordem dos joins. Se o custo do plano é < 1.0, utilize-o. Se não, mas o MAXDOP é > 0 e o min cost > cost for
parallelism, utilize um plano com paralelismo;
6) Explore todas as opções, mas escolha o plano menos custoso;
Importância da estimativa de cardinalidade
Quantidade de linhas por operador;
Utilização de um plano serial quando um plano com paralelismo é mais apropriado;
Estratégias de join ineficientes;
Utilização excessiva de memória;
Modelo de pressuposição
Independência;
Uniformidade;
Contido;
Inclusão;
Habilitar o novo CE
Compatibility Level;
Trace Flag 2312;
O que mudou no SQL Server 2014?
A primeira atualização no CE desde o SQL Server 7.0;
Melhora na avaliação de correlação entre múltiplos predicados;
Melhora na estimativa de uma chave ascendente;
Mudanças nos algoritmos de estimativa de joins;
Melhora na avaliação de correlação entre múltiplos predicados Na ausência de estatísticas com múltiplas colunas, o
antigo CE pressupõe que não existe correlação entre as colunas utilizadas no predicado;
Para assumir que existe correlação entra as colunas do predicado, o novo CE utiliza um processo chamado “exponential back-off”;
Demo Time
Melhora na estimativa de uma chave ascendente Novos valores inseridos na tabela, não são
imediatamente considerados no histograma;
O novo CE pressupõe que os valores solicitados pela consulta existem na tabela, mesmo que ainda não existam no histograma;
Densidade * Cardinalidade
Demo Time
Melhora na estimativa de um join simples O antigo CE pressupõe que as colunas utilizadas em um
join com duas tabelas, existam dos dois lados do join;
Caso existam predicados fora do join e que utilizem colunas de tabelas envolvidas no join, o antigo CE pressupõe algum nível de correção entre as colunas
Simple Containment X Base Containment
Demo Time
Testar o ambiente antes de migrar para o novo CE A maioria dos planos será melhorado;
Poderão ocorrer regressões;
Para utilizar as novas funcionalidades do SQL Server 2014 e não habilitar o novo CE, deixe o compatibility level mais alto no banco de dados e habilite a trace flag 9481 no nível do servidor;
O que fazer no caso de uma regressão?
Manter o novo CE e realizar a análise das consultas que tiveram problemas;
Manter o novo CE e utilizar a trace flag 9481 na consultas que tiveram problemas;
Retornar ao antigo nível de compatibilidade e utilizar a trace flag 2312 para as consultas que tiveram um aumento de performance com o novo CE;
Retornar ao antigo CE no caso de uma grande queda de performance em consultas críticas ao sistema;
OBRIGADO!
Links
http://vfava.wordpress.com
https://groups.google.com/d/forum/sqlmaniacs
http://facebook.com/devSQL