Modello di applicazione per il partizionamento di tabelle con ottimizzazione per la memoria
Si applica a:
SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure
OLTP in memoria supporta un modello di progettazione di applicazioni che gestisce le risorse per le prestazioni per dati relativamente correnti. Questo modello può essere applicato quando i dati correnti vengono letti o aggiornati molto più spesso rispetto ai dati meno recenti. In questo caso, i dati correnti sono detti attivi o ad accesso frequente e i dati meno recenti sono detti ad accesso sporadico.
Il concetto è archiviare i dati ad accesso frequente in una tabella ottimizzata per la memoria. Su base settimanale o mensile, i dati meno recenti che rientrano nella categoria dei dati ad accesso sporadico vengono spostati in una tabella partizionata. I dati nella tabella partizionata vengono archiviati su disco o in un'altra unità disco rigido, non in memoria.
In genere, questa progettazione usa una chiave datetime per consentire al processo di spostamento di distinguere in modo efficiente tra dati ad accesso frequente e quelli ad accesso sporadico.
Partizionamento avanzato
In questa progettazione è previsto l'uso di una tabella partizionata che include anche una partizione ottimizzata per la memoria. Per il corretto funzionamento di questa progettazione, è necessario assicurarsi che tutte le tabelle condividano uno schema comune. Nell'esempio di codice riportato più avanti in questo articolo viene illustrata la tecnica.
Si presume che i nuovi dati siano ad accesso frequente per definizione. I dati ad accesso frequente vengono inseriti e aggiornati nella tabella ottimizzata per la memoria. I dati ad accesso sporadico vengono mantenuti nella tabella partizionata tradizionale. Periodicamente, una stored procedure aggiunge una nuova partizione. La partizione contiene i dati ad accesso sporadico più recenti che sono stati spostati fuori dalla tabella ottimizzata per la memoria.
Se un'operazione richiede solo dati ad accesso frequente, può usare le stored procedure compilate in modo nativo per accedere ai dati. Per unire in join la tabella ottimizzata per la memoria con la tabella partizionata, le operazioni che potrebbero accedere a dati ad accesso frequente o sporadico devono usare istruzioni Transact-SQL interpretate.
Aggiungere una partizione
I dati che sono diventati di recente ad accesso sporadico devono essere spostati nella tabella partizionata. I passaggi per questo scambio di partizione periodico sono i seguenti:
- Per i dati nella tabella ottimizzata per la memoria, determinare il valore datetime che rappresenta il limite o il valore di cambio data tra dati ad accesso frequente e dati diventati di recente ad accesso sporadico.
- Inserisci i nuovi dati ad accesso sporadico, dalla tabella OLTP in memoria in una tabella cold_staging.
- Eliminare gli stessi dati ad accesso sporadico dalla tabella ottimizzata per la memoria.
- Scambia la tabella cold_staging in una partizione.
- Aggiungere la partizione.
Finestra di manutenzione
Uno dei passaggi precedenti consiste nell'eliminare i dati diventati di recente ad accesso sporadico dalla tabella ottimizzata per la memoria. Esiste un intervallo di tempo tra questa eliminazione e il passaggio finale che aggiunge la nuova partizione. Durante questo intervallo, le applicazioni che tentano di leggere i dati diventati di recente ad accesso sporadico riscontreranno errori.
Per un esempio correlato, vedere Partizionamento a livello di applicazione.
Codice di esempio
L'esempio Transact-SQL seguente viene presentato in una serie di blocchi di codice più piccoli, solo per la facilità di presentazione. È possibile aggiungerli tutti in un unico grande blocco di codice per i test.
Nel suo complesso, l'esempio T-SQL illustra come usare una tabella ottimizzata per la memoria con una tabella basata su disco partizionata.
Nelle prime fasi dell'esempio T-SQL viene creato il database, quindi vengono creati gli oggetti, come le tabelle nel database. Nelle fasi successive viene illustrato come spostare i dati da una tabella ottimizzata per la memoria in una tabella partizionata.
Creazione di un database
Questa sezione dell'esempio T-SQL crea un database di prova. Il database è configurato in modo da supportare sia tabelle ottimizzate per la memoria che tabelle partizionate.
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
Creare una tabella ottimizzata per la memoria per i dati ad accesso frequente
Questa sezione crea la tabella ottimizzata per la memoria che contiene i dati più recenti, in gran parte dati ancora ad accesso frequente.
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
Creare una tabella partizionata per i dati ad accesso sporadico
In questa sezione viene creata la tabella partizionata che contiene i dati ad accesso sporadico.
-- 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
Creare una tabella per archiviare i dati ad accesso sporadico durante lo spostamento
In questa sezione viene creata la tabella cold_staging. Viene anche creata una vista che unisce i dati ad accesso frequente e sporadico dalle due tabelle.
-- 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
Creare la stored procedure
In questa sezione viene creata la stored procedure da eseguire periodicamente. La stored procedure consente di spostare i dati diventati ad accesso sporadico di recente dalla tabella ottimizzata per la memoria alla tabella partizionata.
-- 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
Preparare i dati di esempio ed eseguire la stored procedure
Questa sezione genera e inserisce dati di esempio, quindi esegue la stored procedure a titolo dimostrativo.
-- 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;
Eliminare tutti gli oggetti dimostrativi
Ricordarsi di rimuovere il database di test dimostrativo dal sistema di test.
-- You must first leave the context of the PartitionSample database.
-- USE <A-Database-Name-Here>;
GO
DROP DATABASE PartitionSample;
GO
Vedi anche
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per