Um guia para o processamento de consultas para tabelas de Memory-Optimized

In-Memory OLTP apresenta 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 consultas 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 pipeline de processamento de consultas no SQL Server para tabelas baseadas em disco.
Otimização de consulta; a função de estatísticas em tabelas com otimização de memória, bem como diretrizes para solução de problemas de planos de consulta incorretos.
O uso de Transact-SQL interpretado para acessar tabelas otimizadas para memória.
Considerações sobre a otimização de consulta para acesso à tabela com otimização de memória.
Compilação e processamento de procedimentos armazenados compilados nativamente.
Estatísticas usadas para estimativa de custo pelo otimizador.
Maneiras de corrigir planos de consulta incorretos.

Consulta de exemplo

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

Consideramos duas tabelas, Cliente e Pedido. O script de Transact-SQL a seguir contém as definições para essas duas tabelas e índices associados, em sua forma (tradicional) baseada em disco:

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 de Northwind e Pubs para SQL Server 2000.

Considere a consulta a seguir, que une as tabelas Cliente e Pedido e retorna a ID do pedido e as informações do 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, é o seguinte

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

Sobre este plano de consulta:

As linhas da tabela Cliente são recuperadas do índice clusterizado, que é a estrutura de dados primária e tem os dados completos da tabela.
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. Retornar colunas adicionais da tabela Order exigiria pesquisas no índice clusterizado para a tabela Order.
O operador Inner Join lógico é implementado pelo operador Merge Joinfísico. Os outros tipos de junção física são Nested Loops e Hash Join. O Merge Join operador aproveita o fato de que ambos os índices são ordenados na coluna de junção chamada CustomerID.

Considere uma pequena variação nessa consulta, que retorna todas as linhas da tabela Order, não apenas 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 de hash de tabelas baseadas em disco.

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

Processamento de consultas do SQL Server para tabelas de Disk-Based

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:

O usuário emite uma consulta.
O analisador e o algebrizer constrói uma árvore de consulta com operadores lógicos com base no texto Transact-SQL enviado pelo usuário.
O otimizador cria um plano de consulta otimizado que contém operadores físicos (por exemplo, junção de loops aninhados). Após a otimização, o plano pode ser armazenado no cache do plano. Essa etapa será ignorada se o cache de planos já contiver um plano para essa consulta.
O mecanismo de execução de consulta processa uma interpretação do plano da consulta.
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.
Os Métodos de Acesso recuperam as linhas do índice e das páginas de dados no buffer pool e carregam páginas do disco no buffer pool quando necessário.

Para a primeira consulta de exemplo, o mecanismo de execução solicita linhas no índice clusterizado no Cliente e no índice não clusterizado em Ordem dos Métodos de Acesso. Os métodos de acesso percorrem as estruturas de índice em B-tree para recuperar as linhas solicitadas. Nesse caso, todas as linhas são recuperadas conforme o plano exige verificações de índice completas.

Acesso interpretado Transact-SQL a tabelas Memory-Optimized

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

Os Transact-SQL interpretados podem ser usados para acessar tabelas com otimização de memória e baseadas em disco. A figura a seguir ilustra o processamento de consulta para acesso Transact-SQL interpretado a tabelas com otimização de memória:

Pipeline de processamento de consulta para acesso Transact-SQL interpretado a tabelas com otimização de memória.

Conforme ilustrado pela figura, o pipeline de processamento de consulta permanece praticamente inalterado:

O analisador e o algebrizer constroem a árvore de consulta.
O otimizador cria o plano de execução.
O motor de execução de consultas interpreta o plano de execução.

A principal diferença com o pipeline de processamento de consulta tradicional (figura 2) é que as linhas para tabelas com otimização de memória não são recuperadas do pool de buffers usando 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. 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 Pedido e Cliente, 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 junção de tabelas com otimização de memória.

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

