Na primeira parte, vimos pontos como pedido, tarefas, workers, otimização e execução. Aqui, dando continuidade, serão abordados resultados, consulta à memória em execução, organização de dados e acesso aos dados.

***

Resultados

Os resultados são retornados de volta para o programa cliente conforme a execução prossegue. Como linhas em uma lista de execução, a parte superior do operador é geralmente encarregada de escrevê-las em buffers de rede e enviá-las de volta para o cliente. O resultado não é criado pela primeira vez em algum armazenamento intermediário (memória ou disco) e depois enviado de volta para o cliente, em vez disso, é enviado de volta do jeito que está sendo criado (da forma que a consulta é executada).

Enviar o resultado de volta para o cliente é uma ação feita sob o protocolo de controle de fluxo de rede. Se o cliente não está consumindo ativamente o resultado (por exemplo, chamando SQLDataReader.Read ()), eventualmente o controle do fluxo terá que bloquear a consulta que está sendo executada) e esta, por sua vez, suspende a execução da consulta. A consulta é retomada e produz mais resultados (continua repetindo o plano de execução), assim que o controle de fluxo de rede alivia os recursos de rede necessários.

Um caso interessante e o dos parâmetros de saída associados ao pedido. Para retornar o valor de saída de volta para o cliente, o valor tem de ser inserido no fluxo de dados de rede que é enviado a partir da execução da consulta de volta para o cliente. O valor só pode ser escrito de volta para o cliente no final da execução, como o pedido termina. É por isso que os valores dos parâmetros de saída só podem ser verificados após todos os resultados serem consumidos.

Consulta à memória em execução

Alguns operadores precisam de quantidades significativas de memória para fazer seu trabalho. Ordenar um operador é necessário para armazenar toda a entrada, a fim de resolver o problema. Hash e agregados de hash têm que construir grandes tabelas de hash para concluírem seu trabalho. O plano de execução sabe de forma bruta quanto de memória será necessário para esses operadores, com base no tipo de operador, no número estimado de linhas que o operador tem de processar (a partir das estatísticas) e nos tamanhos das colunas. A quantidade total de memória exigida por um plano de execução para esses operadores é muitas vezes referida como concessão de memória.

Quando muitas consultas simultâneas são lançadas em um plano de execução, este pode ficar sem memória por causa do elevado número de operadores solicitando grandes concessões de memória ao mesmo tempo. Para evitar essa situação, o SQL Server utiliza um recurso chamado “semáforo”. Isso assegura ao servidor que a soma de todas as concessões de memória atribuídas às consultas em execução não exceda a memória total do servidor. Quando a soma das concessões de memória está esgotando a memória disponível no servidor, as consultas que solicitam mais recursos terão que esperar outras consultas que estão sendo executadas concluírem suas tarefas e liberarem os recursos que estão ocupando. O status atual de concessões de memória (solicitada, alocada etc.) pode ser consultado a partir da DMV sys.dm_exec_query_memory_grants. Quando uma consulta precisa esperar por uma concessão de memória, outro evento é gerado – os Avisos de Execução em Classe de evento:

Os Avisos de Execução em Classe de evento indicam advertências relacionadas à concessão de memória que ocorreu durante a execução de uma instrução SQL Server ou procedimento armazenado. Essa classe de evento pode ser monitorada para determinar se as consultas tinham que esperar pelo menos um segundo antes de prosseguir para a solicitação de memória, ou se a primeira tentativa de obter memória falhou.

Como sempre, as coisas são um pouco mais complexas quando nos aprofundamos nos detalhes. As consultas não são necessariamente concedidas de acordo com a quantidade de memória total solicitada. Elas podem ser lançadas em execução com uma reserva de memória menor do que o solicitado, e isso é bom. Os operadores são informados de que uma menor quantidade de memória foi concedida, e então eles devem adaptar sua execução de acordo com a concessão de memória alocada.

Outra situação que pode ocorrer é a estimativa da memória se tornar insuficiente (geralmente devido a estatísticas antigas do servidor), caso em que o operador é obrigado a liberar recursos da tempdb (tabela temporária). Gerar memória no disco é um processo lento e há eventos de aviso especiais para eles:

• Classe de evento Exchange Spill
• Classe de evento de classificação de avisos
• Classe de evento de aviso de Hash. Rigorosamente falando, as coisas realmente são ainda mais complexas para hashes, para mais detalhes, veja o material Aviso de hash de eventos do SQL Profiler.

A memória concedida a uma consulta é uma reserva, não uma atribuição. Durante a execução da consulta, são solicitadas alocações de memória reais a partir dessa concessão reservada, e isso é feito quando a memória é realmente consumida pela consulta. É possível para uma consulta consumir (alocar) menos memória do que concedeu (as estimativas são geralmente pessimistas e baseadas em casos mais graves).

A memória que foi concedida, mas não foi consumida é usada para cache de dados (buffer pool). No entanto, grandes quantidades de recursos que não são utilizados têm um efeito negativo, porque bloqueiam outras consultas até mesmo de iniciarem sua execução por causa do limite do semáforo de recursos.

Um conceito relacionado é o semáforo de recursos de compilação de consulta. Ele é semelhante ao plano de execução, mas aplica-se basicamente a consultas de compilação, não a consultas de execução. Normalmente, isso nunca deve ser um problema, pois as compilações ocorrem raramente (ou pelo menos deveriam ocorrer). Um grande número de solicitações bloqueadas antes da compilação da consulta indica um problema com a reutilização do plano de consulta – consulte Diagnóstico de problemas do Plano de Cache relacionados com o desempenho e soluções sugeridas.

Uma última nota sobre as concessões de memória: lembre-se de que nem todas as pesquisas exigem uma concessão de memória. Concessões de memória são necessárias apenas para consultas complexas que envolvem triagem, grandes escaneamentos de dados (paralelismo) e hash ou agregados. Se você tiver problemas de concessão de memória em sistemas que deverão ter baixa latência (como um website), então é hora de repensar seu projeto de modelo de dados. Concessões de memória para consultas são válidas em cenários analíticos (em consultas grandes onde a alta latência é esperada e tolerada).

Organização de dados

Neste momento, sinto que é necessário introduzir uma explicação sobre a forma como os dados são organizados no SQL Server, porque a compreensão do tema de acesso a dados depende disso. Os dados no SQL Server podem ser organizados de três maneiras:

Heaps

Heap é a tabela com ou sem qualquer ordem definida e que contém todas as colunas da tabela. Se uma tabela é organizada como heap, então sempre que nos referirmos à “tabela”, a heap também será “tabela”. Caso não seja declarada uma cláusula PRIMARY KEY na criação da tabela, seus dados serão heap também. A tabela criada por uma instrução SELECT … INTO … FROM… será heap. Para mais detalhes sobre como tabelas heap são organizadas, veja Estruturas Heap.

Índices de cluster

Um índice de cluster é uma tabela com uma ordem definida. Contém todas as colunas de uma tabela e, se a tabela for organizada com um índice, sempre que estivermos nos referindo à tabela o índice de cluster é a própria tabela. Índices de cluster são B-Trees. Se for declarada uma chave primária em uma tabela, a mesma chave também será usada para o índice em cluster, a menos que a chave primária seja explicitamente declarada como NONCLUSTERED. Para mais detalhes sobre como índices de cluster são organizados, veja Estruturas de índice de cluster.

Índices não clusterizados ou não agrupados

Um índice não agrupado é uma cópia de um subconjunto de uma tabela de dados com uma ordem específica definida e contém uma ou mais colunas da tabela. Quando se fala de “índices” em uma tabela, na maioria das vezes a discussão se refere a índices não clusterizados. Índices não clusterizados são B-Trees. Para mais detalhes sobre como índices não clusterizados são organizados veja Estruturas de índices não clusterizados.

Com o SQL Server 2012, há um outro modo de organizar um conjunto de dados, ou seja, Nonclustered Columnstores, e na próxima versão do SQL Server haverá também um modo cluster columnstore.

Acesso aos dados

Nas extremidades da árvore de execução, existem operadores que implementam o acesso aos dados. Os operadores retornam uma linha real de dados de uma tabela (ou a partir de um índice) quando o método next() é chamado sobre eles. Existem três operadores de acesso de dados possíveis:

Operador de digitalização

O operador de digitalização irá percorrer todas as linhas na sua fonte. Uma varredura nunca pode localizar uma linha específica, ela sempre executa a varredura de todo o conjunto de dados (daí o seu nome…). Ao inspecionar um plano de execução, é possível encontrar, eventualmente, qualquer um dos operadores cluster Cluster Index Scan, Nonclustered Index Scan, Table Scan, Remote Index Scan e Remote Scan. Eles são operadores distintos porque se aplicam a diferentes fontes de dados (índices, tabelas, servidores remotos etc.), mas todos têm em comum o comportamento end-to-end de digitalização. Como esses operadores têm que ler os dados inteiros, eles são caros. Apenas consultas para armazenamento de dados devem recorrer a esse tipo de varredura.

Operador de busca

