Um guia para processamento de consulta de tabelas com otimização de memória

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

O OLTP in-memory introduz tabelas com otimização de memória e procedimentos armazenados compilados nativamente no SQL Server. Este artigo fornece uma visão geral do processamento de consulta para tabelas com otimização de memória e procedimentos armazenados compilados nativamente.

O documento explica como as consultas em tabelas com otimização de memória são compiladas e executadas, incluindo:

• O fluxo de processamento de consultas no SQL Server para tabelas baseadas em disco.

• Otimização de consulta; a função de estatísticas sobre tabelas com otimização de memória, bem como diretrizes para solucionar problemas de planos de consulta incorretos.

• O uso do Transact-SQL interpretado para acessar tabelas com otimização de memória.

• Considerações sobre a otimização de consultas para acesso a tabelas com otimização de memória.

• Compilação e processamento do procedimento armazenado compilado nativamente.

• Estatísticas que são usadas para estimativa de custo pelo otimizador.

• Modos de corrigir planos de consulta incorretos.

Consulta de exemplo

O exemplo a seguir será usado para ilustrar conceitos de processamento de consulta discutidos neste artigo.

Vamos considerar duas tabelas, Customer e Order. O script Transact-SQL a seguir contém as definições dessas duas tabelas e os índices associados, no seu formato baseado em disco (tradicional):

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

Para construir os planos de consulta mostrados neste artigo, as duas tabelas foram preenchidas com dados de exemplo do banco de dados de exemplo Northwind, que você pode baixar em Bancos de dados de exemplo Northwind e pubs para SQL Server 2000.

Considere a consulta a seguir, que une as tabelas Customer e Order e retorna a ID da ordem e as informações de cliente associadas:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

O plano de execução estimado, conforme exibido pelo SQL Server Management Studio, é como mostrado a seguir.

Plano de consulta para a junção de tabelas com base em disco.

Sobre este plano de consulta:

• As linhas da tabela Customer são recuperadas do índice clusterizado, que é a estrutura de dados primária e tem os dados de tabela completos.

• Os dados da tabela Order são recuperados usando o índice não clusterizado na coluna CustomerID. Esse índice contém a coluna CustomerID, que é usada para a junção, e a coluna de chave primária OrderID, que é retornada ao usuário. O retorno de colunas adicionais da tabela Order exigiria pesquisas no índice clusterizado da tabela Order.

• O operador lógico Inner Join é implementado pelo operador físico Merge Join. Os outros tipos de junção física são Nested Loops e Hash Join. O operador Merge Join aproveita o fato de que ambos os índices são classificados na coluna de junção CustomerID.

Considere uma ligeira variação nessa consulta, que retorna todas as colunas da tabela Order, não apenas a coluna OrderID:

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

O plano estimado para essa consulta é:

Plano de consulta para uma junção por hash de tabelas baseadas em disco.

Nessa consulta, as linhas da tabela Order são recuperadas usando o índice clusterizado. O operador físico Hash Match agora é usado para Inner Join. O índice clusterizado em Order não é classificado em CustomerID e, portanto, Merge Join exigiria um operador de classificação, o que afetaria o desempenho. Observe o custo relativo do operador Hash Match (75%) comparado com o custo do operador Merge Join no exemplo anterior (46%). O otimizador consideraria o operador Hash Match também no exemplo anterior, mas concluiu que o operador Merge Join forneceu melhor desempenho.

Processamento de consulta do SQL Server para tabelas baseadas em disco

O diagrama a seguir descreve o fluxo de processamento de consulta no SQL Server para consultas ad hoc:

Pipeline de processamento de consultas do SQL Server.

Neste cenário:

1. O usuário emite uma consulta.

2. O analisador e o algebrista constroem uma árvore de consulta com operadores lógicos de acordo com o texto do Transact-SQL enviado pelo usuário.

3. O otimizador cria um plano de consulta otimizado que contém operadores físicos (por exemplo, junção de loops aninhados). Depois da otimização, o plano pode ser armazenado no cache do plano. Essa etapa será ignorada se o cache do plano já contiver um plano para essa consulta.

4. O mecanismo de execução da consulta processa uma interpretação do plano da consulta.

5. Para cada busca de índice, verificação de índice e operador de verificação de tabela, o mecanismo de execução solicita linhas das respectivas estruturas de índice e tabela dos Métodos de Acesso.

6. Os Métodos de Acesso recuperam as linhas das páginas de índice e de dados no pool de buffers e carregam as páginas do disco para o pool de buffers conforme necessário.

Para a primeira consulta de exemplo, o mecanismo de execução solicita dos Métodos de Acesso as linhas no índice clusterizado em Customer e no índice não clusterizado em Order. Os Métodos de Acesso passam pelas estruturas de índice da árvore B para recuperar as linhas solicitadas. Nesse caso, todas as linhas são recuperadas, pois o plano exige varreduras completas de índice.

