Interpretando planos de execução de consultas T-SQL

Interpretando planos de execução de consultas T-SQL

Interpretando planos de execução de consultas T-SQL 1
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Neste artigo, analisaremos um plano simples de execução de consultas T-SQL com diferentes aspectos. Isso nos ajudará a melhorar nossas habilidades práticas em vez de discutir o conhecimento teórico.

O plano de execução é um ponto muito significativo para descobrir o que está acontecendo por trás do processo de execução da consulta. Por esse motivo, se queremos melhorar o desempenho de uma consulta na qual temos um desempenho ruim, precisamos entender claramente o que este guia nos diz. De fato, os planos de execução são uma saída do otimizador de consultas, portanto, tentaremos entender como o otimizador de consultas se comporta. Primeiro, vamos examinar alguns dos principais conceitos que serão usados ​​neste artigo.

O que é o Transact-SQL (T-SQL)?

SQL significa Linguagem de Consulta Estruturada e foi projetado para consultar o sistema de banco de dados e também é compatível com o padrão ANSI. Contudo, Transact-SQL (T-SQL) é uma forma avançada da linguagem SQL usada principalmente para consultar bancos de dados Microsoft SQL.

O que é um otimizador de consulta?

o otimizador de consultas é um componente muito crucial do mecanismo de banco de dados que analisa consultas e tenta gerar planos de execução eficazes (ideais). Toda consulta executada consumirá recursos, como E / S, CPU, Memória. O otimizador calcula o custo estimado desses recursos e escolhe um plano de consulta ideal com base nesse cálculo.

O que é um plano de execução?

O plano de execução pode ser pensado como um gravador de dados de voo que nos informa os detalhes da execução de uma consulta. o execução real plano gerado após uma consulta inclui informações detalhadas sobre as métricas de tempo de execução. Por outro lado, um otimizador de consulta pode criar um plano de consulta com base no método de estimativa, e esse plano de consulta é chamado de Execução estimada Plano.

Como exibir um plano de execução?

Podemos ativar o Plano de execução real de uma consulta na barra de ferramentas do SQL Server Management Studio (SSMS). Só precisamos clicar no Incluir plano de execução real , como mostrado abaixo, ou podemos pressionar o Ctrl + M combinação de teclas ao mesmo tempo.

Leia Também  Introdução ao tipo de dados XML no SQL Server

Habilitar o plano de execução real no SSMS

Após a execução da consulta, podemos ver o plano de execução real.

Plano de consulta de uma consulta Transact-SQL.

Pré-requisitos

Neste artigo, usaremos uma tabela muito simples que pode ser criada através da seguinte consulta T-SQL.

Gorjeta: Chave primária restrição é uma coluna ou conjunto de colunas que identificam exclusivamente as linhas na tabela. Quando criamos uma restrição de chave primária na tabela, o índice em cluster será criado automaticamente para a tabela sublinhada se não a especificarmos explicitamente como um índice não clusterizado exclusivo. Você pode consultar o artigo Aprenda SQL: Chave Primária para obter mais detalhes sobre a chave primária.

Eu usei o ApexSQL Generate para gerar dados de teste de 1 milhão para esta tabela e leva apenas 2,27 segundos.

Gerar teste de 1 milhão para usar o ApexSQL Generate

O que é a busca de índice clusterizado e os operadores de varredura de índice clusterizado?

Um índice clusterizado armazena dados das linhas ’em uma estrutura lógica classificada, por isso é uma maneira muito eficiente de ler as linhas usando a estrutura de índice clusterizado. Esse método de acesso a dados é chamado de busca de índice em cluster. Por exemplo, a consulta a seguir identifica as linhas qualificadas usando o operador de busca de índice em cluster.

O índice clusterizado busca detalhes do operador de uma consulta Transact-SQL.

Quando passamos o mouse sobre o operador de busca de índice em cluster, aparecerá um formulário pop-up que envolve informações detalhadas sobre o operador de busca de índice. Nesta forma, o Número de linhas lidas A propriedade indica o número total de linhas que são lidas pelo operador.

Propriedade Número de linhas de leitura do operador de busca de índice em cluster.

Particularmente para essa consulta T-SQL, o valor da propriedade “Número de linhas lidas” e o valor da propriedade “Número real de linhas” são iguais, portanto, o operador de busca do índice não realizou leituras desnecessárias. Como resultado, podemos dizer que o operador de busca de índice clusterizado é uma maneira eficiente de ler as linhas.

Por outro lado, o otimizador de consulta lê todas as páginas de índice em cluster para encontrar as linhas apropriadas, e essa operação é chamada de varredura de índice em cluster. Agora vamos executar a seguinte consulta e analisar o plano de execução.

Leia Também  Noções básicas sobre mascaramento dinâmico de dados no SQL Server

