Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
In-Memory OLTP ondersteunt een toepassingsontwerppatroon dat prestatiebronnen inzet op relatief actuele gegevens. Dit patroon kan van toepassing zijn wanneer de huidige gegevens veel vaker worden gelezen of bijgewerkt dan oudere gegevens. In dit geval zeggen we dat de huidige gegevens actief of dynamisch zijn en dat de oudere gegevens koud zijn.
Het belangrijkste idee is het opslaan van dynamische gegevens in een tabel die is geoptimaliseerd voor geheugen. Op wekelijkse of maandelijkse basis worden oudere gegevens die koud zijn geworden verplaatst naar een gepartitioneerde tabel. De gepartitioneerde tabel bevat de gegevens die zijn opgeslagen op een schijf of een andere harde schijf, niet in het geheugen.
Normaal gesproken gebruikt dit ontwerp een datum/tijd-sleutel om het verplaatsingsproces in staat te stellen efficiënt onderscheid te maken tussen dynamische en koude gegevens.
Geavanceerde partitionering
Het ontwerp wil een gepartitioneerde tabel nabootsen die ook één partitie heeft die is geoptimaliseerd voor geheugen. Voordat dit ontwerp werkt, moet u ervoor zorgen dat de tabellen allemaal een gemeenschappelijk schema delen. Het codevoorbeeld verderop in dit artikel bevat de techniek.
Er wordt verondersteld dat nieuwe gegevens per definitie veelgevraagd zijn. Actuele gegevens worden ingevoegd en bijgewerkt in de geheugen-geoptimaliseerde tabel. Koude gegevens worden onderhouden in de traditionele gepartitioneerde tabel. Periodiek voegt een opgeslagen procedure een nieuwe partitie toe. De partitie bevat de meest recente koude gegevens die zijn verplaatst uit de tabel die is geoptimaliseerd voor geheugen.
Als een bewerking alleen dynamische gegevens nodig heeft, kan deze systeemeigen gecompileerde opgeslagen procedures gebruiken om toegang te krijgen tot de gegevens. Bewerkingen die toegang kunnen krijgen tot dynamische of koude gegevens, moeten geïnterpreteerde Transact-SQL gebruiken om de tabel die is geoptimaliseerd voor geheugen te koppelen aan de gepartitioneerde tabel.
Een partitie toevoegen
Gegevens die onlangs koud zijn geworden, moeten worden verplaatst naar de gepartitioneerde tabel. De stappen voor deze periodieke partitiewisseling zijn als volgt:
- Bepaal voor de gegevens in de geheugen-geoptimaliseerde tabel de datum/tijd die de scheiding vormt tussen veelvuldig gebruikte en nieuw koude gegevens.
- Voeg de zojuist koude gegevens uit de In-Memory OLTP-tabel in een cold_staging tabel in.
- Verwijder dezelfde koude gegevens uit de tabel die is geoptimaliseerd voor geheugen.
- Verplaats de cold_staging-tabel naar een partitie.
- Voeg de partitie toe.
Onderhoudsvenster
Een van de voorgaande stappen is het verwijderen van de zojuist koude gegevens uit de tabel die is geoptimaliseerd voor geheugen. Er is een tijdsinterval tussen deze verwijdering en de laatste stap waarmee de nieuwe partitie wordt toegevoegd. Tijdens dit interval mislukt elke toepassing die de nieuwe koude gegevens probeert te lezen.
Zie Application-Level Partitionering voor een gerelateerd voorbeeld.
Codevoorbeeld
Het volgende Transact-SQL voorbeeld wordt weergegeven in een reeks kleinere codeblokken, alleen voor het gemak van de presentatie. U kunt ze allemaal toevoegen aan één groot codeblok voor uw tests.
Als geheel laat het T-SQL-voorbeeld zien hoe u een tabel gebruikt die is geoptimaliseerd voor geheugen met een gepartitioneerde tabel op basis van een schijf.
De eerste fasen van het T-SQL-voorbeeld maken de database en maken vervolgens objecten zoals tabellen in de database. Latere fasen laten zien hoe u gegevens verplaatst van een tabel die is geoptimaliseerd voor geheugen naar een gepartitioneerde tabel.
Een database maken
In deze sectie van het T-SQL-voorbeeld wordt een testdatabase gemaakt. De database is geconfigureerd ter ondersteuning van tabellen die zijn geoptimaliseerd voor geheugen en gepartitioneerde tabellen.
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
Een geheugen-geoptimaliseerde tabel maken voor veelgebruikte gegevens.
In deze sectie maakt u de tabel die is geoptimaliseerd voor geheugen, waarin de meest recente gegevens zijn opgeslagen. Dit zijn meestal nog steeds dynamische gegevens.
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
Een gepartitioneerde tabel maken voor koude gegevens
In deze sectie wordt de gepartitioneerde tabel gemaakt die de koude gegevens bevat.
-- 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
Een tabel maken om koude gegevens op te slaan tijdens de verplaatsing
In deze sectie wordt de cold_staging tabel gemaakt. Er wordt ook een weergave gemaakt die de dynamische en koude gegevens uit de twee tabellen samenvoegt.
-- 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
De opgeslagen procedure maken
In deze sectie maakt u de procedure aan die u periodiek uitvoert. Met de procedure worden nieuwe koude gegevens uit de tabel die is geoptimaliseerd voor geheugen verplaatst naar de gepartitioneerde tabel.
-- 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
Voorbeeldgegevens voorbereiden en de opgeslagen procedure demonstreren.
In deze sectie worden voorbeeldgegevens gegenereerd en ingevoegd, waarna de opgeslagen procedure wordt uitgevoerd als demonstratie.
-- 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;
Alle demo-objecten verwijderen
Vergeet niet om de demotestdatabase van uw testsysteem op te schonen.
-- You must first leave the context of the PartitionSample database.
-- USE <A-Database-Name-Here>;
GO
DROP DATABASE PartitionSample;
GO