Padrão de Aplicação para o Particionamento de Tabelas Otimizadas para Memória

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

In-Memory OLTP suporta um padrão de design de aplicações que disponibiliza recursos de desempenho com dados relativamente atuais. Este padrão pode aplicar-se quando dados atuais são lidos ou atualizados com muito mais frequência do que os dados mais antigos. Neste caso, dizemos que os dados atuais estão ativos ou quentes, e os dados mais antigos estão frios.

A ideia principal é armazenar dados quentes numa tabela otimizada para memória. Semanalmente ou mensalmente, dados antigos que arrefeceram são transferidos 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 de data-hora 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, deve garantir que todas as tabelas partilham um esquema comum. O exemplo de código mais adiante neste artigo mostra a técnica.

Os novos dados são presumidos como quentes por definição. 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:

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.
Insira os novos dados frios, da tabela In-Memory OLTP, em uma tabela cold_staging.
Apague os mesmos dados frios da tabela otimizada para memória.
Converte a tabela cold_staging em uma partição.
Adiciona a partição.

Janela de manutenção

Um dos passos anteriores é eliminar os dados recém-frios da tabela otimizada para memória. Existe 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 falhará.

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 seção cria a tabela cold_staging. 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),
   INDEX ix_date_total NONCLUSTERED (so_date desc, so_total desc),
   CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01')
);
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 FROM 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 INT = (
        SELECT MAX(partition_number)
            FROM sys.partitions
            WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

      EXEC 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 nvarchar( 100) = CONVERT( nvarchar( 100) , @splitdate , 121);
      DECLARE @sql nvarchar( 1000) = N'alter table dbo.SalesOrders_cold_staging 
         add constraint CHK_SalesOrders_cold_staging check (so_date > ''' + @s + ''')';
      PRINT @sql;
      EXEC sp_executesql @sql;

      COMMIT;
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 datetime2 = SYSDATETIME();
EXEC 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 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 datetime2 = SYSDATETIME();
EXEC 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 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.

-- You must first leave the context of the PartitionSample database.

-- USE <A-Database-Name-Here>;
GO

DROP DATABASE PartitionSample;
GO

Ver também

Memory-Optimized Tabelas

Feedback

Esta página foi útil?

Last updated on 2025-12-08