Tabellen partitioneren in een toegewezen SQL-pool
Aanbevelingen en voorbeelden voor het gebruik van tabelpartities in een toegewezen SQL-pool.
Wat zijn tabelpartities?
Met tabelpartities kunt u uw gegevens opsplitsen in kleinere groepen gegevens. In de meeste gevallen worden tabelpartities gemaakt op een datumkolom. Partitioneren wordt ondersteund voor alle toegewezen SQL-pooltabeltypen; inclusief geclusterde columnstore, geclusterde index en heap. Partitionering wordt ook ondersteund voor alle distributietypen, inclusief zowel hash- als round robin gedistribueerd.
Partitionering kan het gegevensonderhoud en de queryprestaties ten goede komen. Of het beide of slechts één voordeel oplevert, is afhankelijk van hoe gegevens worden geladen en of dezelfde kolom voor beide doeleinden kan worden gebruikt, omdat partitionering slechts op één kolom kan worden uitgevoerd.
Voordelen van laden
Het belangrijkste voordeel van partitioneren in een toegewezen SQL-pool is het verbeteren van de efficiëntie en prestaties van het laden van gegevens door het verwijderen van partities, schakelen en samenvoegen. In de meeste gevallen worden gegevens gepartitioneerd op een datumkolom die nauw is gekoppeld aan de volgorde waarin de gegevens in de SQL-pool worden geladen. Een van de grootste voordelen van het gebruik van partities om gegevens te onderhouden, is het vermijden van transactielogboekregistratie. Hoewel eenvoudigweg het invoegen, bijwerken of verwijderen van gegevens de eenvoudigste aanpak kan zijn, met een beetje nadenken en moeite, kan het gebruik van partitionering tijdens het laadproces de prestaties aanzienlijk verbeteren.
Schakelen tussen partities kan worden gebruikt om snel een sectie van een tabel te verwijderen of te vervangen. Een tabel met verkoopfeit kan bijvoorbeeld alleen gegevens bevatten voor de afgelopen 36 maanden. Aan het einde van elke maand worden de oudste verkoopmaandgegevens uit de tabel verwijderd. Deze gegevens kunnen worden verwijderd met behulp van een verwijderinstructie om de gegevens voor de oudste maand te verwijderen.
Het verwijderen van een grote hoeveelheid gegevens per rij met een delete-instructie kan echter te veel tijd in beslag nemen en het risico creëren van grote transacties die lang duren om terug te draaien als er iets misgaat. Een optimalere benadering is het verwijderen van de oudste gegevenspartitie. Waar het verwijderen van de afzonderlijke rijen uren kan duren, kan het verwijderen van een hele partitie seconden duren.
Voordelen van query's
Partitioneren kan ook worden gebruikt om de queryprestaties te verbeteren. Een query die een filter toepast op gepartitioneerde gegevens, kan de scan beperken tot alleen de in aanmerking komende partities. Met deze filtermethode voorkomt u een volledige tabelscan en scant u alleen een kleinere subset van gegevens. Met de introductie van geclusterde columnstore-indexen zijn de prestatievoordelen van de predicaatverwijdering minder gunstig, maar in sommige gevallen kan er een voordeel zijn voor query's.
Als de tabel met verkoopgegevens bijvoorbeeld is gepartitioneerd in 36 maanden met behulp van het veld Verkoopdatum, kunnen query's die filteren op de verkoopdatum het zoeken in partities die niet overeenkomen met het filter overslaan.
Partitiegrootte aanpassen
Hoewel partitionering in sommige scenario's kan worden gebruikt om de prestaties te verbeteren, kan het maken van een tabel met te veel partities de prestaties onder bepaalde omstandigheden nadelig beïnvloeden. Deze problemen gelden met name voor geclusterde columnstore-tabellen.
Als u wilt dat partitioneren nuttig is, is het belangrijk om te weten wanneer partitionering moet worden gebruikt en het aantal partities dat moet worden gemaakt. Er is geen vaste snelle regel voor het aantal partities dat te veel is. Dit is afhankelijk van uw gegevens en het aantal partities dat u tegelijkertijd laadt. Een geslaagd partitioneringsschema heeft meestal tientallen tot honderden partities, niet duizenden.
Bij het maken van partities op geclusterde columnstore-tabellen is het belangrijk om te overwegen hoeveel rijen bij elke partitie horen. Voor optimale compressie en prestaties van geclusterde columnstore-tabellen is minimaal 1 miljoen rijen per distributie en partitie nodig. Voordat partities worden gemaakt, verdeelt een toegewezen SQL-pool elke tabel al in 60 distributies.
Elke partitionering die aan een tabel wordt toegevoegd, komt bovenop de distributies die achter de schermen zijn gemaakt. Als in dit voorbeeld de feitentabel verkoop 36 maandelijkse partities bevat en een toegewezen SQL-pool 60 distributies heeft, moet de feitentabel verkoop 60 miljoen rijen per maand bevatten, of 2,1 miljard rijen wanneer alle maanden zijn gevuld. Als een tabel minder dan het aanbevolen minimum aantal rijen per partitie bevat, kunt u overwegen om minder partities te gebruiken om het aantal rijen per partitie te verhogen.
Zie het artikel Indexering voor meer informatie, dat query's bevat waarmee de kwaliteit van columnstore-clusterindexen kan worden beoordeeld.
Syntaxisverschillen met SQL Server
Toegewezen SQL-pool introduceert een manier om partities te definiëren die eenvoudiger is dan SQL Server. Partitioneringsfuncties en -schema's worden niet gebruikt in een toegewezen SQL-pool, zoals in SQL Server. In plaats daarvan hoeft u alleen de gepartitioneerde kolom en de grenspunten te identificeren.
Hoewel de syntaxis van partitioneren enigszins kan afwijken van SQL Server, zijn de basisconcepten hetzelfde. SQL Server en toegewezen SQL-pool ondersteunen één partitiekolom per tabel, die een partitiebereik kan hebben. Zie Gepartitioneerde tabellen en indexen voor meer informatie over partitioneren.
In het volgende voorbeeld wordt de instructie CREATE TABLE gebruikt om de FactInternetSales tabel te partitioneren in de OrderDateKey kolom:
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
)
)
);
Partities migreren vanuit SQL Server
Als u SQL Server partitiedefinities wilt migreren naar een toegewezen SQL-pool, gaat u als volgende te werk:
- Verwijder het SQL Server partitieschema.
- Voeg de definitie van de partitiefunctie toe aan uw CREATE TABLE.
Als u een gepartitioneerde tabel migreert vanuit een SQL Server-exemplaar, kan de volgende SQL u helpen het aantal rijen in elke partitie te bepalen. Houd er rekening mee dat als dezelfde partitioneringsgranulariteit wordt gebruikt in een toegewezen SQL-pool, het aantal rijen per partitie met een factor 60 afneemt.
-- 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];
Schakelen tussen partities
Toegewezen SQL-pool biedt ondersteuning voor het splitsen, samenvoegen en schakelen tussen partities. Elk van deze functies wordt uitgevoerd met behulp van de instructie ALTER TABLE .
Als u wilt schakelen tussen partities tussen twee tabellen, moet u ervoor zorgen dat de partities op hun respectieve grenzen zijn afgestemd en dat de tabeldefinities overeenkomen. Omdat er geen controlebeperkingen beschikbaar zijn om het bereik van waarden in een tabel af te dwingen, moet de brontabel dezelfde partitiegrenzen bevatten als de doeltabel. Als de partitiegrenzen niet hetzelfde zijn, mislukt de partitieswitch omdat de partitiemetagegevens niet worden gesynchroniseerd.
Een partitiesplitsing vereist dat de respectieve partitie (niet noodzakelijkerwijs de hele tabel) leeg is als de tabel een geclusterde columnstore-index (CCI) heeft. Andere partities in dezelfde tabel kunnen gegevens bevatten. Een partitie die gegevens bevat, kan niet worden gesplitst. Dit resulteert in een fout: 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. als tijdelijke oplossing voor het splitsen van een partitie die gegevens bevat, raadpleegt u Een partitie die gegevens bevat splitsen.
Een partitie splitsen die gegevens bevat
De meest efficiënte methode voor het splitsen van een partitie die al gegevens bevat, is het gebruik van een CTAS -instructie. Als de gepartitioneerde tabel een geclusterde columnstore is, moet de tabelpartitie leeg zijn voordat deze kan worden gesplitst.
In het volgende voorbeeld wordt een gepartitioneerde columnstore-tabel gemaakt. Er wordt één rij in elke partitie ingevoegd:
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);
Met de volgende query wordt het aantal rijen gevonden met behulp van de sys.partitions catalogusweergave:
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';
De volgende splitsopdracht ontvangt een foutbericht:
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.
U kunt echter gebruiken CTAS om een nieuwe tabel te maken voor het opslaan van de gegevens.
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;
Als de partitiegrenzen zijn uitgelijnd, is een switch toegestaan. Hierdoor blijft de brontabel achter met een lege partitie die u vervolgens kunt splitsen.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
U hoeft alleen nog de gegevens uit te lijnen op de nieuwe partitiegrenzen met behulp van CTASen de gegevens vervolgens terug te zetten in de hoofdtabel.
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;
Zodra u de verplaatsing van de gegevens hebt voltooid, is het een goed idee om de statistieken in de doeltabel te vernieuwen. Het bijwerken van statistieken zorgt ervoor dat de statistieken nauwkeurig de nieuwe distributie van de gegevens in hun respectieve partities weerspiegelen.
UPDATE STATISTICS [dbo].[FactInternetSales];
Ten slotte, in het geval van een eenmalige partitieswitch om gegevens te verplaatsen, kunt u de tabellen verwijderen die zijn gemaakt voor de partitieswitch, FactInternetSales_20000101_20010101 en FactInternetSales_20000101. U kunt ook lege tabellen bewaren voor reguliere, geautomatiseerde partitieswitches.
Nieuwe gegevens laden in partities die gegevens bevatten in één stap
Het laden van gegevens in partities met schakelen tussen partities is een handige manier om nieuwe gegevens op te zetten in een tabel die niet zichtbaar is voor gebruikers. Het kan lastig zijn op drukke systemen om te gaan met het vergrendelingsconflict dat gepaard gaat met het schakelen tussen partities.
Om de bestaande gegevens in een partitie te wissen, was vroeger een ALTER TABLE vereist om de gegevens over te schakelen. Vervolgens was er nog een ALTER TABLE vereiste om de nieuwe gegevens in te schakelen.
In een toegewezen SQL-pool wordt de TRUNCATE_TARGET optie ondersteund in de ALTER TABLE opdracht . Met TRUNCATE_TARGET de ALTER TABLE opdracht worden bestaande gegevens in de partitie overschreven met nieuwe gegevens. Hieronder ziet u een voorbeeld waarin wordt gebruikt CTAS om een nieuwe tabel met de bestaande gegevens te maken, nieuwe gegevens in te voegen en vervolgens alle gegevens terug te zetten in de doeltabel, waarbij de bestaande gegevens worden overschreven.
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);
Broncodebeheer voor tabelpartitionering
Notitie
Als uw hulpprogramma voor broncodebeheer niet is geconfigureerd om partitieschema's te negeren, kan het wijzigen van het schema van een tabel om partities bij te werken ertoe leiden dat een tabel wordt verwijderd en opnieuw wordt gemaakt als onderdeel van de implementatie, wat mogelijk niet haalbaar is. Een aangepaste oplossing voor het implementeren van een dergelijke wijziging, zoals hieronder beschreven, kan nodig zijn. Controleer of uw hulpprogramma voor continue integratie/continue implementatie (CI/CD) dit toestaat. Zoek in SQL Server Data Tools (SSDT) naar de geavanceerde publicatie-instellingen 'Partitieschema's negeren' om te voorkomen dat een gegenereerd script ervoor zorgt dat een tabel wordt verwijderd en opnieuw wordt gemaakt.
Dit voorbeeld is handig bij het bijwerken van partitieschema's van een lege tabel. Als u voortdurend partitiewijzigingen wilt implementeren in een tabel met gegevens, volgt u de stappen in Een partitie splitsen die gegevens bevat naast de implementatie om gegevens tijdelijk uit elke partitie te verplaatsen voordat u de partitie SPLIT RANGE toepast. Dit is nodig omdat het CI/CD-hulpprogramma niet weet welke partities gegevens bevatten.
Als u wilt voorkomen dat uw tabeldefinitie roest in uw broncodebeheersysteem, kunt u de volgende aanpak overwegen:
De tabel maken als een gepartitioneerde tabel, maar zonder partitiewaarden
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 () ) );SPLITde tabel als onderdeel van het implementatieproces:-- 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;
Met deze benadering blijft de code in broncodebeheer statisch en mogen de partitiegrenswaarden dynamisch zijn; ontwikkelen met de SQL-pool in de loop van de tijd.
Volgende stappen
Zie de artikelen op Tabeloverzicht voor meer informatie over het ontwikkelen van tabellen.