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

Artigo
05/30/2023

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

Se você usar tabelas temporárias, tabelas variáveis ou parâmetros com valor de tabela, considere convertê-las para aproveitar as 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 utilização ou contenção de tempdb.
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 detalhes, consulte Índices de 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
- Durability = 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 a ##tabela tempdb 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. Criar e descartar tabelas com otimização de memória como parte da carga de trabalho online afetaria o desempenho da carga de trabalho, além do desempenho da ação de refazer em secundários do grupo de disponibilidade Always On e 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 B.1

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

Crie a tabela dbo.soGlobalB uma vez, como faria com qualquer tabela no disco tradicional.
No Transact-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 um runtime, para evitar a sobrecarga de compilação que acompanha a criação da tabela.
No T-SQL, substitua todas as menções de ##tempGlobalB por dbo.soGlobalB.

C. Cenário: substituir a #tabela tempdb 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 a necessidade de mais esforço 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 @@spid. A função poderá ser usada por todas as tabelas SCHEMA_ONLY convertidas 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.

Observe que 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    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH  
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),  
        
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    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:
- Antiga: #tempSessionC
- Nova: dbo.soSessionC
Substitua as instruções CREATE TABLE #tempSessionC no seu código com 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 um runtime, para evitar a sobrecarga de compilação que acompanha a criação da tabela.
Remova as instruções DROP TABLE #tempSessionC do seu código. Opcionalmente, você pode inserir uma instrução DELETE FROM dbo.soSessionC caso o tamanho de memória seja um problema potencial

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

Uma variável de tabela tradicional representa uma tabela no banco de dados tempdb. 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) );

D. 1 Conversão embutida para explícita

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

Escopo: a definição TYPE criada pelo primeiro lote delimitada 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 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 de TYPO explícito para o seguinte, que adiciona:

Um índice. Novamente, cada tabela com otimização de memória deve ter pelo menos um índice.
MEMORY_OPTIMIZED = ON.

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 de otimização de memória, o banco de dados deve ter um FILEGROUP declarado com MEMORY_OPTIMIZED_DATA.

O Banco de Dados SQL do Azure não exige a criação deste 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 do FILEGROUP mais de uma vez, mesmo se você ajustar repetidamente e executar novamente a comparação T-SQL de velocidade na próxima subseção.

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 instrução “Go 5001”, que reenvia os tempos de T-SQL 5001. 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 opção.

H. Confira também

Tabelas com otimização de memória.
Definindo a durabilidade de objetos com otimização de memória.
Atualização cumulativa para eliminar a chance de erros de Falta de memória incorretos, anunciada no blog em setembro de 2017.
- As versões de build do SQL Server 2016 fornecem detalhes completos de versões, service packs e atualizações cumulativas.
- Esses erros inadequados ocasionais não ocorriam na edição Enterprise do SQL Server.

Compartilhar via