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

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada 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 são geralmente mínimas.

Este artigo descreve:

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

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

Uma variável de tabela com otimização de memória fornece grande eficiência usando o mesmo algoritmo otimizado para 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 variável de tabela com otimização de memória:

É armazenado apenas na memória e não tem nenhum componente no disco.
Não envolve nenhuma atividade de IO.
Não envolve utilização tempdb ou contenção.
Pode ser passado para um proc 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 número de buckets idealmente deve ser entre 1 e 2 vezes o número de chaves de índice exclusivas esperadas, mas superestimar o número de buckets geralmente não há problema (até 10 vezes). Para obter mais informações, consulte Índices nas Tabelas Memory-Optimized.

Tipos de objeto

In-Memory OLTP fornece os seguintes objetos que podem ser usados para tabelas temp de 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
- Deve ser declarado em duas etapas (em vez de em linha):
  - CREATE TYPE my_type AS TABLE ...; e, em seguida,
  - DECLARE @mytablevariable my_type;.

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

Substituir uma tabela temporária global por uma tabela SCHEMA_ONLY com otimização de memória é bastante simples. A maior mudança é criar a tabela no momento da implantação, não no tempo de execução. A criação de tabelas otimizadas para memória leva mais tempo do que a criação de tabelas tradicionais, devido às otimizações em tempo de compilação. Criar e eliminar tabelas otimizadas para memória como parte da carga de trabalho online afetaria o desempenho dessa carga de trabalho, bem como o desempenho das operações de refazer nos secundários de Grupos de Disponibilidade Always On e na recuperação do 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 otimizada para memória que tenha DURABILIDADE = SCHEMA_ONLY.

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

Passos

A conversão de temporário global para SCHEMA_ONLY segue os seguintes passos:

Crie a dbo.soGlobalB tabela apenas uma vez, como se faz com qualquer tabela tradicional no disco.
Do seu Transact-SQL (T-SQL), remova a criação da ##tempGlobalB tabela. É importante criar a tabela otimizada para memória no momento da implantação, não no tempo de execução, para evitar a sobrecarga de compilação que vem com a criação da tabela.
No seu T-SQL, substitua todas as menções de ##tempGlobalB por dbo.soGlobalB.

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

Os preparativos para substituir uma tabela temporária de sessão envolvem mais T-SQL do que para o cenário de tabela temporária global anterior. Felizmente, o T-SQL extra não significa que mais esforço é 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 tempo de execução, para evitar a sobrecarga de compilação.

Suponha que você tenha 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 o @@spid. A função é utilizável por todas as tabelas SCHEMA_ONLY que você converte 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 e 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 índice HASH pode ser melhor, se calcularmos o BUCKET_COUNT apropriado. Mas para esta amostra, simplificamos para um índice não agrupado.

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, no seu código T-SQL geral:

Altere todas as referências à tabela temp em suas instruções Transact-SQL para a nova tabela com otimização de memória:
- Velho:#tempSessionC
- Novo:dbo.soSessionC
Substitua as CREATE TABLE #tempSessionC declarações no seu código por DELETE FROM dbo.soSessionC, para garantir que uma sessão não seja exposta aos conteúdos da tabela inseridos por uma sessão anterior com o mesmo session_id. É importante criar a tabela otimizada para memória no momento da implantação, não no tempo de execução, para evitar a sobrecarga de compilação que vem com a criação da tabela.
Remova as DROP TABLE #tempSessionC instruções do seu código. Opcionalmente, pode inserir uma DELETE FROM dbo.soSessionC declaração caso o tamanho da memória seja uma preocupação potencial.

D. Cenário: A variável de Tabela pode ter 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. O seu escopo termina quando termina o lote ou a sessão.

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

Converter inline em explícito

Diz-se que a sintaxe anterior cria a variável de tabela embutida. A sintaxe embutida não suporta otimização de memória. Então, vamos converter a sintaxe implícita para a sintaxe explícita do TYPE.

Âmbito de aplicação: A definição TYPE criada pelo primeiro lote delimitado persiste mesmo depois que o servidor é desligado e reiniciado. Mas após o primeiro delimitador go, a tabela @tvTableC declarada persiste apenas até que o próximo go seja atingido e o lote termine.

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 Converter de formato explícito em disco para memória otimizada

Uma variável de tabela com otimização de memória não reside no tempdb. A otimização da memória resulta em aumentos de velocidade que geralmente são 10 vezes mais rápidos ou mais.

A conversão para memória otimizada é alcançada em apenas uma etapa. Melhore a criação explícita do TYPE da seguinte forma, que adiciona:

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

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

Concluído.

E. Pré-requisito FILEGROUP para 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 código de Transact-SQL a seguir para um FILEGROUP é um pré-requisito para os exemplos de código T-SQL longos nas seções posteriores deste artigo.

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

Todos os diretórios no valor FILENAME devem preexistir, exceto o diretório final não deve preexistir.

Execute o T-SQL editado.

Não há necessidade de executar o FILEGROUP T-SQL mais de uma vez, mesmo se você ajustar e executar novamente a comparação de velocidade T-SQL 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 recomendadas do banco de dados: enable-in-memory-oltp.sql

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

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

Esta seção fornece o código Transact-SQL que você pode executar para testar e comparar o ganho de velocidade de INSERT-DELETE ao usar uma variável de tabela otimizada para memória. O código é composto por duas metades que são quase as mesmas, exceto na primeira metade o tipo de tabela é otimizado para 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, certifique-se de executar a partir 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

Aqui está 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, os índices não clusterizados usam mais memória do que para tabelas com 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 for 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 não conseguir estimar o BUCKET_COUNT apropriado, um índice NONCLUSTERED é uma boa segunda escolha.

Comentários

Esta página foi útil?

Last updated on 2025-05-31