Estimativa de cardinalidade (SQL Server)

A lógica de estimativa de cardinalidade, chamada de avaliador de cardinalidade, é recriada no SQL Server 2014 para melhorar a qualidade dos planos de consulta e, portanto, melhorar o desempenho da consulta. O novo avaliador de cardinalidade incorpora suposições e algoritmos que funcionam bem em cargas de trabalho modernas de OLTP e data warehousing. Ele se baseia em pesquisas detalhadas de estimativa de cardinalidade sobre cargas de trabalho modernas e nossos aprendizados nos últimos 15 anos de melhoria do avaliador de cardinalidade do SQL Server. Comentários de clientes mostram que, embora a maioria das consultas se beneficie da alteração ou permaneça inalterada, um pequeno número pode mostrar regressões em comparação com o estimador de cardinalidade anterior.

Recomendações de teste de desempenho e ajuste

O novo avaliador de cardinalidade está habilitado para todos os novos bancos de dados criados no SQL Server 2014. No entanto, a atualização para o SQL Server 2014 não habilita o novo avaliador de cardinalidade em bancos de dados existentes.

Para garantir o melhor desempenho de consulta, use essas recomendações para testar sua carga de trabalho com o novo avaliador de cardinalidade antes de habilitá-la em seu sistema de produção.

1. Atualize todos os bancos de dados existentes para usar o novo avaliador de cardinalidade. Para fazer isso, use ALTER DATABASE Nível de Compatibilidade (Transact-SQL) para definir o nível de compatibilidade do banco de dados como 120.

2. Execute sua carga de trabalho de teste com o novo avaliador de cardinalidade e, em seguida, solucione novos problemas de desempenho da mesma maneira que você atualmente soluciona problemas de desempenho.

3. Depois que a carga de trabalho estiver em execução com o novo avaliador de cardinalidade (nível de compatibilidade do banco de dados 120 (SQL Server 2014)) e uma consulta específica tiver regredido, você poderá executar a consulta com o sinalizador de rastreamento 9481 para usar a versão do avaliador de cardinalidade usada no SQL Server 2012 e anterior. Para executar uma consulta com uma bandeira de rastreamento, consulte o artigo KB Habilitar o comportamento do otimizador de consulta do SQL Server que pode ser controlado por diferentes bandeiras de rastreamento no nível de consulta específica.

4. Se você não puder alterar todos os bancos de dados ao mesmo tempo para usar o novo avaliador de cardinalidade, poderá usar o antigo avaliador de cardinalidade para todos os bancos de dados usando ALTER DATABASE Nível de Compatibilidade (Transact-SQL) para definir o nível de compatibilidade do banco de dados como 110.

5. Se a carga de trabalho estiver em execução com o nível de compatibilidade do banco de dados 110 e você quiser testar ou executar uma consulta específica com o novo avaliador de cardinalidade, você poderá executar a consulta com o sinalizador de rastreamento 2312 para usar a versão do SQL Server 2014 do avaliador de cardinalidade. Para executar uma consulta com um sinalizador de rastreamento, consulte o artigo KB Ativar os comportamentos do otimizador de consulta do SQL Server que podem ser controlados por diferentes sinalizadores de rastreamento em um nível específico de consulta.

Novos XEvents

Há dois novos query_optimizer_estimate_cardinality XEvents para dar suporte aos novos planos de consulta.

• query_optimizer_estimate_cardinality ocorre quando o otimizador de consulta estima a cardinalidade em uma expressão relacional.

• query_optimizer_force_both_cardinality_estimation_behaviors ocorre quando as traceflags 2312 e 9481 estão habilitadas, tentando forçar o comportamento de estimativa de cardinalidade antigo e novo ao mesmo tempo.

Exemplos

Os exemplos a seguir mostram algumas das alterações nas novas estimativas de cardinalidade. O código para estimar a cardinalidade foi reescrito. A lógica é complexa e não é possível fornecer uma lista completa de todas as alterações.

