Tabela temporária e variável de tabela mais rápidas usando a otimização de memóriaFaster temp table and table variable by using memory optimization

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure SQL nãoAzure Synapse Analytics (SQL DW) nãoData Warehouse Paralelo APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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.If you use temporary tables, table variables, or table-valued parameters, consider conversions of them to leverage memory-optimized tables and table variables to improve performance. As alterações de código normalmente são mínimas.The code changes are usually minimal.

Este artigo descreve:This article describes:

  • Cenários favorecem a conversão para in-memory.Scenarios which argue in favor of conversion to In-Memory.
  • Etapas técnicas para implementar as conversões para in-memory.Technical steps for implementing the conversions to In-Memory.
  • Pré-requisitos da conversão para in-memory.Prerequisites before conversion to In-Memory.
  • Um exemplo de código que destaca os benefícios de desempenho de otimização de memóriaA code sample that highlights the performance benefits of memory-optimization

a.A. Noções básicas de variáveis de tabelas com otimização de memóriaBasics of memory-optimized table variables

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 memory-optimized table variable provides great efficiency by using the same memory-optimized algorithm and data structures that are used by memory-optimized tables. A eficiência é maximizada quando a variável de tabela é acessada de dentro de um módulo compilado nativamente.The efficiency is maximized when the table variable is accessed from within a natively compiled module.

Uma tabela com otimização de memória variável:A memory-optimized table variable:

  • É armazenado somente na memória e não tem nenhum componente no disco.Is stored only in memory, and has no component on disk.
  • Não envolve nenhuma atividade de E/S.Involves no IO activity.
  • Não envolve nenhuma utilização ou contenção de tempdb.Involves no tempdb utilization or contention.
  • Pode ser passado para um procedimento armazenado como um parâmetro com valor de tabela (TVP).Can be passed into a stored proc as a table-valued parameter (TVP).
  • Deve ter pelo menos um índice, hash ou não clusterizado.Must have at least one index, either hash or nonclustered.
    • 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).For a hash index, the bucket count should ideally be 1-2 times the number of expected unique index keys, but overestimating bucket count is usually fine (up to 10X). Para obter detalhes, consulte Índices de tabelas com otimização de memória.For details see Indexes for Memory-Optimized Tables.

Tipos de objetoObject types

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:In-Memory OLTP provides the following objects that can be used for memory-optimizing temp tables and table variables:

  • Tabelas com otimização de memóriaMemory-optimized tables
    • Durability = SCHEMA_ONLYDurability = SCHEMA_ONLY
  • Variáveis de tabela com otimização de memóriaMemory-optimized table variables
    • Deve ser declarado em duas etapas (em vez de embutidos):Must be declared in two steps (rather than inline):
      • CREATE TYPE my_type AS TABLE ...; , entãoCREATE TYPE my_type AS TABLE ...; , then
      • DECLARE @mytablevariable my_type;.DECLARE @mytablevariable my_type;.

B.B. Cenário: substituir tabela tempdb global ##Scenario: Replace global tempdb ##table

Substituir uma tabela temporária global por uma tabela com otimização memória SCHEMA_ONLY é bastante simples.Replacing a global temporary table with a memory-optimized SCHEMA_ONLY table is fairly straightforward. A maior alteração é criar a tabela no momento da implantação, não em runtime.The biggest change is to create the table at deployment time, not at 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.Creation of memory-optimized tables takes longer than creation of traditional tables, due to the compile-time optimizations. Criar e descartar as tabelas com otimização de memória como parte da carga de trabalho online afetaria o desempenho da carga de trabalho, bem como o desempenho de restauração em secundários do AlwaysOn e recuperação de banco de dados.Creating and dropping memory-optimized tables as part of the online workload would impact the performance of the workload, as well as the performance of redo on AlwaysOn secondaries and database recovery.

Suponha que você tenha a seguinte tabela temporária global.Suppose you have the following global temporary table.

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.Consider replacing the global temporary table with the following memory-optimized table that has 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.1B.1 Steps

A conversão de temporário global para SCHEMA_ONLY é o seguinte:The conversion from global temporary to SCHEMA_ONLY is the following steps:

  1. Crie a tabela dbo.soGlobalB uma vez, como faria com qualquer tabela no disco tradicional.Create the dbo.soGlobalB table, one time, just as you would any traditional on-disk table.
  2. No Transact-SQL, remova a criação da tabela ##tempGlobalB.From your Transact-SQL, remove the create of the ##tempGlobalB table. É 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.It is important to create the memory-optimized table at deployment time, not at runtime, to avoid the compilation overhead that comes with table creation.
  3. No seu T-SQL, substitua todos os menções de ##tempGlobalB com dbo.soGlobalB.In your T-SQL, replace all mentions of ##tempGlobalB with dbo.soGlobalB.