O operador de busca pode localizar uma linha diretamente com base em uma chave de busca. Esses operadores podem operar somente em conjuntos de dados B-Tree, por isso só podem ser aplicados a índices clusterizados e não clusterizados. Se um índice possui uma chave complexa (composta por várias colunas), então o operador de busca só irá funcionar se os valores da definição do índice forem fornecidos. Para dar um exemplo, se um índice tem as colunas principais (A, B, C), então o operador de busca pode localizar a primeira fileira, onde A = “a”, ou a primeira fileira, onde A = ‘A’ e B = ‘b’ o,u a primeira linha onde A = ‘A’ e B = ‘b’ e C = ‘c’. No entanto, em tal índice, a busca não pode localizar uma linha onde B = ‘b’ ou uma linha onde C = ‘c’.

O operador de busca também é capaz de implementar intervalos. Dada a mesma definição do índice em (A, B, C), um operador de busca pode interagir com todas as linhas onde A > ‘A’ e A < ‘z’ ou com todas as linhas onde A = ‘A’, B > ‘b’ e B < ‘z’, mas não pode fazer uma iteração de linhas onde B > ‘b’ e B <‘z’. Ao inspecionar um plano de execução, os operadores em índice de cluster ou Índice Remoto de Busca podem ser encontrados. Eles são operadores distintos porque se aplicam a diferentes fontes de dados, mas todos eles têm em comum a capacidade de localizar eficientemente um valor de chave ou repetir de forma eficiente uma determinada faixa de valores de chave. Obviamente, não há operadores de busca para heap, pois heap não tem a capacidade de localizar uma linha de forma eficiente com base em uma chave. A busca através de índices deve ser ser o método de acesso de dados preferido em quase todas as situações.

Operador de pesquisa

Este é um operador especial de acesso a dados que podem localizar eficientemente uma linha baseada em um tipo especial de valor, chamado de marcador. Você não pode fornecer o operador, apenas o mecanismo interno do banco de dados pode recuperar marcadores para consulta posterior. E esta é a essência desse operador especial: ele nunca é o operador de acesso direto para dados primários, mas será sempre o operador usado para procurar uma linha que foi acessada anteriormente em um dos outros operadores, uma digitalização ou uma busca. Os marcadores podem ser consultados em qualquer modo de organização dos dados, tanto em heap quanto em B-Trees. Em um plano de execução, é possível encontrar operadores de busca, operadores de localização de ID ou um valor de pesquisa de chave (B-Tree).

• Operadores de digitalização leem todos os dados.
• Operador de busca apenas os dados necessários (e não todos os dados, por exemplo).
• Heaps suportam apenas análises de dados.
• B-Trees podem realizar buscas apenas se a chave de índice for especificada.

Resumidamente falando, todos os operadores utilizados para inserir, excluir ou atualizar linhas também são operadores de acesso aos dados. O operador de digitalização e os operadores de exclusão de digitalização fazem acesso aos dados para repetir o gatilho de inserção e exclusão nas pseudotabelas. O log de digitalização de linha é um operador de acesso de dados mais abstrato (ele lê as linhas do log, não as tabelas de dados). Mas ir para esse nível de detalhe iria inviabilizar a propósito deste artigo.

Frequentemente você verá o conceito de sincronismo ser mencionado. Isso se refere a um operador que utiliza operadores de busca para localizar rapidamente um registro pela chave de busca e, em seguida, continua fazendo a iteração das linhas a partir dessa posição, muitas vezes até um segundo valor de chave de busca específica. O operador de busca está realizando uma varredura dentro do intervalo definido pelos valores de início e fim, daí o termo “Faixa de digitalização” é usado.

Se olharmos para como a execução da consulta ocorre, agora podemos entender como os operadores de acesso a dados conduzem toda a iteração do plano de consulta. Quando o método next() é chamado na raiz da árvore de comando, ele se infiltra do operador pai para o operador filho até que atinja todos os operadores de acesso aos dados. Esses operadores implementam o método next() por realmente lerem os dados da fonte e retornarem as linhas a serem lidas e lembram-se da posição da linha retornada para iterar com a posterior chamada do método next() que lê a próxima linha e retorna a linha seguinte. Operadores de acesso aos dados não têm mais operadores filhos, eles são acessados pelo plano de consulta.

Operadores superiores são utilizados para implementar recursos como filtragem de linha, união de tabelas, classificação de resultados, computação de agregados, e assim por diante, sobre os dados retornados pelos operadores de acesso.

***

Na próxima e última parte, serão abordados leitura de dados, travas, bloqueios, entre outros tópicos.

***

Artigo traduzido pela Redação iMasters com autorização do autor. Publicado originalmente em http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/