Tabela temporária e variável de tabela mais rápidas usando a otimização de memória

2025-05-31

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Se você usar tabelas temporárias, variáveis de tabela ou parâmetros com valor de tabela, considere conversões delas para usar tabelas com otimização de memória e variáveis de tabela para melhorar o desempenho. As alterações de código normalmente são mínimas.

Este artigo descreve:

Cenários favorecem a conversão para in-memory.
Etapas técnicas para implementar as conversões para in-memory.
Pré-requisitos da conversão para in-memory.
Um exemplo de código que destaca os benefícios de desempenho de otimização de memória

R. Noções básicas de variáveis de tabelas com otimização de memória

Uma variável de tabela com otimização de memória fornece excelente eficiência usando o mesmo algoritmo com otimização de memória e estruturas de dados que são usadas por tabelas com otimização de memória. A eficiência é maximizada quando a variável de tabela é acessada de dentro de um módulo compilado nativamente.

Uma tabela com otimização de memória variável:

É armazenado somente na memória e não tem nenhum componente no disco.
Não envolve nenhuma atividade de E/S.
Não envolve nenhuma tempdb utilização ou contenção.
Pode ser passado para um procedimento armazenado como um parâmetro com valor de tabela (TVP).
Deve ter pelo menos um índice, hash ou não clusterizado.
- Para um índice de hash, o bucket idealmente deve ser de 1 a 2 vezes o número de chaves de índice exclusivo esperado, porém normalmente não há problema em superestimar a contagem de bucket (até 10 vezes). Para obter mais informações, consulte Índices em tabelas com otimização de memória.

Tipos de objeto

OLTP in-memory fornece os seguintes objetos que podem ser usados para tabelas temp com otimização de memória e variáveis de tabela:

Tabelas com otimização de memória
- Durabilidade = SCHEMA_ONLY
Variáveis de tabela com otimização de memória
- Devem ser declaradas em duas etapas (em vez de embutidas):
  - CREATE TYPE my_type AS TABLE ...; , então
  - DECLARE @mytablevariable my_type;.

B. Cenário: substituir tabela temporária global

Substituir uma tabela temporária global por uma tabela com otimização memória SCHEMA_ONLY é bastante simples. A maior alteração é criar a tabela no momento da implantação, não em runtime. A criação de tabelas com otimização de memória demora mais do que a criação de tabelas tradicionais, devido a otimizações de tempo de compilação. A criação e a remoção de tabelas com otimização de memória como parte da carga de trabalho online afetariam o desempenho dessa carga, bem como o desempenho das operações de refazer em secundários do Grupo de Disponibilidade Always On e na recuperação de banco de dados.

Suponha que você tenha a seguinte tabela temporária global.

CREATE TABLE ##tempGlobalB
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Considere substituir a tabela temporária global pela seguinte tabela com otimização de memória que tem DURABILITY = SCHEMA_ONLY.

CREATE TABLE dbo.soGlobalB
(
    Column1 INT NOT NULL INDEX ix1 NONCLUSTERED,
    Column2 NVARCHAR (4000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Etapas

A conversão de temporário global para SCHEMA_ONLY é o seguinte:

Crie a dbo.soGlobalB tabela, uma vez, da mesma forma que faria com qualquer tabela tradicional em disco.
Em seu Transact-SQL (T-SQL), remova a criação da tabela ##tempGlobalB. É importante criar a tabela com otimização de memória no momento da implantação, não em runtime, para evitar a sobrecarga de compilação que vem com a criação da tabela.
No seu T-SQL, substitua todas as ocorrências de ##tempGlobalB por dbo.soGlobalB.

C. Cenário: substituir tabela temporária de sessão

As preparações para substituir uma tabela temporária de sessão envolvem mais T-SQL que para o cenário anterior da tabela temporária global. Felizmente, o T-SQL extra não significa que mais esforço seja necessário para realizar a conversão.

Assim como no cenário de tabela temporária global, a maior alteração é criar a tabela no momento da implantação, não no runtime, para evitar a sobrecarga de compilação.

Suponha que você tem a seguinte tabela temporária de sessão.

CREATE TABLE #tempSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Primeiro, crie a seguinte função de valor de tabela para filtrar em @@spid. A função pode ser usada por todas as tabelas SCHEMA_ONLY que você converte a partir de tabelas temporárias de sessão.

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter SMALLINT)
RETURNS TABLE
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SpidFilter
    WHERE @SpidFilter = @@spid

Em segundo lugar, crie a tabela SCHEMA_ONLY, bem como uma política de segurança na tabela.

Cada tabela com otimização de memória deve ter pelo menos um índice.

Para a tabela dbo.soSessionC, um HASH de índice pode ser melhor se for possível calcular o BUCKET_COUNT apropriado. Porém para este exemplo, simplificamos um índice NONCLUSTERED.

CREATE TABLE dbo.soSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

Em terceiro lugar, seu código geral T-SQL:

Altere todas as referências da tabela temporária nas instruções Transact-SQL para a nova tabela com otimização de memória:
- Antigo: #tempSessionC
- Novo: dbo.soSessionC
Substitua as instruções CREATE TABLE #tempSessionC em seu código por DELETE FROM dbo.soSessionC, para garantir que uma sessão não seja exposta ao conteúdo da tabela inserido por uma sessão anterior com o mesmo session_id. É importante criar a tabela com otimização de memória no momento da implantação, não em runtime, para evitar a sobrecarga de compilação que vem com a criação da tabela.
Remova as DROP TABLE #tempSessionC declarações do código. Opcionalmente, você pode inserir uma DELETE FROM dbo.soSessionC declaração caso o tamanho da memória seja uma possível preocupação.

D. Cenário: uma variável da tabela pode ser MEMORY_OPTIMIZED=ON

Uma variável de tabela tradicional representa uma tabela no tempdb banco de dados. Para um desempenho muito mais rápido, você pode otimizar a memória da variável de tabela.

Aqui está o T-SQL para uma variável de tabela tradicional. Seu escopo termina quando o lote ou a sessão termina.

DECLARE @tvTableD TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));