C.C. Cenário: substituir a tabela tempdb de sessão #Scenario: Replace session tempdb #table

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.The preparations for replacing a session temporary table involve more T-SQL than for the earlier global temporary table scenario. Felizmente o T-SQL extra não significa a necessidade de mais esforço para realizar a conversão.Happily the extra T-SQL does not mean any more effort is needed to accomplish the conversion.

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.As with the global temp table scenario, the biggest change is to create the table at deployment time, not runtime, to avoid the compilation overhead.

Suponha que você tem a seguinte tabela temporária de sessão.Suppose you have the following session temporary table.

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

Primeiro, crie a seguinte função de valor de tabela para filtrar @@spid.First, create the following table-value function to filter on @@spid. A função poderá ser usada por todas as tabelas SCHEMA_ONLY convertidas de tabelas temporárias de sessão.The function will be usable by all SCHEMA_ONLY tables that you convert from session temporary tables.

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.Second, create the SCHEMA_ONLY table, plus a security policy on the table.

Observe que cada tabela com otimização de memória deve ter pelo menos um índice.Note that each memory-optimized table must have at least one index.

  • Para a tabela dbo.soSessionC, um HASH de índice pode ser melhor se for possível calcular o BUCKET_COUNT apropriado.For table dbo.soSessionC a HASH index might be better, if we calculate the appropriate BUCKET_COUNT. Porém para este exemplo, simplificamos um índice NONCLUSTERED.But for this sample we simplify to a NONCLUSTERED index.
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:Third, in your general T-SQL code:

  1. Altere todas as referências à tabela temporária em suas instruções Transact-SQL para a nova tabela com otimização de memória:Change all references to the temp table in your Transact-SQL statements to the new memory-optimized table:
    • Antiga: #tempSessionCOld: #tempSessionC
    • Nova: dbo.soSessionCNew: dbo.soSessionC
  2. 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.Replace the CREATE TABLE #tempSessionC statements in your code with DELETE FROM dbo.soSessionC, to ensure a session is not exposed to table contents inserted by a previous session with the same 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.It is important to create the memory-optimized table at deployment time, not at runtime, to avoid the compilation overhead that comes with table creation.
  3. 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 potencialRemove the DROP TABLE #tempSessionC statements from your code - optionally you can insert a DELETE FROM dbo.soSessionC statement, in case memory size is a potential concern

D.D. Cenário: uma variável da tabela pode ser MEMORY_OPTIMIZED=ONScenario: Table variable can be MEMORY_OPTIMIZED=ON

Uma variável de tabela tradicional representa uma tabela no banco de dados tempdb.A traditional table variable represents a table in the tempdb database. Para um desempenho muito mais rápido, você pode otimizar a memória da variável de tabela.For much faster performance you can memory-optimize your table variable.

Aqui está o T-SQL para uma variável de tabela tradicional.Here is the T-SQL for a traditional table variable. Seu escopo termina quando o lote ou a sessão termina.Its scope ends when either the batch or the session ends.

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

D. 1 Conversão embutida para explícitaD.1 Convert inline to explicit

A sintaxe anterior deve criar a variável de tabela embutida.The preceding syntax is said to create the table variable inline. A sintaxe embutida não dá suporte à otimização da memória.The inline syntax does not support memory-optimization. Por isso, vamos converter a sintaxe embutida na sintaxe explícita para o TYPE.So let us convert the inline syntax to the explicit syntax for the TYPE.

Escopo: a definição TYPE criada pelo primeiro lote delimitada por go persiste mesmo depois que o servidor é desligado e reiniciado.Scope: The TYPE definition created by the first go-delimited batch persists even after the server is shutdown and restarted. 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.But after the first go delimiter, the declared table @tvTableC persists only until the next go is reached and the batch ends.

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óriaD.2 Convert explicit on-disk to memory-optimized

Uma variável de tabela com otimização de memória não reside em tempdb.A memory-optimized table variable does not reside in tempdb. A otimização de memória resulta em um aumento de velocidade geralmente 10 vezes mais rápido ou ainda maior.Memory-optimization results in speed increases that are often 10 times faster or more.

