Partizionamento di tabelle nel pool SQL dedicato
Raccomandazioni ed esempi per l'uso di partizioni di tabella nel pool SQL dedicato.
Introduzione alle partizioni di tabella
Le partizioni di tabella consentono di suddividere i dati in gruppi più piccoli. Nella maggior parte dei casi, le partizioni di tabella vengono create in una colonna data. Il partizionamento è supportato in tutti i tipi di tabella del pool SQL dedicato; inclusi columnstore cluster, indice cluster e heap. Il partizionamento è supportato anche in tutti i tipi di distribuzione, sia hash che round robin.
Il partizionamento può recare vantaggio alle prestazioni di query e di conservazione dei dati. Il fatto di recare vantaggio a entrambi i tipi di prestazioni o solo a uno dei due dipende dalla modalità di caricamento dei dati e dalla possibilità di usare la stessa colonna per entrambi gli scopi, poiché il partizionamento può essere eseguito solo su una colonna.
Vantaggi in termini di caricamento
Il vantaggio principale del partizionamento nel pool SQL dedicato consiste nel migliorare l'efficienza e le prestazioni del caricamento dei dati tramite l'uso dell'eliminazione della partizione, il passaggio e l'unione. Nella maggior parte dei casi i dati vengono partizionati in una colonna di data strettamente legata all'ordine in cui i dati vengono caricati nel pool SQL. Uno dei maggiori vantaggi dell'uso delle partizioni per gestire i dati è l'evitare la registrazione delle transazioni. Mentre le semplici operazioni di inserimento, aggiornamento o eliminazione dei dati possono rappresentare l'approccio più semplice, con un po' di impegno e di ragionamento, l'uso del partizionamento durante il processo di caricamento può migliorare notevolmente le prestazioni.
Il cambio di partizioni consente di rimuovere o sostituire rapidamente una sezione di tabella. Ad esempio, una tabella dei fatti delle vendite potrebbe contenere solo i dati relativi agli ultimi 36 mesi. Alla fine di ogni mese, il mese dei dati di vendita meno recenti viene eliminato dalla tabella. Questi dati potrebbero essere eliminati tramite un'istruzione delete per eliminare i dati relativi al mese meno recente.
Tuttavia, l'eliminazione di una grande quantità di dati riga per riga con un'istruzione delete può richiedere troppo tempo e inoltre creare il rischio di transazioni di grandi dimensioni il cui rollback può richiedere molto tempo nel caso in cui qualcosa andasse storto. Un approccio più appropriato consiste nel rilasciare la partizione dei dati meno recente. Nei casi in cui l'eliminazione delle singole righe arrivasse a richiedere alcune ore, l'eliminazione di un'intera partizione potrebbe richiedere pochi secondi.
Vantaggi in termini di query
Il partizionamento può essere usato anche per aumentare le prestazioni delle query. Una query che applica un filtro ai dati partizionati può limitare l'analisi solo alle partizioni idonee. Questo metodo di filtraggio può evitare un'analisi completa della tabella e analizzare solo un subset di dati più piccolo. Con l'introduzione di indici columnstore cluster, i vantaggi delle prestazioni di eliminazione del predicato sono meno utili, ma in alcuni casi possono esserci vantaggi per le query.
Ad esempio, se la tabella dei fatti di vendita viene partizionata in 36 mesi usando il campo data di vendita, le query che filtrano in base alla data di vendita possono ignorare la ricerca nelle partizioni che non corrispondono al filtro.
Dimensionamento della partizione
Mentre il partizionamento può essere usato per aumentare le prestazioni di alcuni scenari, in alcune circostanze la creazione di una tabella con troppe partizioni può influire negativamente sulle prestazioni. Questi problemi valgono soprattutto per le tabelle columnstore cluster.
Affinché il partizionamento sia utile, per un amministratore di database è importante capire quando usare il partizionamento e il numero di partizioni da creare. Non esiste una regola rigida per quanto riguarda il numero di partizioni troppe, dipende dai dati e dal numero di partizioni caricate contemporaneamente. In genere, uno schema di partizione corretto ha decine di centinaia di partizioni, non migliaia.
Quando si creano partizioni in tabelle columnstore cluster, è importante tenere in considerazione il numero di righe appartenenti a ogni partizione. Per ottenere risultati ottimali in termini di compressione e prestazioni delle tabelle columnstore cluster, è necessario almeno 1 milione di righe per distribuzione e partizione. Prima di creare le partizioni, il pool SQL dedicato divide già ogni tabella in 60 distribuzioni.
Eventuali partizionamenti aggiunti a una tabella sono in più rispetto alle distribuzioni create in background. Usando questo esempio, se la tabella dei fatti delle vendite contiene 36 partizioni mensili e dato che un pool SQL dedicato ha 60 distribuzioni, la tabella dei fatti di vendita deve contenere 60 milioni di righe al mese o 2,1 miliardi di righe quando vengono popolati tutti i mesi. Se una tabella contiene un numero di righe inferiore a quello minimo consigliato per partizione, è necessario prendere in considerazione l'uso di un minor numero di partizioni per aumentare il numero di righe per partizione.
Per altre informazioni, vedere anche l'articolo sull'indicizzazione, che include le query che possono valutare la qualità degli indici columnstore cluster.
Differenze di sintassi rispetto a SQL Server
Il pool SQL dedicato introduce un modo per definire partizioni più semplici di SQL Server. Le funzioni e gli schemi di partizionamento non vengono usati nel pool SQL dedicato perché sono in SQL Server. Piuttosto, è necessario identificare la colonna partizionata e le delimitazioni.
Mentre la sintassi del partizionamento può essere leggermente diversa da quella di SQL Server, i concetti di base sono gli stessi. SQL Server e pool SQL dedicato supportano una colonna di partizione per tabella, che può essere partizionata a intervalli. Per altre informazioni sul partizionamento, vedere Tabelle e indici partizionati.
Nell'esempio seguente viene utilizzata l'istruzione CREATE TABLE per partizionare la FactInternetSales tabella nella OrderDateKey colonna :
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
);
Eseguire la migrazione di partizioni da SQL Server
Per eseguire la migrazione delle definizioni di partizione SQL Server al pool SQL dedicato, è sufficiente:
- Eliminare lo schema di partizione di SQL Server.
- Aggiungere la definizione di funzione di partizione all'istruzione CREATE TABLE.
Se si esegue la migrazione di una tabella partizionata da un'istanza di SQL Server, il codice SQL seguente consente di determinare il numero di righe in ogni partizione. Tenere presente che se la stessa granularità di partizionamento viene usata nel pool SQL dedicato, il numero di righe per partizione diminuisce di un fattore di 60.
-- Partition information for a SQL Server Database
SELECT s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, p.[partition_number] AS [partition_number]
, SUM(a.[used_pages]*8.0) AS [partition_size_kb]
, SUM(a.[used_pages]*8.0)/1024 AS [partition_size_mb]
, SUM(a.[used_pages]*8.0)/1048576 AS [partition_size_gb]
, p.[rows] AS [partition_row_count]
, rv.[value] AS [partition_boundary_value]
, p.[data_compression_desc] AS [partition_compression_desc]
FROM sys.schemas s
JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
JOIN sys.partitions p ON p.[object_id] = t.[object_id]
JOIN sys.allocation_units a ON a.[container_id] = p.[partition_id]
JOIN sys.indexes i ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.data_spaces ds ON ds.[data_space_id] = i.[data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.partition_functions pf ON pf.[function_id] = ps.[function_id]
LEFT JOIN sys.partition_range_values rv ON rv.[function_id] = pf.[function_id]
AND rv.[boundary_id] = p.[partition_number]
WHERE p.[index_id] <=1
GROUP BY s.[name]
, t.[name]
, i.[name]
, p.[partition_number]
, p.[rows]
, rv.[value]
, p.[data_compression_desc];
Cambio di partizione
Il pool SQL dedicato supporta la suddivisione, l'unione e il cambio di partizione. Ognuna di queste funzioni viene eseguita usando l'istruzione ALTER TABLE.
Per il cambio di partizione tra due tabelle, è necessario verificare che le partizioni siano allineate sui rispettivi limiti e che le definizioni delle tabelle corrispondano. Poiché non sono disponibili vincoli CHECK per imporre l'intervallo di valori in una tabella, la tabella di origine deve contenere gli stessi limiti di partizione della tabella di destinazione. Se i limiti della partizione non sono uguali, l'opzione di partizione avrà esito negativo perché i metadati della partizione non verranno sincronizzati.
Una divisione di partizione richiede che la rispettiva partizione (non necessariamente l'intera tabella) sia vuota se la tabella ha un indice columnstore cluster (CCI). Altre partizioni nella stessa tabella possono contenere dati. Una partizione che contiene dati non può essere suddivisa, verrà restituito un errore: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. come soluzione alternativa per suddividere una partizione contenente dati, vedere Come suddividere una partizione che contiene dati.
Come suddividere una partizione che contiene dati
Il metodo più efficiente per suddividere una partizione che contiene già dati, consiste nell'usare un'istruzione CTAS . Se la tabella partizionata è un columnstore cluster, la partizione della tabella deve essere vuota per poterla suddividere.
L'esempio seguente crea una tabella columnstore partizionata. Inserisce una riga in ogni partizione:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
);
INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);
La query seguente consente di trovare il numero delle righe usando la vista del catalogo sys.partitions:
SELECT QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
, i.[name] as Index_name
, p.partition_number as Partition_nmbr
, p.[rows] as Row_count
, p.[data_compression_desc] as Data_Compression_desc
FROM sys.partitions p
JOIN sys.tables t ON p.[object_id] = t.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
JOIN sys.indexes i ON p.[object_id] = i.[object_Id]
AND p.[index_Id] = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';
Il comando di divisione seguente riceve un messaggio di errore:
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
È tuttavia possibile usare CTAS per creare una nuova tabella per contenere i dati.
CREATE TABLE dbo.FactInternetSales_20000101
WITH ( DISTRIBUTION = HASH(ProductKey)
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
)
AS
SELECT *
FROM FactInternetSales
WHERE 1=2;
Poiché i limiti della partizione sono allineati, il cambio è consentito. In questo modo la tabella di origine avrà una partizione vuota che in seguito si potrà suddividere.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
A questo punto è sufficiente allineare i dati ai nuovi limiti di partizione usando CTAS e ritrasferire i dati nella tabella principale.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales_20000101]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101;
ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;
Dopo aver completato lo spostamento dei dati, è consigliabile aggiornare le statistiche nella tabella di destinazione. L'aggiornamento delle statistiche assicura che le statistiche rispecchino in modo accurato la nuova distribuzione dei dati nelle rispettive partizioni.
UPDATE STATISTICS [dbo].[FactInternetSales];
Infine, nel caso di un cambio di partizione una tantum per spostare i dati, è possibile eliminare le tabelle create per l'opzione FactInternetSales_20000101_20010101 di partizione e FactInternetSales_20000101. In alternativa, è possibile mantenere le tabelle vuote per le normali opzioni di partizione automatizzate.
Caricare nuovi dati in partizioni contenenti dati in un unico passaggio
Il caricamento dei dati in partizioni con il cambio di partizione è un modo pratico per gestire i nuovi dati in una tabella che non è visibile agli utenti. Può essere difficile nei sistemi occupati gestire la contesa di blocco associata al cambio di partizione.
Per cancellare i dati esistenti in una partizione, è necessario usare per ALTER TABLE disattivare i dati. È stato quindi necessario un altro ALTER TABLE passaggio nei nuovi dati.
Nel pool SQL dedicato l'opzione TRUNCATE_TARGET è supportata nel ALTER TABLE comando . Con TRUNCATE_TARGET il ALTER TABLE comando sovrascrive i dati esistenti nella partizione con nuovi dati. Di seguito è riportato un esempio che usa CTAS per creare una nuova tabella con i dati esistenti, inserisce nuovi dati e quindi ritrasmette tutti i dati nella tabella di destinazione, sovrascrivendo i dati esistenti.
CREATE TABLE [dbo].[FactInternetSales_NewSales]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);
ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
Controllo del codice sorgente del partizionamento della tabella
Nota
Se lo strumento di controllo del codice sorgente non è configurato per ignorare gli schemi di partizione, la modifica dello schema di una tabella per aggiornare le partizioni può causare l'eliminazione e la ricreazione di una tabella durante la distribuzione, che potrebbe non essere verificabile. Potrebbe essere necessaria una soluzione personalizzata per implementare tale modifica, come descritto di seguito. Verificare che lo strumento di integrazione continua/distribuzione continua (CI/CD) lo consenta. In SQL Server Data Tools (SSDT) cercare impostazioni di pubblicazione avanzate "Ignora schemi di partizione" per evitare uno script generato che causa l'eliminazione e la ricreazione di una tabella.
Questo esempio è utile quando si aggiornano gli schemi di partizione di una tabella vuota. Per distribuire continuamente le modifiche di partizione in una tabella con dati, seguire la procedura descritta in Come suddividere una partizione che contiene dati insieme alla distribuzione per spostare temporaneamente i dati da ogni partizione prima di applicare la suddivisione intervallo di partizione. Ciò è necessario perché lo strumento CI/CD non è a conoscenza delle partizioni con dati.
Per evitare che la definizione di tabella venga arrugginita nel sistema di controllo del codice sorgente, è consigliabile prendere in considerazione l'approccio seguente:
Creare la tabella come tabella partizionata, ma senza valori di partizione.
CREATE TABLE [dbo].[FactInternetSales] ( [ProductKey] int NOT NULL , [OrderDateKey] int NOT NULL , [CustomerKey] int NOT NULL , [PromotionKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [OrderQuantity] smallint NOT NULL , [UnitPrice] money NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES () ) );SPLITper suddividere la tabella come parte del processo di distribuzione:-- Create a table containing the partition boundaries CREATE TABLE #partitions WITH ( LOCATION = USER_DB , DISTRIBUTION = HASH(ptn_no) ) AS SELECT ptn_no , ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no FROM ( SELECT CAST(20000101 AS INT) ptn_no UNION ALL SELECT CAST(20010101 AS INT) UNION ALL SELECT CAST(20020101 AS INT) UNION ALL SELECT CAST(20030101 AS INT) UNION ALL SELECT CAST(20040101 AS INT) ) a; -- Iterate over the partition boundaries and split the table DECLARE @c INT = (SELECT COUNT(*) FROM #partitions) , @i INT = 1 --iterator for while loop , @q NVARCHAR(4000) --query , @p NVARCHAR(20) = N'' --partition_number , @s NVARCHAR(128) = N'dbo' --schema , @t NVARCHAR(128) = N'FactInternetSales' --table; WHILE @i <= @c BEGIN SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i); SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');'); -- PRINT @q; EXECUTE sp_executesql @q; SET @i+=1; END -- Code clean-up DROP TABLE #partitions;
Con questo approccio, il codice nel controllo del codice sorgente rimane statico e i valori limite di partizionamento possono essere dinamici; evoluzione con il pool SQL nel tempo.
Passaggi successivi
Per altre informazioni sullo sviluppo di tabelle, vedere gli articoli sui cenni preliminari sulle tabelle.