Converter inline em explícito

A sintaxe anterior deve criar a variável de tabela embutida. A sintaxe embutida não dá suporte à otimização de memória. Por isso, vamos converter a sintaxe embutida na sintaxe explícita para o TYPE.

Âmbito: A definição TYPE criada pelo primeiro lote delimitado por go persiste mesmo depois que o servidor é desligado e reiniciado. Porém, após o primeiro delimitador go, a tabela declarada @tvTableC persiste somente até o próximo go ser atingido e o lote terminar.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));
GO

SET NOCOUNT ON;

DECLARE @tvTableD AS dbo.typeTableD;
INSERT INTO @tvTableD (Column1) VALUES (1), (2);
SELECT * FROM @tvTableD;
GO

D.2 Conversão explícita em disco para otimização de memória

Uma variável de tabela com otimização de memória não reside em tempdb. A otimização de memória resulta em um aumento de velocidade geralmente 10 vezes mais rápido ou ainda maior.

A conversão para a otimização de memória é obtida em apenas uma etapa. Aprimore a criação explícita de TYPE da seguinte forma, que adiciona:

Um índice. Novamente, cada tabela com otimização de memória deve ter pelo menos um índice.
MEMÓRIA_OTIMIZADA = ATIVADO.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Concluído.

E. FILEGROUP de pré-requisito for SQL Server

No Microsoft SQL Server, para usar recursos com otimização de memória, seu banco de dados deve ter um FILEGROUP declarado com MEMORY_OPTIMIZED_DATA.

O Banco de Dados SQL do Azure não requer a criação desse FILEGROUP.

Pré-requisito: o seguinte código Transact-SQL para um FILEGROUP é um pré-requisito para os exemplos de código T-SQL longos nas próximas seções deste artigo.

Você deve usar SSMS.exe ou outra ferramenta que pode enviar o T-SQL.
Cole o código T-SQL do FILEGROUP de exemplo no SSMS.
Edite o T-SQL para alterar seus nomes específicos e caminhos de diretório de sua preferência.

Todos os diretórios no valor do FILEGROUP já devem existir, exceto o diretório final, que não deve existir anteriormente.

Execute o T-SQL editado.

Não é necessário executar o T-SQL FILEGROUP mais de uma vez, mesmo se você ajustar e repetir a execução da comparação de velocidade T-SQL na subseção seguinte.

ALTER DATABASE InMemTest2
    ADD FILEGROUP FgMemOptim3 CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemTest2
    ADD FILE (NAME = N'FileMemOptim3a', FILENAME = N'C:\DATA\FileMemOptim3a'
    -- C:\DATA\ preexisted.
) TO FILEGROUP FgMemOptim3;
GO

O script a seguir cria o grupo de arquivos para você e define as configurações de banco de dados recomendadas: enable-in-memory-oltp.sql

Para obter mais informações sobre ALTER DATABASE ... ADD para FILE e FILEGROUP, consulte:

F. Teste rápido para comprovar a melhoria de velocidade

Esta seção fornece código Transact-SQL que você pode executar para testar e comparar o ganho de velocidade para INSERT-DELETE usando uma variável de tabela com otimização de memória. O código é composto de duas partes que são praticamente as mesmas, exceto na primeira metade em que o tipo de tabela tem otimização de memória.

O teste de comparação dura cerca de 7 segundos. Para executar o exemplo:

Pré-requisito: você já deve ter executado o FILEGROUP T-SQL da seção anterior.
Execute o seguinte script T-SQL INSERT-DELETE.

Observe a GO 5001 instrução, que reenvia o T-SQL 5.001 vezes. Você pode ajustar o número e executar novamente.

Ao executar o script em um Banco de Dados SQL do Azure, execute de uma VM na mesma região.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';
DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.
-------------------------------------------------
---- Start traditional on-disk.
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;
GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

Veja a seguir o conjunto de resultados.

---- Next, memory-optimized, faster. ----
2016-04-20 00:26:58.033  = Begin time, _mem.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:26:58.733  = End time, _mem.

---- Next, tempdb based, slower. ----
2016-04-20 00:26:58.750  = Begin time, _tempdb.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:27:05.440  = End time, _tempdb.
---- Tests done. ----

G. Prever o consumo de memória ativa

Você pode aprender a prever as necessidades de memória ativa de suas tabelas com otimização de memória com os seguintes recursos:

Para variáveis de tabela maiores, índices não clusterizados usam mais memória do que para tabelascom otimização de memória. Quanto maior a contagem de linhas e a chave de índice, mais a diferença aumenta.

Se a variável de tabela com otimização de memória é acessada apenas com um valor de chave exato por acesso, um índice de hash pode ser uma escolha melhor do que um índice não clusterizado. No entanto, se você não puder estimar o BUCKET_COUNT apropriado, um índice NONCLUSTERED é uma boa segunda escolha.