Exemplo A. Novas estimativas de cardinalidade usam uma cardinalidade média para dados crescentes adicionados recentemente

Este exemplo demonstra como o novo avaliador de cardinalidade pode melhorar as estimativas de cardinalidade para dados crescentes que excedem o valor máximo na tabela durante a atualização de estatísticas mais recente.

SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';  

Neste exemplo, novas linhas são adicionadas à tabela Vendas todos os dias, a consulta solicita vendas que ocorreram em 19/12/2013 e as estatísticas foram atualizadas pela última vez em 18/12/2013. O avaliador de cardinalidade anterior pressupõe que os valores de 19/12/2013 não existem, pois a data excede a data máxima e as estatísticas não foram atualizadas para incluir os valores de 19/12/2013. Essa situação, conhecida como o problema de chave crescente, ocorrerá se você carregar dados durante o dia e, em seguida, executar consultas nos dados antes que as estatísticas sejam atualizadas.

Esse comportamento foi alterado. Agora, mesmo que as estatísticas não tenham sido atualizadas para os dados crescentes mais recentes adicionados desde a última atualização de estatísticas, o novo avaliador de cardinalidade pressupõe que os valores existem e usa a cardinalidade média para cada valor na coluna como a estimativa de cardinalidade.

Exemplo B. Novas estimativas de cardinalidade pressupõem que predicados filtrados na mesma tabela tenham alguma correlação

Para este exemplo, suponha que a tabela Carros tenha 1000 linhas, a coluna Marca tenha 200 ocorrências de 'Honda', a coluna Modelo tenha 50 ocorrências de 'Civic', onde todos os Civics são Hondas. Portanto, 20% dos valores na coluna Make são 'Honda', 5% dos valores na coluna Modelo são 'Civic', e o número real de Honda Civics é 50. As estimativas de cardinalidade anteriores pressupõem que os valores nas colunas Make e Model são independentes uns dos outros. O otimizador de consulta anterior estima que há 10 Honda Civics (.05 * .20 * 1000 linhas = 10 linhas).

SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';  

Esse comportamento foi alterado. Agora, as novas estimativas de cardinalidade pressupõem que as colunas Make e Model tenham alguma correlação. O otimizador de consulta estima uma cardinalidade maior adicionando um componente exponencial à equação de estimativa. O otimizador de consulta agora estima que 22,36 linhas (.05 * SQRT(.20) * 1000 linhas = 22,36 linhas ) correspondem ao predicado. Para esse cenário e distribuição de dados específica, 22,36 linhas estão mais próximas das 50 linhas reais que a consulta retornará.

Observação, a nova lógica do estimador de cardinalidade classifica as seletividades de predicado e aumenta o expoente. Por exemplo, se as seletividades de predicado fossem .05, .20 e .25, a estimativa de cardinalidade seria (.05 * SQRT(.20) * SQRT(SQRT(.25)) ).

Exemplo C. Novas estimativas de cardinalidade pressupõem que predicados filtrados em tabelas diferentes são independentes

Para este exemplo, o avaliador de cardinalidade anterior pressupõe que os filtros de predicado s.type e r.date estão correlacionados. No entanto, os resultados do teste em cargas de trabalho modernas mostraram que os filtros de predicado em colunas em tabelas diferentes geralmente não estão correlacionados entre si.

SELECT s.ticket, s.customer, r.store FROM dbo.Sales AS s CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND s.type = 'toy' AND r.date = '2013-12-19';  

Esse comportamento foi alterado. Agora, a nova lógica do avaliador de cardinalidade pressupõe que s.type não está correlacionado com r.date. Em termos práticos, a suposição é que os brinquedos são retornados todos os dias e não apenas em um dia específico. Nesse caso, as novas estimativas de cardinalidade serão um número menor do que as estimativas de cardinalidade anteriores.

Consulte Também

Monitorar e ajustar para de desempenho