Padrão de aplicação para particionamento de tabelas otimizadas para memória

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

In-Memory OLTP suporta um padrão de design de aplicações que prioriza o desempenho dos dados atuais. Este padrão aplica-se quando dados atuais são lidos ou atualizados significativamente mais frequentemente do que dados mais antigos. Os dados atuais são considerados ativos ou quentes, e os dados mais antigos são frios.

A ideia principal é armazenar dados quentes numa tabela otimizada para memória. Semanalmente ou mensalmente, os dados antigos que se tornam frios são movidos para uma tabela particionada. A tabela particionada tem os seus dados armazenados num disco ou outro disco rígido, não na memória.

Normalmente, este design utiliza uma chave datetime2 para permitir que o processo de transferência distinga eficientemente entre dados quentes e frios.

Particionamento avançado

O design pretende imitar a existência de uma tabela particionada que também possui uma partição otimizada para memória. Para que este design funcione, certifique-se de que as tabelas partilham um esquema comum. O exemplo de código mais adiante neste artigo mostra a técnica.

Novos dados são sempre considerados quentes. Os dados frequentemente acessados são inseridos e atualizados na tabela otimizada para 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 otimizada para memória.

Se uma operação precisar apenas de dados quentes, pode usar procedimentos armazenados compilados nativamente para aceder aos dados. As operações que possam aceder a dados quentes ou frios devem usar Transact-SQL interpretado, para juntar a tabela otimizada para memória com a tabela particionada.

Adicionar uma partição

Os dados que recentemente arrefeceram têm de ser movidos para a tabela particionada. Os passos para esta troca periódica de partições são os seguintes:

1. Para os dados na tabela otimizada para memória, determine a data-hora que é o limite ou ponto de corte entre dados quentes e dados que se tornaram recentemente frios.

2. Insira os novos dados frios da tabela OLTP In-Memory numa tabela cold_staging.

3. Apague os mesmos dados frios da tabela otimizada para memória.

4. Substituir a cold_staging tabela numa partição.

5. Adiciona a partição.

Janela de manutenção

Um dos passos anteriores é eliminar os dados recém-frios da tabela otimizada para memória. Há um intervalo de tempo entre esta eliminação e o passo final que adiciona a nova partição. Durante este intervalo, qualquer aplicação que tente ler os dados recém-frios falha.

Para um exemplo relacionado, veja Particionamento ao Nível da Aplicação.

Exemplo de código

O seguinte exemplo Transact-SQL é apresentado numa série de blocos de código mais pequenos, apenas para facilitar a apresentação. Podes adicioná-los todos num grande bloco de código para os teus testes.

No seu conjunto, o exemplo T-SQL mostra como usar uma tabela otimizada para memória com uma tabela baseada em disco particionada.

As primeiras fases do exemplo T-SQL criam a base de dados e depois criam objetos como tabelas na base de dados. Fases posteriores mostram como mover dados de uma tabela otimizada para memória para uma tabela particionada.

Criar uma base de dados

Esta secção do exemplo T-SQL cria uma base de dados de teste. A base de dados está configurada para suportar tanto tabelas otimizadas para memória como 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

Crie uma tabela otimizada para memória para dados quentes

Esta secção cria a tabela otimizada para memória destinada a conter os dados mais recentes, que na sua maioria 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 secçã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

Crie uma tabela para armazenar dados frios durante a mudança

Esta secção cria a cold_staging tabela. Também é criada uma visã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 secção cria o procedimento armazenado que se executa periodicamente. O procedimento move dados recém-frios 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

Preparar dados de amostra e demonstrar o procedimento armazenado

Esta secção gera e insere dados de amostra, e depois executa o procedimento armazenado como 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;

Elimine todos os objetos de demonstração

Lembra-te de limpar a base de dados de testes de demonstração do teu sistema de testes.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Conteúdo relacionado

• Base de dados de exemplo para OLTP em memória