Acesso via Transact-SQL interpretado a tabelas com otimização de memória

Lotes ad hoc do Transact-SQL e procedimentos armazenados também são conhecidos como Transact-SQL interpretados. Interpretado se refere ao fato de que o plano de consulta é interpretado pelo mecanismo de execução da consulta para cada operador do plano de consulta. O mecanismo de execução lê o operador e seus parâmetros e executa a operação.

O Transact-SQL interpretado pode ser usado para acessar tabelas com otimização de memória e baseadas em disco. A figura a seguir ilustra o processamento de consulta para acesso do Transact-SQL interpretado a tabelas com otimização de memória:

Fluxo de processamento de consultas para o acesso via Transact-SQL interpretado a tabelas otimizadas para memória.

Conforme ilustrado pela figura, o pipeline de processamento de consultas permanece em grande parte inalterado:

• O analisador e o algebrista constroem a árvore de consulta.

• O otimizador cria o plano de execução.

• O mecanismo de execução de consultas interpreta o plano de execução.

A principal diferença entre o pipeline tradicional de processamento de consultas (figura 2) é que as linhas das tabelas otimizadas para memória não são recuperadas do pool de buffers por meio de Métodos de Acesso. Em vez disso, as linhas são recuperadas das estruturas de dados na memória por meio do mecanismo OLTP In-Memory. As diferenças nas estruturas de dados fazem com que o otimizador escolha planos diferentes em alguns casos, conforme ilustrado pelo exemplo a seguir.

O script Transact-SQL a seguir contém versões com otimização de memória das tabelas Order e Customer, usando índices de hash:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Considere a mesma consulta executada em tabelas com otimização de memória:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

O plano estimado é o seguinte:

Plano de consulta para a junção de tabelas com otimização de memória.

Observe as seguintes diferenças em relação ao plano para a mesma consulta em tabelas baseadas em disco (figura 1):

• Esse plano contém uma verificação de tabela em vez de uma verificação de índice clusterizado para a tabela Customer:

  • A definição da tabela não contém um índice clusterizado.

  • Os índices clusterizados não têm suporte nas tabelas com otimização de memória. Em vez disso, cada tabela com otimização de memória deve ter pelo menos um índice não clusterizado e todos os índices nas tabelas com otimização de memória podem acessar com eficiência todas as colunas da tabela sem ter que armazená-las no índice ou consultar um índice clusterizado.

• Esse plano contém Hash Match em vez de Merge Join. Os índices em ambas as tabelas, Order e Customer, são índices hash e, portanto, não têm ordenação. Um Merge Join exigiria os operadores de classificação que diminuiriam o desempenho.

Procedimentos armazenados compilados nativamente

Os procedimentos armazenados compilados nativamente são procedimentos armazenados do Transact-SQL compilados para código de máquina, não sendo interpretados pela mecanismo de execução de consulta. O script a seguir cria um procedimento armazenado originalmente compilado que executa a consulta de exemplo (na seção Consulta de exemplo).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

Os procedimentos armazenados compilados nativamente são compilados no momento da criação, enquanto os procedimentos armazenados interpretados são compilados no momento da primeira execução. (Uma parte da compilação, particularmente análise e algebrização, ocorrem na criação. No entanto, para procedimentos armazenados interpretados, a otimização dos planos de consulta ocorre na primeira execução.) A lógica de recompilação é semelhante. Os procedimentos armazenados compilados nativamente são recompilados na primeira execução do procedimento se o servidor for reiniciado. Os procedimentos armazenados interpretados serão recompilados se o plano não estiver mais no cache de planos. A tabela a seguir resume os casos de compilação e recompilação tanto para procedimentos armazenados compilados nativamente quanto para procedimentos armazenados interpretados:

Processamento de compilação e consulta

O diagrama a seguir ilustra o processo de compilação para procedimentos armazenados compilados nativamente:

Compilação nativa dos procedimentos armazenados.

O processo é descrito como:

1. O usuário emite uma CREATE PROCEDURE instrução para SQL Server.

2. O analisador e o algebrista criam o fluxo de processamento para o procedimento, além das árvores de consulta para as consultas do Transact-SQL no procedimento armazenado.

3. O otimizador cria planos otimizados de execução de consulta para todas as consultas no procedimento armazenado.

4. O compilador OLTP na memória usa o fluxo de processamento com os planos de consulta otimizados inseridos e gera uma DLL que contém o código de máquina para execução do procedimento armazenado.

5. A DLL gerado é carregada na memória.

A invocação de um procedimento armazenado compilado nativamente se traduz em chamar uma função na DLL.

Execução de procedimentos armazenados compilados nativamente.

A invocação de um procedimento armazenado originalmente compilado é descrita a seguir:

1. O usuário emite uma instrução EXECusp_myproc.

2. O analisador sintático extrai o nome e os parâmetros do procedimento armazenado.

   Se a instrução tiver sido preparada, por exemplo, usando sp_prep_exec, o analisador não precisará extrair os parâmetros e o nome do procedimento no momento da execução.

3. O ambiente de execução do OLTP em memória localiza o ponto de entrada da DLL do procedimento armazenado.

4. O código de máquina no DLL é executado e os resultados são retornados para o cliente.

Detecção de parâmetro

Os procedimentos armazenados do Transact-SQL interpretado são compilados na primeira execução, em oposição aos procedimentos armazenados compilados nativamente, que são compilados no momento da criação. Quando os procedimentos armazenados interpretados são compilados na invocação, os valores dos parâmetros fornecidos para essa invocação são usados pelo otimizador durante a geração do plano de execução. Esse uso de parâmetros durante a compilação é chamado de detecção de parâmetro.

A detecção de parâmetro não é usada para compilar os procedimentos armazenados compilados nativamente. Todos os parâmetros para o procedimento armazenado são considerados como tendo valores UNKNOWN. Assim como os procedimentos armazenados interpretados, os procedimentos armazenados compilados nativamente também oferecem suporte à dica OPTIMIZE FOR. Para obter mais informações, veja Dicas de consulta (Transact-SQL).

Recuperando um plano de execução de consulta para procedimentos armazenados compilados de forma nativa

O plano de execução de consulta para um procedimento armazenado compilado nativamente pode ser recuperado usando o Plano de Execução Estimado no Management Studio ou usando a opção SHOWPLAN_XML em Transact-SQL. Por exemplo:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

O plano de execução gerado pelo otimizador de consulta consiste em uma árvore com operadores de consulta nos nós e nas folhas da árvore. A estrutura da árvore determina a interação (o fluxo de linhas de um operador para outro) entre os operadores. Na exibição gráfica do SQL Server Management Studio, o fluxo é da direita para a esquerda. Por exemplo, o plano de consulta na figura 1 contém dois operadores de varredura de índice, que fornecem linhas para um operador de junção por mesclagem. O operador merge join fornece linhas a um operador de seleção. O operador de seleção, por fim, retorna as linhas ao cliente.

Operadores de consulta em procedimentos armazenados compilados nativamente

A tabela a seguir resume os operadores de consulta com suporte em procedimentos armazenados compilados nativamente:

Estatísticas de coluna e junções

O SQL Server mantém estatísticas sobre os valores das colunas-chave do índice para ajudar a estimar o custo de determinadas operações, como varreduras de índice e operações de busca em índice. (O SQL Server também cria estatísticas em colunas de chave não indexadas se você as cria explicitamente ou se o otimizador de consulta as cria em resposta a uma consulta com um predicado.) A principal métrica na estimativa de custo é o número de linhas processadas por um único operador. Observe que para tabelas baseadas em disco, o número de páginas acessadas por um operador específico é significativo na estimativa de custo. No entanto, como a contagem de páginas não é importante para tabelas com otimização de memória (sempre será zero), este documento se concentra na contagem de linhas. A estimativa começa com os operadores de busca e varredura de índice no plano e depois é expandida para incluir os demais operadores, como o operador de junção. O número estimado de linhas a serem processadas por um operador de junção é baseado na estimativa dos operadores subjacentes de índice, busca e verificação. Para o acesso interpretado via Transact-SQL a tabelas otimizadas para memória, você pode observar o plano de execução real para ver a diferença entre os números de linhas estimados e reais dos operadores do plano.

Para o exemplo na figura 1:

• A verificação de índice clusterizado em Customer estimou 91; real 91.
• A verificação de índice não clusterizado em CustomerID estimou 830; real 830.
• O operador Merge Join estimou 815; real 830.

As estimativas para as verificações de índice são precisas. O SQL Server mantém a contagem de linhas para tabelas baseadas em disco. As estimativas para varreduras completas de tabela e varreduras de índice são sempre precisas. A estimativa da junção também é bastante precisa.

Se essas estimativas forem alteradas, as considerações de custo para diferentes alternativas de plano também serão alteradas. Por exemplo, se um dos lados da junção tiver uma contagem de linhas estimada de 1 ou apenas algumas linhas, usar as junções de loops aninhados é menos dispendioso. Considere a consulta a seguir:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Depois de excluir todas as linhas, exceto uma na tabela Customer, o plano de consulta a seguir é gerado:

Em relação a esse plano de consulta:

• O Hash Match foi substituído por um operador de junção físico Nested Loops.
• A varredura completa do índice em IX_CustomerID foi substituída por uma busca no índice. Isso resultou na verificação de 5 linhas, em vez das 830 linhas exigidas para a verificação de índice completo.

Confira também

Tabelas com otimização de memória