A conversão para a otimização de memória é obtida em apenas uma etapa.The conversion to memory-optimized is achieved in only one step. Aprimore a criação de TYPO explícito para o seguinte, que adiciona:Enhance the explicit TYPE creation to be the following, which adds:

  • Um índice.An index. Novamente, cada tabela com otimização de memória deve ter pelo menos um índice.Again, each memory-optimized table must have at least one index.
  • MEMORY_OPTIMIZED = ON.MEMORY_OPTIMIZED = ON.
CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL   INDEX ix1,  
        Column2  CHAR(10)  
    )  
    WITH  
        (MEMORY_OPTIMIZED = ON);  

Concluído.Done.

E.E. FILEGROUP de pré-requisito for SQL ServerPrerequisite FILEGROUP 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.On Microsoft SQL Server, to use memory-optimized features, your database must have a FILEGROUP that is declared with MEMORY_OPTIMIZED_DATA.

  • O Banco de Dados SQL do Azure não exige a criação deste FILEGROUP.Azure SQL Database does not require creating this 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.Prerequisite: The following Transact-SQL code for a FILEGROUP is a prerequisite for the long T-SQL code samples in later sections of this article.

  1. Você deve usar SSMS.exe ou outra ferramenta que pode enviar o T-SQL.You must use SSMS.exe or another tool that can submit T-SQL.
  2. Cole o código T-SQL do FILEGROUP de exemplo no SSMS.Paste the sample FILEGROUP T-SQL code into SSMS.
  3. Edite o T-SQL para alterar seus nomes específicos e caminhos de diretório de sua preferência.Edit the T-SQL to change its specific names and directory paths to your liking.
  • Todos os diretórios no valor do FILEGROUP já devem existir, exceto o diretório final, que não deve existir anteriormente.All directories in the FILENAME value must preexist, except the final directory must not preexist.
  1. Execute o T-SQL editado.Run your edited T-SQL.
  • 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.There is no need to run the FILEGROUP T-SQL more than one time, even if you repeatedly adjust and rerun the speed comparison T-SQL in the next subsection.
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.sqlThe following script creates the filegroup for you and configures recommended database settings: enable-in-memory-oltp.sql

Para obter mais informações sobre ALTER DATABASE ... ADD para FILE e FILEGROUP, consulte:For more information about ALTER DATABASE ... ADD for FILE and FILEGROUP, see:

F.F. Teste rápido para comprovar a melhoria de velocidadeQuick test to prove speed improvement

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.This section provides Transact-SQL code that you can run to test and compare the speed gain for INSERT-DELETE from using a memory-optimized table variable. 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.The code is composed of two halves that are nearly the same, except in the first half the table type is memory-optimized.

O teste de comparação dura cerca de 7 segundos.The comparison test lasts about 7 seconds. Para executar o exemplo:To run the sample:

  1. Pré-requisito: você já deve ter executado o FILEGROUP T-SQL da seção anterior.Prerequisite: You must already have run the FILEGROUP T-SQL from the previous section.
  2. Execute o seguinte script T-SQL INSERT-DELETE.Run the following T-SQL INSERT-DELETE script.
  • Observe a instrução “Go 5001”, que reenvia os tempos de T-SQL 5001.Notice the 'GO 5001' statement, which resubmits the T-SQL 5001 times. Você pode ajustar o número e executar novamente.You can adjust the number and rerun.

Ao executar o script em um Banco de Dados SQL do Azure, execute de uma VM na mesma região.When running the script in an Azure SQL Database, make sure to run from a VM in the same region.

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  

/*** Actual output, SQL Server 2016:  

---- 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.G. Prever o consumo de memória ativaPredict active memory consumption

Você pode aprender a prever as necessidades de memória ativa de suas tabelas com otimização de memória com os seguintes recursos:You can learn to predict the active memory needs of your memory-optimized tables with the following resources:

Para variáveis de tabela maiores, índices não clusterizados usam mais memória do que para tabelascom otimização de memória.For larger table variables, nonclustered indexes use more memory than they do for memory-optimized tables. Quanto maior a contagem de linhas e a chave de índice, mais a diferença aumenta.The larger the row count and the index key, the more the difference increases.

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.If the memory-optimized table variable is accessed only with one exact key value per access, a hash index might be a better choice than a nonclustered index. No entanto, se você não puder estimar o BUCKET_COUNT apropriado, um índice NONCLUSTERED é uma boa segunda opção.However, if you cannot estimate the appropriate BUCKET_COUNT, a NONCLUSTERED index is a good second choice.

H.H. Confira tambémSee also