Modèle d'application pour partitionner des tables mémoire optimisées
L'OLTP en mémoire prend en charge un modèle où une quantité limitée de données actives est conservée dans une table optimisée en mémoire, alors que les données les moins fréquemment sollicitées sont traitées sur disque. En général, c'est un scénario où les données sont stockées en fonction d'une clé datetime.
Pour émuler les tables partitionnées avec les tables mémoire optimisées, conservez une table partitionnée et une table mémoire optimisée avec un schéma commun. Les données actives sont insérées et mises à jour dans la table mémoire optimisée, alors que les données les moins fréquemment sollicitées sont conservées dans la table partitionnée traditionnelle.
Une application qui sait que les données actives sont dans une table mémoire optimisée peut utiliser des procédures stockées compilées en mode natif pour accéder aux données. Les opérations qui doivent accéder à l’ensemble de l’étendue des données, ou qui ne savent peut-être pas quelle table contient les données pertinentes, utilisent Transact-SQL interprété pour joindre la table optimisée en mémoire à la table partitionnée.
Ce basculement de partition s'effectue comme suit :
Insérez des données de la table de l'OLTP en mémoire dans une table de mise en lots, en utilisant si possible une date de coupure.
Supprimez les données de la table mémoire optimisée.
Basculez dans la table de mise en lots.
Ajoutez la partition active.
Maintenance de données actives
Les actions qui commencent par Deleting ActiveOrders doivent être effectuées pendant une période de maintenance pour éviter des requêtes sans données pendant l'intervalle entre la suppression des données et le basculement dans la table de mise en lots.
Pour obtenir un exemple, consultez Partitionnement au niveau de l’application.
Exemple de code
L'exemple suivant montre comment utiliser une table mémoire optimisée avec une table sur disque partitionnée. Les données couramment utilisées sont stockées en mémoire. Pour enregistrer les données sur le disque, créez une partition et copiez les données dans la table partitionnée.
La première partie de cet exemple permet de créer la base de données et les objets nécessaires. La deuxième partie de l'exemple montre comment déplacer des données d'une table mémoire optimisée dans une table partitionnée.
CREATE DATABASE partitionsample;
GO
-- enable 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
USE partitionsample;
GO
-- frequently used portion of the SalesOrders - memory-optimized
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
-- cold portion of the SalesOrders - partitioned disk-based table
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
-- table for temporary partitions
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
-- aggregate view of the hot and 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_hot'
FROM dbo.SalesOrders_cold;
GO
-- move all sales orders up to the split date to cold storage
CREATE PROCEDURE dbo.usp_SalesOrdersOffloadToCold @splitdate datetime2
AS
BEGIN
BEGIN TRANSACTION;
-- create new heap based on the hot data to be moved to cold storage
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;
-- remove moved data
DELETE FROM dbo.SalesOrders_hot WITH( SERIALIZABLE)
WHERE so_date <= @splitdate;
-- update partition function, and switch in 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 constraint on 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
-- insert sample values in 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 contents of the table
SELECT * FROM dbo.SalesOrders;
GO
-- offload all sales orders to date to cold storage
DECLARE @t datetime2 = SYSDATETIME();
EXEC dbo.usp_SalesOrdersOffloadToCold @t;
-- verify contents of the tables
SELECT * FROM dbo.SalesOrders;
GO
-- verify partitions
SELECT OBJECT_NAME( object_id) , * FROM sys.dm_db_partition_stats ps
WHERE object_id = OBJECT_ID( 'dbo.SalesOrders_cold');
-- insert more rows in 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
-- verify contents of the tables
SELECT * FROM dbo.SalesOrders;
GO
-- offload all sales orders to date to cold storage
DECLARE @t datetime2 = SYSDATETIME();
EXEC dbo.usp_SalesOrdersOffloadToCold @t;
-- verify contents of the tables
SELECT * FROM dbo.SalesOrders;
GO
-- verify 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;