Este plano de execução 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.
- Não há suporte para índices clusterizados com 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 em tabelas com otimização de memória podem acessar com eficiência todas as colunas da tabela sem precisar armazená-las no índice ou se referir a um índice clusterizado.
Este plano contém um Hash Match em vez de um Merge Join. Os índices nas tabelas do Pedido e do Cliente são índices de hash e, portanto, não são ordenados. Um Merge Join requer operadores de classificação que diminuiriam o desempenho.

Procedimentos armazenados compilados nativamente

Procedimentos armazenados nativamente compilados são procedimentos Transact-SQL que são compilados para código de máquina, em vez de serem interpretados pelo motor de execução de consultas. O script a seguir cria um procedimento armazenado compilado nativamente 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 primeiro tempo de execução. (Uma parte da compilação, particularmente a análise e a 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 sã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 para procedimentos armazenados compilados e interpretados nativamente:

	Compilado de forma nativa	Interpretado
Compilação inicial	No momento da criação.	Na primeira execução.
Recompilação automática	Após a primeira execução do procedimento após a reinicialização de um banco de dados ou servidor.	Na reinicialização do servidor. Ou, expulsão do cache de planos, geralmente baseada em alterações de esquema ou estatísticas, ou pressão de memória.
Recompilação manual	Não há suporte. A solução alternativa é remover e recriar o procedimento armazenado.	Use `sp_recompile`. Você pode remover manualmente o plano do cache, por exemplo, por meio de DBCC FREEPROCCACHE. Você também pode criar o procedimento armazenado WITH RECOMPILE e o procedimento armazenado será recompilado a cada execução.

Compilação e processamento de consulta

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

Compilação nativa de procedimentos armazenados.

O processo é descrito como,

O usuário emite uma CREATE PROCEDURE instrução para o SQL Server.
O analisador sintático e o algebrizador criam o fluxo de processamento para o procedimento, bem como as árvores de consulta para as consultas Transact-SQL dentro do procedimento armazenado.
O otimizador cria planos de execução de consulta otimizados para todas as consultas no procedimento armazenado.
O compilador OLTP In-Memory usa o fluxo de processamento com os planos de consulta otimizados inseridos e gera uma DLL que contém o código do computador para executar o procedimento armazenado.
A DLL gerada é 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 compilado nativamente é descrita da seguinte maneira:

O usuário emite uma instrução EXECusp_myproc.
O analisador extrai o nome e os parâmetros de procedimento armazenados.

Se a instrução foi preparada, por exemplo, usando sp_prep_exec, o analisador não precisa extrair o nome do procedimento e os parâmetros no momento da execução.
O runtime In-Memory OLTP localiza o ponto de entrada da DLL para o procedimento armazenado.
O código do computador na DLL é executado e os resultados são retornados ao cliente.

Detecção de parâmetros

Procedimentos armazenados interpretados Transact-SQL são compilados na primeira execução, ao contrário de procedimentos armazenados compilados de forma nativa, 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 ao gerar o plano de execução. Esse uso de parâmetros durante a compilação é chamado de detecção de parâmetros.

A detecção de parâmetros não é usada para compilar procedimentos armazenados compilados nativamente. Todos os parâmetros para o procedimento armazenado são considerados com valores DESCONHECIDOs. Assim como os procedimentos armazenados interpretados, os procedimentos armazenados compilados nativamente também dão suporte ao hint 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 nativamente

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 no 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 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 verificação de índice, que fornecem linhas para um operador de junção de mesclagem. O operador de junção por mesclagem fornece linhas para um operador de seleção. O operador select, por fim, retorna as linhas para o cliente.

Operadores de consulta em procedimentos armazenados compilados nativamente

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

Operador	Exemplo de consulta
SELECIONAR	`SELECT OrderID FROM dbo.[Order]`
INSERIR	`INSERT dbo.Customer VALUES ('abc', 'def')`
ATUALIZAÇÃO	`UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'`
EXCLUIR	`DELETE dbo.Customer WHERE CustomerID='abc'`
Computar Escalar	Esse operador é usado para funções intrínsecas e conversões de tipo. Nem todas as funções e conversões de tipo têm suporte em procedimentos armazenados compilados nativamente. `SELECT OrderID+1 FROM dbo.[Order]`
Junção de loops aninhados	Nested Loops é o único operador de junção suportado em procedimentos armazenados compilados nativamente. Todos os planos que contêm junções usarão o operador Loops Aninhados, mesmo que o plano para a mesma consulta executada como interpretado Transact-SQL contenha uma junção de hash ou mesclagem. `SELECT o.OrderID, c.CustomerID` `FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`
Classificar	`SELECT ContactName FROM dbo.Customer` `ORDER BY ContactName`
Início	`SELECT TOP 10 ContactName FROM dbo.Customer`
Classificação superior	A `TOP` expressão (o número de linhas a serem retornadas) não pode exceder 8.000 linhas. Menos se também houver operadores de junção e agregação na consulta. Junções e agregação normalmente reduzem o número de linhas a serem classificadas, em comparação com a contagem de linhas das tabelas base. `SELECT TOP 10 ContactName FROM dbo.Customer` `ORDER BY ContactName`
Agregação de fluxo	Observe que o operador Hash Match não tem suporte para agregação. Portanto, toda a agregação em procedimentos armazenados compilados nativamente usa o operador Stream Aggregate, mesmo que o plano para a mesma consulta em Transact-SQL interpretado use o operador Hash Match. `SELECT count(CustomerID) FROM dbo.Customer`

Estatísticas de coluna e junções

O SQL Server mantém estatísticas sobre valores em colunas de chave de índice para ajudar a estimar o custo de determinadas operações, como verificação de índice e buscas de índice. ( O SQL Server também cria estatísticas em colunas de chave que não são de índice se você as cria explicitamente ou se o otimizador de consulta as cria em resposta a uma consulta com um predicado.) A métrica principal 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 custos. No entanto, como a contagem de páginas não é importante para tabelas com otimização de memória (é sempre zero), essa discussão se concentra na contagem de linhas. A estimativa começa com os operadores de busca e varredura de índices no plano e, em seguida, é estendida para incluir outros operadores, como o operador de junção. O número estimado de linhas a serem processadas por um operador de junção baseia-se na estimativa dos operadores de índice, busca e verificação subjacentes. Para o acesso Transact-SQL interpretado a tabelas com otimização de memória, você pode observar o plano de execução real para ver a diferença entre as contagens de linhas estimadas e reais para os operadores no plano.

Para o exemplo na figura 1,

A verificação de índice clusterizado no Cliente estimou 91; real 91.
A verificação de índice não clusterizado em CustomerID estimou 830; real 830.
O operador Merge Join estimou 815; realizou 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 verificações completas de tabela e índice são sempre precisas. A estimativa para a junção também é bastante precisa.

Se essas estimativas forem alteradas, as considerações de custo para diferentes alternativas de plano também mudarão. Por exemplo, se um dos lados da junção tiver uma contagem estimada de linhas de 1 ou apenas algumas linhas, o uso de junções por laços aninhados é menos custoso.

Este é o plano para a consulta:

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 Cliente:

Estatísticas de coluna e junções.

Em relação a este plano de consulta:

O Hash Match foi substituído por um operador de junção física Nested Loops.
A verificação de índice completa em IX_CustomerID foi substituída por uma busca de índice. Isso resultou na verificação de 5 linhas, em vez das 830 linhas necessárias para a verificação completa do índice.

Estatísticas e cardinalidade para tabelas de Memory-Optimized

O SQL Server mantém estatísticas em nível de coluna para tabelas com otimização de memória. Além disso, ele mantém a contagem real de linhas da tabela. No entanto, ao contrário das tabelas baseadas em disco, as estatísticas para tabelas com otimização de memória não são atualizadas automaticamente. Portanto, as estatísticas precisam ser atualizadas manualmente após alterações significativas nas tabelas. Para obter mais informações, consulte Estatísticas para tabelas de Memory-Optimized.

Consulte Também

TabelasMemory-Optimized

Last updated on 2017-06-13

Partilhar via