Padrão de aplicativo para particionamento de tabelas com otimização de memória

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

In-Memory OLTP dá suporte a um padrão de design de aplicativo que prioriza o desempenho dos dados atuais. Esse padrão se aplica quando os dados atuais são lidos ou atualizados com mais frequência do que os dados mais antigos. Os dados atuais são considerados ativos ou quentes, e os dados mais antigos estão frios.

A principal ideia é armazenar dados de acesso frequente em uma tabela com otimização de memória. Semanalmente ou mensalmente, os dados mais antigos que ficam frios são movidos para uma tabela particionada. A tabela particionada tem seus dados armazenados em um disco ou em outro disco rígido, não na memória.

Normalmente, esse design usa uma chave datetime2 para permitir que o processo de movimentação distingue com eficiência entre dados quentes versus frios.

Particionamento avançado

O design pretende imitar a existência de uma tabela particionada que também tenha uma partição com otimização de memória. Para que esse design funcione, verifique se as tabelas compartilham um esquema comum. O exemplo de código mais adiante neste artigo mostra a técnica.

Novos dados são sempre considerados ativos. Os dados de acesso frequente são inseridos e atualizados na tabela com otimização de memória. Os dados frios são mantidos na tabela particionada tradicional. Periodicamente, um procedimento armazenado adiciona uma nova partição. A partição contém os dados frios mais recentes que foram movidos para fora da tabela com otimização de memória.

Se uma operação precisar apenas de dados quentes, ela poderá usar procedimentos armazenados compilados nativamente para acessar os dados. As operações que podem acessar dados quentes ou frios devem usar o Transact-SQL interpretado para unir a tabela otimizada para memória com a tabela particionada.

Adicionar uma partição

Os dados que ficaram frios recentemente devem ser movidos para a tabela particionada. As etapas para esta troca de partição periódica são as seguintes:

1. Para os dados na tabela com otimização de memória, determine a data e hora que marca a transição entre os dados quentes e os dados recém-arrefecidos.

2. Insira os dados recentemente frios, da tabela In-Memory OLTP, em uma tabela cold_staging.

3. Exclua os mesmos dados frios da tabela com otimização de memória.

4. Converta a cold_staging tabela em uma partição.

5. Adicione a partição.

Janela de manutenção

Uma das etapas anteriores é excluir os dados recém-resfriados da tabela otimizada para memória. Há um intervalo de tempo entre essa exclusão e a etapa final que adiciona a nova partição. Durante esse intervalo, qualquer aplicativo que tente ler os dados frios recentemente falhará.

Para ver um exemplo relacionado, veja Particionamento no nível de aplicativo.

Exemplo de código

O exemplo do Transact-SQL a seguir é exibido em uma série de blocos de código menores apenas para facilitar a apresentação. Você poderia acrescentá-los a um grande bloco de código para seu teste.

Como um todo, o exemplo de T-SQL mostra como usar uma tabela com otimização de memória com uma tabela baseada em disco particionada.

As primeiras fases do exemplo de T-SQL criam o banco de dados e, em seguida, criam objetos como tabelas no banco de dados. As fases posteriores mostram como mover dados de uma tabela com otimização de memória para uma tabela particionada.

Criar um banco de dados

Esta seção do exemplo de T-SQL cria um banco de dados de teste. O banco de dados está configurado para dar suporte a tabelas com otimização de memória e tabelas particionadas.

CREATE DATABASE PartitionSample;
GO

-- Add a FileGroup, enabled for In-Memory OLTP.
-- Change file path as needed.
ALTER DATABASE PartitionSample
    ADD FILEGROUP PartitionSample_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE PartitionSample
    ADD FILE (
        NAME = 'PartitionSample_mod',
        FILENAME = 'C:\data\PartitionSample_mod')
    TO FILEGROUP PartitionSample_mod;
GO

Criar uma tabela com otimização de memória para dados de acesso frequente

Esta seção cria a tabela otimizada para memória que contém os dados mais recentes, que, na maioria dos casos, ainda são dados quentes.

USE PartitionSample;
GO

