Modèle d’application pour partitionner des tables mémoire optimisées
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
OLTP en mémoire prend en charge un modèle de conception d’application qui affiche des ressources de performances sur des données relativement actuelles. Ce modèle peut s’appliquer quand les données actuelles sont lues ou mises à jour de façon beaucoup plus fréquente que les données plus anciennes. Dans ce cas, les données actuelles sont dites actives ou chaudes, tandis que les données plus anciennes sont dites froides.
L’idée principale est de stocker les données chaudes dans une table à mémoire optimisée. Toutes les semaines ou tous les mois, les données plus anciennes devenues froides sont déplacées vers une table partitionnée. Les données de la table partitionnée sont stockées sur un lecteur ou autre disque dur, et non en mémoire.
En règle générale, cette conception se sert d’une clé DateHeure pour permettre au processus de déplacement de distinguer efficacement les données chaudes des données froides.
Partitionnement avancé
La conception fait comme s’il existait une table partitionnée dotée également d’une partition à mémoire optimisée. Pour permettre à cette conception de fonctionner, vous devez veiller à ce que toutes les tables partagent un schéma commun. L’exemple de code présenté plus loin dans cet article en illustre la technique.
Les nouvelles données sont supposées être chaudes par définition. Les données chaudes sont insérées et mises à jour dans la table à mémoire optimisée. Les données froides sont conservées dans la table partitionnée classique. À intervalles réguliers, une procédure stockée ajoute une nouvelle partition. La partition contient les données froides les plus récentes qui ont été déplacées de la table à mémoire optimisée.
Si une opération a besoin uniquement de données chaudes, elle peut utiliser des procédures stockées compilées en mode natif pour accéder aux données. Les opérations qui peuvent accéder aux données chaudes ou froides doivent utiliser Transact-SQL interprétées pour joindre la table optimisée en mémoire à la table partitionnée.
Ajouter une partition
Les données devenues froides récemment doivent être déplacées dans la table partitionnée. Cet échange de partition périodique se déroule comme suit :
- Pour les données contenues dans la table à mémoire optimisée, déterminez la valeur de DateHeure qui constitue la limite ou la démarcation entre les données chaudes et les données nouvellement froides.
- Insérez les données nouvellement froides, de la table OLTP en mémoire, dans une table cold_staging .
- Supprimez ces mêmes données froides de la table à mémoire optimisée.
- Échangez la table cold_staging en une partition.
- Ajoutez la partition.
Fenêtre de maintenance
L’une des étapes précédentes consiste à supprimer les données nouvellement froides de la table à mémoire optimisée. Un certain temps s’écoule entre le moment où cette suppression se produit et où la nouvelle partition est ajoutée à la dernière étape. Pendant ce temps, aucune application ne peut lire les données nouvellement froides.
Pour obtenir un exemple, consultez Partitionnement au niveau de l’application.
Exemple de code
L’exemple Transact-SQL ci-dessous a été divisé en plusieurs blocs de code plus petits dans le seul but d’en faciliter la présentation. Vous pouvez les ajouter à un bloc de code plus grand à des fins de test.
Globalement, l’exemple T-SQL montre comment utiliser une table à mémoire optimisée avec une table sur disque partitionnée.
Les premières phases de l’exemple T-SQL créent la base de données avant de créer des objets comme les tables de la base de données. Les phases suivantes montrent comment déplacer les données d’une table à mémoire optimisée vers une table partitionnée.
Création d'une base de données
Cette section de l’exemple T-SQL crée une base de données de test. La base de données est configurée pour prendre en charge à la fois les tables à mémoire optimisée et les tables partitionnées.
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
Créer une table à mémoire optimisée pour les données chaudes
Cette section crée la table à mémoire optimisée qui contient les données les plus récentes, qui sont principalement des données chaudes fixes.
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
Créer une table partitionnée pour les données froides
Cette section crée la table partitionnée qui contient les données froides.
-- 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
Créer une table pour stocker les données froides pendant le déplacement
Cette section crée la table cold_staging. Une vue réunissant les données chaudes et froides des deux tables est également créée.
-- 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
Créer la procédure stockée
Cette section crée la procédure stockée que vous exécutez à intervalles réguliers. La procédure déplace les données nouvellement froides de la table à mémoire optimisée vers la table partitionnée.
-- 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
Préparer les exemples de données et exécuter la procédure stockée à titre de démonstration
Cette section génère des exemples de données, les insère, puis exécute la procédure stockée à titre de démonstration.
-- 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;
Supprimer tous les objets de démonstration
N’oubliez pas de nettoyer la base de données de test de démonstration de votre système de test.
-- You must first leave the context of the PartitionSample database.
-- USE <A-Database-Name-Here>;
GO
DROP DATABASE PartitionSample;
GO
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour