Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure
In-Memory OLTP 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 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 consultas; o papel das estatísticas em tabelas com otimização de memória, bem como diretrizes para solucionar problemas de planos de consulta incorreta.
O uso de Transact-SQL interpretado para aceder a tabelas otimizadas para a memória.
Considerações sobre a otimização de consultas para acesso à tabela com otimização de memória.
Compilação e processamento de procedimentos armazenados compilados nativamente.
Estatísticas que são usadas para estimativa de custos pelo otimizador.
Maneiras de corrigir planos de consulta ineficazes.
Exemplo de consulta
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 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 and pubs Sample Databases for SQL Server 2000.
Considere a seguinte consulta, que une as tabelas Cliente e Pedido e retorna o 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 consultas:
As linhas da tabela Customer 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 associação, e a coluna de chave primária OrderID, que é retornada ao usuário. O retorno de colunas adicionais da tabela de Pedidos implicaria consultas no índice agrupado dessa tabela.
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 facto de que ambos os índices estão ordenados na coluna de junção CustomerID.
Considere uma pequena 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 esta 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 operador físico Hash Match agora é usado para o Inner Join . O índice clusterizado em Order não está ordenado por CustomerID, por isso, um Merge Join exigiria um operador de ordenação, o que afetaria o desempenho. Observe o custo relativo do operador Hash Match (75%) em comparação com o custo do operador Merge Join no exemplo anterior (46%). O otimizador teria considerado o operador Hash Match também no exemplo anterior, mas concluiu que o operador Merge Join deu melhor desempenho.
Processamento de consultas do SQL Server para tabelas Disk-Based
O diagrama a seguir descreve o fluxo de processamento de consultas 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 constroem 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 contendo operadores físicos (por exemplo, junção de loops aninhados). Após a otimização, o plano pode ser armazenado no cache do plano. Esta etapa será ignorada se o cache do plano já contiver um plano para essa consulta.
O motor de execução de consultas processa uma interpretação do plano de consulta.
Para cada operador de busca de índice, varredura de índice e varredura de tabela, o mecanismo de execução solicita linhas das respetivas estruturas de índice e tabela dos Métodos de Acesso.
Os Métodos de Acesso recuperam as linhas das páginas de índice e de dados no pool de buffers e carregam páginas do disco para o pool de buffers conforme necessário.
Para a primeira consulta de exemplo, o mecanismo de execução solicita linhas no índice clusterizado em Customer e o índice não clusterizado em Order from Access Methods. Os Métodos de Acesso percorrem as estruturas de índice da árvore B para recuperar as linhas solicitadas. Nesse caso, todas as linhas são recuperadas, pois o plano exige verificações de índice completas.
Observação
A documentação usa o termo árvore B geralmente em referência a índices. Em índices de armazenamento de linha, o Mecanismo de Banco de Dados implementa uma árvore B+. Isto não se aplica a índices em columnstore nem a índices em tabelas otimizadas para memória. Para obter mais informações, consulte o SQL Server e o guia de design de arquitetura e design de índice SQL do Azure.
Interpretado Transact-SQL Acesso às 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 motor de execução lê o operador e seus parâmetros e executa a operação.
A Transact-SQL interpretada pode ser usada para aceder a tabelas otimizadas para memória e baseadas em disco. A figura a seguir ilustra o processamento de consultas para acesso Transact-SQL interpretado a tabelas com otimização de memória:
Pipeline de processamento de consultas para acesso Transact-SQL interpretado a tabelas com otimização de memória.
Como ilustrado pela figura, o pipeline de processamento de consultas 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 em relação ao pipeline de processamento de consultas tradicional (figura 2) é que as linhas para tabelas com otimização de memória não são recuperadas do pool de buffers usando os 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, como ilustrado pelo exemplo a seguir.
O script de Transact-SQL a seguir contém versões otimizadas para 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 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 contém uma verificação de tabela em vez de uma verificação de índice clusterizado para a tabela Cliente:
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 fazer referência a um índice clusterizado.
Este plano contém um Hash Match em vez de um Merge Join. Os índices nas tabelas Ordem e Cliente são índices de hash e, portanto, não são ordenados. Uma Merge Join exigiria operadores de ordenação que diminuiriam o desempenho.
Procedimentos armazenados compilados nativamente
Procedimentos armazenados compilados nativamente são Transact-SQL procedimentos armazenados que são compilados em código de máquina, ao invés de serem interpretados pelo mecanismo de execução de consultas. O script a seguir cria um procedimento armazenado compilado nativamente que executa a consulta de exemplo (da 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 momento de execução. (Uma parte da compilação, particularmente análise e algebrização, ocorre 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:
| Tipo de compilação | Compilado de forma nativa | Interpretada |
|---|---|---|
| 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, remoção do cache do plano, geralmente com base em alterações de esquema ou estatísticas, ou pressão de memória. |
| Recompilação manual | Use sp_recompile. | Use sp_recompile. Você pode remover manualmente o plano do cache, por exemplo, através do DBCC FREEPROCCACHE. Você também pode criar o procedimento armazenado COM RECOMPILE e o procedimento armazenado será recompilado a cada execução. |
Compilação e processamento de consultas
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 instrução CREATE PROCEDURE para o SQL Server.
O analisador e o algebrizador criam o fluxo de processamento para o procedimento, bem como árvores de consulta para as consultas Transact-SQL no 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 incorporados e gera uma DLL que contém o código da máquina 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 forma:
O usuário emite uma instrução EXECusp_myproc.
O analisador extrai o nome e os parâmetros do procedimento armazenado.
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 In-Memory runtime OLTP localiza o ponto de entrada na DLL para o procedimento armazenado.
O código da máquina na DLL é executado e os resultados de são retornados ao cliente.
Deteção de parâmetros
Os procedimentos armazenados interpretados Transact-SQL são compilados na primeira execução, em contraste com os 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 ao gerar o plano de execução. Esse uso de parâmetros durante a compilação é chamado de deteção de parâmetros.
A deteção de parâmetros não é usada para compilar procedimentos armazenados compilados nativamente. Todos os parâmetros para o procedimento armazenado são considerados como tendo valores DESCONHECIDOS. Como os procedimentos armazenados interpretados, os procedimentos armazenados compilados nativamente também suportam a dica OPTIMIZE FOR. Para obter mais informações, consulte Sugestões 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 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 varredura de índice, que fornecem linhas para um operador de junção por mesclagem. O operador de junção de mesclagem fornece linhas para um operador selecionado. O operador select, finalmente, retorna as linhas para o cliente.
Operadores de consulta em procedimentos armazenados compilados nativamente
A tabela a seguir resume os operadores de consulta suportados dentro de procedimentos armazenados compilados nativamente:
| Operador | Exemplo de consulta | Observações |
|---|---|---|
| SELECIONAR | SELECT OrderID FROM dbo.[Order] |
|
| INSERIR | INSERT dbo.Customer VALUES ('abc', 'def') |
|
| ATUALIZAÇÃO | UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc' |
|
| SUPRIMIR | DELETE dbo.Customer WHERE CustomerID='abc' |
|
| Calcular escalar | SELECT OrderID+1 FROM dbo.[Order] |
Este operador é usado tanto para funções intrínsecas quanto para conversões de tipo. Nem todas as funções e conversões de tipo são suportadas dentro de procedimentos armazenados compilados nativamente. |
| Junção de ciclos aninhados | SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c |
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 Nested Loops, mesmo que o plano para a mesma consulta executada conforme interpretado Transact-SQL contenha um hash ou junção de mesclagem. |
| Ordenar | SELECT ContactName FROM dbo.Customer ORDER BY ContactName |
|
| Início | SELECT TOP 10 ContactName FROM dbo.Customer |
|
| Classificação superior | SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName |
A expressão TOP (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. As junções e a agregação normalmente reduzem o número de linhas a serem classificadas, em comparação com a contagem de linhas das tabelas base. |
| Agregação de Fluxo | SELECT count(CustomerID) FROM dbo.Customer |
Observe que o operador Hash Match não é suportado 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 no Transact-SQL interpretado use o operador Hash Match. |
Estatísticas de colunas 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 criar explicitamente ou se o otimizador de consulta as criar em resposta a uma consulta com um predicado.) A principal métrica na estimativa de custos é 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 zero), esta discussão se concentra na contagem de linhas. A estimação começa com os operadores de busca e leitura de índice no plano e, em seguida, é estendida para incluir os outros operadores, como o operador de associação. O número estimado de linhas a serem processadas por um operador de junção baseia-se na estimativa para os operadores subjacentes de índice, procura e varredura. Para acesso interpretado Transact-SQL a tabelas otimizadas para memória, pode observar o plano de execução real para ver a diferença entre as contagens de linhas estimadas e reais dos operadores do plano.
Para o exemplo da figura 1,
- A varredura de índice clusterizado no Cliente teve uma estimação de 91; real 91.
- A verificação de índice não clusterizado no CustomerID estimou 830; efetivo 830.
- O operador Merge Join estimou 815; real 830.
As estimativas para as varreduras 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 tabelas e índices são sempre precisas. A estimativa para a adesão também é bastante precisa.
Se essas estimativas mudarem, 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 de linhas estimada de 1 ou apenas algumas linhas, usar junções de loops aninhados é menos custoso. Considere a seguinte 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 Customer, o seguinte plano de consulta é gerado:
Em relação a este plano de consulta:
- A correspondência de hash foi substituída por um operador de junção física do tipo Nested Loops.
- A verificação completa do índice no 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.