Detalhes do operador de varredura de índice em cluster de uma consulta Transact-SQL.

Quando observamos o valor do atributo Number of Rows Read do operador de varredura de índice em cluster, veremos que esse operador lê 1 milhão de linhas, o que significa que lê todas as linhas das tabelas, mas transfere apenas 62000 linhas qualificadas para o próximo operador.

O número de propriedades de leitura de linhas do operador de varredura de índice em cluster.

Como resultado, podemos dizer que o operador de varredura de índice em cluster toca todas as linhas da tabela.

O que é um plano de execução trivial?

O otimizador de consultas decide criar um plano de execução trivial quando uma consulta é bastante simples, para evitar o tempo gasto para descobrir um plano de execução eficaz. Quando analisamos novamente o plano de consulta acima, o otimizador gerou um plano de execução trivial. o Nível de otimização O valor do atributo mostra claramente essa situação.

Detalhes do plano de execução trivial de uma consulta Transact-SQL

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

De fato, o otimizador de consulta tem um motivo válido para criar um plano de consulta trivial porque ele possui apenas uma opção para acessar os dados do TestTable. No entanto, um plano trivial não oferece nenhuma recomendação de índice ausente porque ignora algumas das fases de otimização de consulta. Bandeira de rastreamento 8757 pode ser usado para eliminar o plano de execução trivial e forçar o otimizador a concluir o ciclo completo do processo de otimização de consulta. Agora usaremos o sinalizador de rastreamento 8757 em nossa consulta T-SQL de exemplo.

Detalhes do índice ausentes e plano de execução trivial

O que é a estimativa de cardinalidade?

O otimizador de consulta usa estatísticas para estimar quantas linhas são retornadas de cada operador do plano de consulta. De acordo com essa estimativa, o otimizador calcula o custo total da consulta T-SQL (CPU, E / S, Memória). Nesse contexto, as estimativas mais precisas ajudam a obter planos de consulta eficazes para o otimizador. o CardinalityEstimationModelVersion indica a versão do estimador de cardinalidade. Para o nosso exemplo, o valor CardinalityEstimationModelVersion é 140 e mostra que estamos executando a consulta no SQL Server 2017.

Propriedade do CardinalityEstimationModelVersion

Qual é o plano de execução paralelo?

O otimizador de consulta pode processar uma consulta que exija carga de trabalho excessiva em threads discretos. Nesta abordagem, a idéia principal é manipular mais linhas em tempo unitário, para reduzir o tempo de execução da consulta. Para nosso exemplo da consulta T-SQL, o otimizador de consulta cria um plano de execução serial, mas podemos forçá-lo a criar um plano de execução paralelo com a ajuda do ENABLE_PARALLEL_PLAN_PREFERENCE dica.

Leia Também  Solucionar problemas de fuso horário, GMT e UTC usando o banco de dados T-SQL Toolbox

Como usar a dica ENABLE_PARALLEL_PLAN_PREFERENCE

Na imagem acima, podemos ver quantas linhas foram lidas por cada thread. o Reunir Stream O operador mescla todas essas entradas múltiplas de encadeamento na saída única.

Operador Gather Stream

Encontre mais segredos sobre planos de execução

Como afirmamos, o plano de execução inclui muitas informações sobre a execução da consulta nos bastidores. Quando observamos o operador de seleção da consulta de amostra, veremos um OptimizerStatsUsage propriedade. Quando expandimos, podemos ver quais estatísticas são usadas pelo otimizador.

Os detalhes da propriedade OptimizerStatsUsage.

o Última atualização A propriedade mostra quando as estatísticas foram atualizadas pela última vez e SamplingPercent indica quantos por cento de linhas de dados estão usando para criar histogramas. Quando atualizamos as estatísticas com o FULLSCAN opção, ele calcula as estatísticas pesquisando todas as linhas da tabela.

Agora, executaremos a consulta de amostra novamente e analisaremos o plano de execução.

Noções básicas sobre os detalhes SamplingPercent do plano de execução.

Como podemos ver, o SamplingPercent O valor do atributo foi alterado após a atualização das estatísticas.

Conclusão

Neste artigo, discutimos os detalhes do plano de execução de uma consulta T-SQL muito simples. Compreender o plano de execução de uma consulta é o ponto de partida para resolver os problemas de desempenho das consultas. Por esse motivo, neste artigo, interpretamos um plano de execução de consulta muito simples para melhorar a experiência prática. Vamos relembrar rapidamente o que aprendemos abaixo neste artigo:

  • Procura de índice em cluster e operadores de varredura de índice em cluster

  • Planos de execução triviais

  • Planos de execução paralela

  • Estimativa de cardinalidade

  • Como descobrir quais estatísticas foram usadas durante a execução da consulta

Esat Erkec
Últimas mensagens de Esat Erkec (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br