Partitionera tabeller i en dedikerad SQL-pool
Rekommendationer och exempel för att använda tabellpartitioner i en dedikerad SQL-pool.
Vad är tabellpartitioner?
Med tabellpartitioner kan du dela upp dina data i mindre grupper med data. I de flesta fall skapas tabellpartitioner i en datumkolumn. Partitionering stöds för alla dedikerade SQL-pooltabelltyper. inklusive klustrad kolumnlagring, klustrat index och heap. Partitionering stöds också på alla distributionstyper, inklusive både hash eller resursallokering distribuerad.
Partitionering kan gynna dataunderhåll och frågeprestanda. Om det gynnar båda eller bara en beror på hur data läses in och om samma kolumn kan användas för båda ändamålen, eftersom partitionering bara kan göras på en kolumn.
Fördelar med att läsa in
Den främsta fördelen med partitionering i en dedikerad SQL-pool är att förbättra effektiviteten och prestandan vid inläsning av data med hjälp av partitionsborttagning, växling och sammanslagning. I de flesta fall partitioneras data på en datumkolumn som är nära kopplad till den ordning som data läses in i SQL-poolen. En av de största fördelarna med att använda partitioner för att underhålla data är att undvika transaktionsloggning. Att bara infoga, uppdatera eller ta bort data kan vara den enklaste metoden, men med lite eftertanke och ansträngning kan partitionering under belastningsprocessen avsevärt förbättra prestandan.
Partitionsväxling kan användas för att snabbt ta bort eller ersätta ett avsnitt i en tabell. En faktatabell för försäljning kan till exempel innehålla data för de senaste 36 månaderna. I slutet av varje månad tas den äldsta månaden med försäljningsdata bort från tabellen. Dessa data kan tas bort med hjälp av en delete-instruktion för att ta bort data för den äldsta månaden.
Det kan dock ta för lång tid att ta bort en stor mängd data rad för rad med en borttagningsinstruktor, samt skapa en risk för stora transaktioner som tar lång tid att återställa om något går fel. En mer optimal metod är att släppa den äldsta partitionen med data. Om det kan ta timmar att ta bort enskilda rader kan det ta några sekunder att ta bort en hel partition.
Fördelar med frågor
Partitionering kan också användas för att förbättra frågeprestanda. En fråga som tillämpar ett filter på partitionerade data kan begränsa genomsökningen till endast kvalificerande partitioner. Den här filtreringsmetoden kan undvika en fullständig tabellgenomsökning och endast genomsöka en mindre delmängd data. Med introduktionen av klustrade kolumnlagringsindex är fördelarna med predikatet elimineringsprestanda mindre fördelaktiga, men i vissa fall kan det finnas en fördel med frågor.
Om tabellen försäljningsfakta till exempel är partitionerad i 36 månader med fältet försäljningsdatum kan frågor som filtrerar på försäljningsdatumet hoppa över sökning i partitioner som inte matchar filtret.
Partitionsstorlek
Partitionering kan användas för att förbättra prestanda, men att skapa en tabell med för många partitioner kan skada prestanda under vissa omständigheter. Dessa problem gäller särskilt för grupperade kolumnlagringstabeller.
För att partitionering ska vara användbart är det viktigt att förstå när partitionering ska användas och hur många partitioner som ska skapas. Det finns ingen hård snabb regel för hur många partitioner som är för många, det beror på dina data och hur många partitioner du läser in samtidigt. Ett lyckat partitioneringsschema har vanligtvis tiotals till hundratals partitioner, inte tusentals.
När du skapar partitioner på klustrade kolumnlagringstabeller är det viktigt att tänka på hur många rader som tillhör varje partition. För optimal komprimering och prestanda för grupperade kolumnlagringstabeller krävs minst 1 miljon rader per distribution och partition. Innan partitioner skapas delar den dedikerade SQL-poolen redan upp varje tabell i 60 distributioner.
All partitionering som läggs till i en tabell är utöver de distributioner som skapats i bakgrunden. Om faktatabellen för försäljning i det här exemplet innehåller 36 månadspartitioner och en dedikerad SQL-pool har 60 distributioner, bör faktatabellen för försäljning innehålla 60 miljoner rader per månad eller 2,1 miljarder rader när alla månader fylls i. Om en tabell innehåller färre än det rekommenderade minsta antalet rader per partition bör du överväga att använda färre partitioner för att öka antalet rader per partition.
Mer information finns i artikeln Indexering , som innehåller frågor som kan utvärdera kvaliteten på klusterkolumnlagringsindex.
Syntaxskillnader från SQL Server
Dedikerad SQL-pool introducerar ett sätt att definiera partitioner som är enklare än SQL Server. Partitioneringsfunktioner och scheman används inte i en dedikerad SQL-pool eftersom de finns i SQL Server. I stället behöver du bara identifiera partitionerad kolumn och gränspunkter.
Även om syntaxen för partitionering kan skilja sig något från SQL Server är de grundläggande begreppen desamma. SQL Server och dedikerad SQL-pool har stöd för en partitionskolumn per tabell, som kan vara en partition med intervall. Mer information om partitionering finns i Partitionerade tabeller och index.
I följande exempel används CREATE TABLE-instruktionenFactInternetSales för att partitionera tabellen i OrderDateKey kolumnen:
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
)
)
);
Migrera partitioner från SQL Server
Så här migrerar du SQL Server partitionsdefinitioner till en dedikerad SQL-pool:
- Eliminera SQL Server partitionsschemat.
- Lägg till partitionsfunktionsdefinitionen i CREATE TABLE.
Om du migrerar en partitionerad tabell från en SQL Server instans kan följande SQL hjälpa dig att ta reda på antalet rader i varje partition. Tänk på att om samma partitioneringskornighet används i en dedikerad SQL-pool minskar antalet rader per partition med en faktor på 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];
Partitionsväxling
Dedikerad SQL-pool stöder partitionsdelning, sammanslagning och växling. Var och en av dessa funktioner körs med alter table-instruktionen .
Om du vill växla partitioner mellan två tabeller måste du se till att partitionerna justeras mot respektive gränser och att tabelldefinitionerna matchar varandra. Eftersom kontrollbegränsningar inte är tillgängliga för att framtvinga intervallet med värden i en tabell måste källtabellen innehålla samma partitionsgränser som måltabellen. Om partitionsgränserna inte är desamma misslyckas partitionsväxeln eftersom partitionsmetadata inte synkroniseras.
En partitionsdelning kräver att respektive partition (inte nödvändigtvis hela tabellen) är tom om tabellen har ett grupperat kolumnlagringsindex (CCI). Andra partitioner i samma tabell kan innehålla data. En partition som innehåller data kan inte delas, det resulterar i fel: 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. Som en lösning för att dela en partition som innehåller data, se Så här delar du en partition som innehåller data.
Dela en partition som innehåller data
Den mest effektiva metoden för att dela en partition som redan innehåller data är att använda en CTAS -instruktion. Om den partitionerade tabellen är ett grupperat kolumnarkiv måste tabellpartitionen vara tom innan den kan delas.
I följande exempel skapas en partitionerad kolumnlagringstabell. Den infogar en rad i varje partition:
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);
Följande fråga hittar radantalet med hjälp sys.partitions av katalogvyn:
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';
Följande delade kommando får ett felmeddelande:
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.
Du kan dock använda CTAS för att skapa en ny tabell för att lagra data.
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;
När partitionsgränserna är justerade tillåts en växel. Detta lämnar källtabellen med en tom partition som du sedan kan dela.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Allt som återstår är att justera data till de nya partitionsgränserna med hjälp av CTASoch sedan växla tillbaka data till huvudtabellen.
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;
När du har slutfört dataflytten är det en bra idé att uppdatera statistiken i måltabellen. Genom att uppdatera statistik ser du till att statistiken korrekt återspeglar den nya fördelningen av data i respektive partitioner.
UPDATE STATISTICS [dbo].[FactInternetSales];
När det gäller en engångspartitionsväxling för att flytta data kan du slutligen släppa tabellerna som skapats för partitionsväxeln FactInternetSales_20000101_20010101 och FactInternetSales_20000101. Du kan också behålla tomma tabeller för vanliga, automatiserade partitionsväxlar.
Läs in nya data i partitioner som innehåller data i ett steg
Att läsa in data i partitioner med partitionsväxling är ett praktiskt sätt att mellanlagra nya data i en tabell som inte är synlig för användarna. Det kan vara svårt för upptagna system att hantera den låsningskonkurrens som är associerad med partitionsväxling.
För att rensa ut befintliga data i en partition måste ALTER TABLE du växla ut data. Sedan krävdes en annan ALTER TABLE för att växla in nya data.
I en TRUNCATE_TARGET dedikerad SQL-pool stöds alternativet i ALTER TABLE kommandot .
ALTER TABLE Kommandot TRUNCATE_TARGET skriver över befintliga data i partitionen med nya data. Nedan visas ett exempel som använder CTAS för att skapa en ny tabell med befintliga data, infogar nya data och sedan växlar alla data tillbaka till måltabellen och skriver över befintliga data.
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);
Källkontroll för tabellpartitionering
Anteckning
Om källkontrollverktyget inte har konfigurerats för att ignorera partitionsscheman kan en ändring av en tabells schema för att uppdatera partitioner leda till att en tabell tas bort och återskapas som en del av distributionen, vilket kan vara omöjligt. En anpassad lösning för att implementera en sådan ändring, enligt beskrivningen nedan, kan vara nödvändig. Kontrollera att ditt CI/CD-verktyg (Continuous Integration/Continuous Deployment) tillåter detta. I SQL Server Data Tools (SSDT) letar du efter avancerade publiceringsinställningar "Ignorera partitionsscheman" för att undvika ett genererat skript som gör att en tabell tas bort och återskapas.
Det här exemplet är användbart när du uppdaterar partitionsscheman för en tom tabell. Om du vill distribuera partitionsändringar kontinuerligt i en tabell med data följer du stegen i Så här delar du upp en partition som innehåller data tillsammans med distributionen för att tillfälligt flytta data från varje partition innan du tillämpar partitionen SPLIT RANGE. Detta är nödvändigt eftersom CI/CD-verktyget inte är medvetet om vilka partitioner som har data.
Om du vill undvika att tabelldefinitionen rostar i källkontrollsystemet kan du överväga följande metod:
Skapa tabellen som en partitionerad tabell men utan partitionsvärden
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 () ) );SPLITtabellen som en del av distributionsprocessen:-- 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;
Med den här metoden förblir koden i källkontrollen statisk och partitioneringsgränsvärdena tillåts vara dynamiska. utvecklas med SQL-poolen över tid.
Nästa steg
Mer information om hur du utvecklar tabeller finns i artiklarna i Tabellöversikt.