-- Create a memory-optimized table for the HOT Sales Order data.
-- Notice the index that uses DATETIME2.
CREATE TABLE dbo.SalesOrders_hot
(
    so_id INT IDENTITY PRIMARY KEY NONCLUSTERED,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL INDEX ix_date NONCLUSTERED,
    so_total MONEY NOT NULL,
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Criar uma tabela particionada para dados frios

Esta seção cria a tabela particionada que contém os dados frios.

-- Create a partition and table for the COLD Sales Order data.
-- Notice the index that uses DATETIME2.
CREATE PARTITION FUNCTION [ByDatePF](DATETIME2)
    AS RANGE RIGHT
    FOR VALUES ();
GO

CREATE PARTITION SCHEME [ByDateRange]
    AS PARTITION [ByDatePF]
    ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.SalesOrders_cold
(
    so_id INT NOT NULL,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL,
    so_total MONEY NOT NULL,
    CONSTRAINT PK_SalesOrders_cold PRIMARY KEY (so_id, so_date),
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
) ON [ByDateRange](so_date);
GO

Criar uma tabela para armazenar dados frios durante a movimentação

Esta seção cria a cold_staging tabela. Também é criada uma exibição que une os dados quentes e frios das duas tabelas.

-- A table used to briefly stage the newly cold data, during moves to a partition.
CREATE TABLE dbo.SalesOrders_cold_staging
(
    so_id INT NOT NULL,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL,
    so_total MONEY NOT NULL,
    CONSTRAINT PK_SalesOrders_cold_staging PRIMARY KEY (so_id, so_date),
    CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01'),
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
);
GO

-- A view, for retrieving the aggregation of hot plus cold data.
CREATE VIEW dbo.SalesOrders AS
    SELECT so_id,
           cust_id,
           so_date,
           so_total,
           1 AS 'is_hot'
    FROM dbo.SalesOrders_hot
    UNION ALL
    SELECT so_id,
           cust_id,
           so_date,
           so_total,
           0 AS 'is_cold'
    FROM dbo.SalesOrders_cold;
GO

Criar o procedimento armazenado

Esta seção cria o procedimento armazenado que você executa periodicamente. O procedimento move dados recém-resfriados da tabela otimizada para memória para a tabela particionada.

-- A stored procedure to move all newly cold sales orders data
-- to its staging location.
CREATE PROCEDURE dbo.usp_SalesOrdersOffloadToCold
@splitdate DATETIME2
AS
BEGIN
    BEGIN TRANSACTION;

    -- Insert the cold data as a temporary heap.
    INSERT INTO dbo.SalesOrders_cold_staging WITH (TABLOCKX)
    SELECT so_id,
           cust_id,
           so_date,
           so_total
    FROM dbo.SalesOrders_hot WITH (SERIALIZABLE)
    WHERE so_date < @splitdate;

    -- Delete the moved data from the hot table.
    DELETE dbo.SalesOrders_hot WITH (SERIALIZABLE)
    WHERE so_date < @splitdate;

    -- Update the partition function, and switch in the new partition.
    ALTER PARTITION SCHEME [ByDateRange] NEXT USED [PRIMARY];

    DECLARE @p AS INT = (SELECT MAX(partition_number)
                         FROM sys.partitions
                         WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

    EXECUTE sp_executesql N'ALTER TABLE dbo.SalesOrders_cold_staging
            SWITCH TO dbo.SalesOrders_cold partition @i', N'@i int', @i = @p;

    ALTER PARTITION FUNCTION [ByDatePF]()
        SPLIT RANGE (@splitdate);

    -- Modify a constraint on the cold_staging table, to align with new partition.
    ALTER TABLE dbo.SalesOrders_cold_staging
    DROP CONSTRAINT CHK_SalesOrders_cold_staging;

    DECLARE @s AS NVARCHAR (100) = CONVERT (NVARCHAR (100), @splitdate, 121);

    DECLARE @sql AS NVARCHAR (1000) = N'ALTER TABLE dbo.SalesOrders_cold_staging
        ADD CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= ''' + @s + ''')';

    PRINT @sql;

    EXECUTE sp_executesql @sql;

    COMMIT TRANSACTION;

END
GO

Prepare dados de exemplo e demonstre o procedimento armazenado.

Esta seção gera e insere dados de exemplo e, em seguida, executa o procedimento armazenado como uma demonstração.

-- Insert sample values into the hot table.
INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

-- Verify that the hot data is in the table, by selecting from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Treat all data in the hot table as cold data:
-- Run the stored procedure, to move (offload) all sales orders to date to cold storage.
DECLARE @t AS DATETIME2 = SYSDATETIME();
EXECUTE dbo.usp_SalesOrdersOffloadToCold @t;

-- Again, read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Retrieve the name of every partition.
SELECT OBJECT_NAME(object_id),
       *
FROM sys.dm_db_partition_stats AS ps
WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold');

-- Insert more data into the hot table.
INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

-- Read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Again, run the stored procedure, to move all sales orders to date to cold storage.
DECLARE @t AS DATETIME2 = SYSDATETIME();
EXECUTE dbo.usp_SalesOrdersOffloadToCold @t;

-- Read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Again, retrieve the name of every partition.
-- The stored procedure can modify the partitions.
SELECT OBJECT_NAME(object_id),
       partition_number,
       row_count
FROM sys.dm_db_partition_stats AS ps
WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold')
      AND index_id = 1;

Remover todos os objetos de demonstração

Lembre-se de limpar o banco de dados de teste demo do seu sistema de teste.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Conteúdo relacionado

• Banco de dados de exemplo para OLTP na memória