Index för dedikerade SQL-pooltabeller i Azure Synapse Analytics
Rekommendationer och exempel för indexering av tabeller i en dedikerad SQL-pool i Azure Synapse Analytics.
Indextyper
Dedikerad SQL-pool erbjuder flera indexeringsalternativ, inklusive grupperade kolumnlagringsindex, klustrade index och icke-indexerade index och ett icke-indexalternativ som även kallas heap.
Information om hur du skapar en tabell med ett index finns i dokumentationen för CREATE TABLE (dedikerad SQL-pool).
Grupperade kolumnlagringsindex
Som standard skapar en dedikerad SQL-pool ett grupperat kolumnlagringsindex när inga indexalternativ anges i en tabell. Grupperade columnstore-tabeller erbjuder både den högsta nivån av datakomprimering och bästa övergripande frågeprestanda. Grupperade kolumnlagringstabeller överträffar vanligtvis grupperade index- eller heap-tabeller och är vanligtvis det bästa valet för stora tabeller. Därför är klustrat kolumnarkiv det bästa stället att börja på när du är osäker på hur du ska indexera tabellen.
Om du vill skapa en klustrad kolumnlagringstabell anger du CLUSTERED COLUMNSTORE INDEX bara i WITH-satsen eller lämnar WITH-satsen av:
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( CLUSTERED COLUMNSTORE INDEX );
Det finns några scenarier där klustrat kolumnarkiv kanske inte är ett bra alternativ:
- Columnstore-tabeller stöder inte varchar(max), nvarchar(max) och varbinary(max). Överväg heap eller klustrat index i stället.
- Kolumnlagringstabeller kan vara mindre effektiva för tillfälliga data. Överväg heap och kanske till och med tillfälliga tabeller.
- Små tabeller med mindre än 60 miljoner rader. Överväg heap-tabeller.
Heap-tabeller
När du tillfälligt landar data i en dedikerad SQL-pool kan det hända att en heaptabell gör den övergripande processen snabbare. Det beror på att inläsningar till heaps är snabbare än till indextabeller och i vissa fall kan efterföljande läsning göras från cacheminnet. Om du läser in data bara för att mellanlagra dem innan du kör fler transformeringar går det mycket snabbare att läsa in tabellen till heaptabellen än att läsa in data till en klustrad kolumnlagringstabell. Dessutom läses in data till en tillfällig tabell snabbare än när en tabell läses in till permanent lagring. När data har lästs in kan du skapa index i tabellen för snabbare frågeprestanda.
Klusterkolumnlagringstabeller börjar uppnå optimal komprimering när det finns mer än 60 miljoner rader. För små uppslagstabeller, mindre än 60 miljoner rader, bör du överväga att använda HEAP eller klustrade index för snabbare frågeprestanda.
Om du vill skapa en heap-tabell anger du helt enkelt HEAP i WITH-satsen:
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( HEAP );
Kommentar
Om du ofta utför INSERT, , eller DELETE åtgärder i en heap-tabell rekommenderar vi att du inkluderar återskapande av tabeller i underhållsschemat med hjälp ALTER TABLE av UPDATEkommandot . Exempel: ALTER TABLE [SchemaName].[TableName] REBUILD Den här metoden bidrar till minskad fragmentering, vilket resulterar i bättre prestanda under läsåtgärder.
Grupperade och icke-grupperade index
Grupperade index kan överträffa klustrade kolumnlagringstabeller när en enskild rad snabbt behöver hämtas. För frågor där en enda eller mycket få radsökning krävs för att utföra med extrem hastighet bör du överväga ett klustrat index eller ett sekundärt index som inte är grupperat. Nackdelen med att använda ett klustrade index är att endast frågor som gynnar är de som använder ett mycket selektivt filter i den klustrade indexkolumnen. För att förbättra filtret för andra kolumner kan ett icke-grupperat index läggas till i andra kolumner. Varje index som läggs till i en tabell lägger dock till både utrymme och bearbetningstid för inläsningar.
Om du vill skapa en klustrad indextabell anger du helt enkelt CLUSTERED INDEX i WITH-satsen:
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( CLUSTERED INDEX (id) );
Om du vill lägga till ett icke-grupperat index i en tabell använder du följande syntax:
CREATE INDEX zipCodeIndex ON myTable (zipCode);
Optimera grupperade kolumnlagringsindex
Grupperade kolumnlagringstabeller organiserar data i segment. Det är viktigt att ha hög segmentkvalitet för att uppnå optimala frågeprestanda i en kolumnlagringstabell. Segmentkvaliteten kan mätas utifrån antalet rader i en komprimerad radgrupp. Segmentkvaliteten är mest optimal där det finns minst 100 K rader per komprimerad radgrupp och får prestanda när antalet rader per radgrupp närmar sig 1 048 576 rader, vilket är de flesta rader som en radgrupp kan innehålla.
Vyn nedan kan skapas och användas i systemet för att beräkna de genomsnittliga raderna per radgrupp och identifiera eventuella suboptimala klusterkolumnlagringsindex. Den sista kolumnen i den här vyn genererar en SQL-instruktion som kan användas för att återskapa dina index.
CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
GETDATE() AS [execution_date]
, DB_Name() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, MAX(p.partition_number) AS [table_partition_count]
, SUM(rg.[total_rows]) AS [row_count_total]
, SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id]) AS [row_count_per_distribution_MAX]
, CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
, SUM(CASE WHEN rg.[State] = 0 THEN 1 ELSE 0 END) AS [INVISIBLE_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE 0 END) AS [INVISIBLE_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] ELSE NULL END) AS [INVISIBLE_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE NULL END) AS [OPEN_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows]
, MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE NULL END) AS [CLOSED_rowgroup_rows_AVG]
, SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count]
, SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows]
, SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
, MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_MIN]
, MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_MAX]
, AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE NULL END) AS [COMPRESSED_rowgroup_rows_AVG]
, 'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;' AS [Rebuild_Index_SQL]
FROM sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_permanent_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
JOIN sys.[partitions] p ON P.object_id = t.object_id
GROUP BY
s.[name]
, t.[name];
Nu när du har skapat vyn kör du den här frågan för att identifiera tabeller med radgrupper med mindre än 100 K rader. Du kanske vill öka tröskelvärdet på 100 K om du vill ha mer optimal segmentkvalitet.
SELECT *
FROM [dbo].[vColumnstoreDensity]
WHERE COMPRESSED_rowgroup_rows_AVG < 100000
OR INVISIBLE_rowgroup_rows_AVG < 100000;
När du har kört frågan kan du börja titta på data och analysera dina resultat. Den här tabellen förklarar vad du ska leta efter i radgruppsanalysen.
| Column | Så här använder du dessa data |
|---|---|
| [table_partition_count] | Om tabellen är partitionerad kan du förvänta dig att se högre antal öppna radgrupper. Varje partition i fördelningen kan i teorin ha en öppen radgrupp associerad med den. Ta med detta i din analys. En liten tabell som har partitionerats kan optimeras genom att helt ta bort partitioneringen eftersom detta skulle förbättra komprimering. |
| [row_count_total] | Totalt antal rader för tabellen. Du kan till exempel använda det här värdet för att beräkna procentandelen rader i komprimerat tillstånd. |
| [row_count_per_distribution_MAX] | Om alla rader är jämnt fördelade skulle det här värdet vara målantalet rader per distribution. Jämför det här värdet med compressed_rowgroup_count. |
| [COMPRESSED_rowgroup_rows] | Totalt antal rader i columnstore-format för tabellen. |
| [COMPRESSED_rowgroup_rows_AVG] | Om det genomsnittliga antalet rader är betydligt mindre än det maximala antalet rader för en radgrupp kan du överväga att använda CTAS eller ALTER INDEX REBUILD för att komprimera data igen |
| [COMPRESSED_rowgroup_count] | Antal radgrupper i kolumnarkivformat. Om det här talet är mycket högt i förhållande till tabellen är det en indikator på att kolumnlagringsdensiteten är låg. |
| [COMPRESSED_rowgroup_rows_DELETED] | Rader tas bort logiskt i kolumnlagringsformat. Om talet är högt i förhållande till tabellstorleken bör du överväga att återskapa partitionen eller återskapa indexet eftersom det tar bort dem fysiskt. |
| [COMPRESSED_rowgroup_rows_MIN] | Använd detta med kolumnerna AVG och MAX för att förstå intervallet med värden för radgrupperna i ditt kolumnarkiv. Ett lågt tal över belastningströskelvärdet (102 400 per partitionsjusterad distribution) tyder på att optimeringar är tillgängliga i databelastningen |
| [COMPRESSED_rowgroup_rows_MAX] | Som ovan |
| [OPEN_rowgroup_count] | Öppna radgrupper är normala. Man kan rimligen förvänta sig en OPEN-radgrupp per tabelldistribution (60). Överdrivna tal tyder på att data läses in mellan partitioner. Dubbelkolla partitioneringsstrategin för att se till att den är ljud |
| [OPEN_rowgroup_rows] | Varje radgrupp kan ha högst 1 048 576 rader. Använd det här värdet om du vill se hur fullständiga de öppna radgrupperna är för närvarande |
| [OPEN_rowgroup_rows_MIN] | Öppna grupper anger att data antingen läses in i tabellen eller att den tidigare belastningen spilldes över återstående rader i den här radgruppen. Använd kolumnerna MIN, MAX och AVG för att se hur mycket data som finns i ÖPPNA radgrupper. För små tabeller kan det vara 100 % av alla data! I vilket fall ALTER INDEX REBUILD för att tvinga data till columnstore. |
| [OPEN_rowgroup_rows_MAX] | Som ovan |
| [OPEN_rowgroup_rows_AVG] | Som ovan |
| [CLOSED_rowgroup_rows] | Titta på de stängda radgruppsraderna som en sanitetskontroll. |
| [CLOSED_rowgroup_count] | Antalet stängda radgrupper bör vara lågt om några visas alls. Stängda radgrupper kan konverteras till komprimerade radgrupper med hjälp av ALTER INDEX ... ORDNA om kommandot. Detta krävs dock normalt inte. Stängda grupper konverteras automatiskt till kolumnlagringsradgrupper av bakgrundsprocessen "tuppelns mover". |
| [CLOSED_rowgroup_rows_MIN] | Stängda radgrupper bör ha en mycket hög fyllningshastighet. Om fyllningshastigheten för en sluten radgrupp är låg krävs ytterligare analys av kolumnarkivet. |
| [CLOSED_rowgroup_rows_MAX] | Som ovan |
| [CLOSED_rowgroup_rows_AVG] | Som ovan |
| [Rebuild_Index_SQL] | SQL för att återskapa columnstore-index för en tabell |
Påverkan av indexunderhåll
Kolumnen Rebuild_Index_SQL i vColumnstoreDensity vyn innehåller en ALTER INDEX REBUILD instruktion som kan användas för att återskapa dina index. När du återskapar indexen måste du allokera tillräckligt med minne till sessionen som återskapar ditt index. Det gör du genom att öka resursklassen för en användare som har behörighet att återskapa indexet i den här tabellen till det rekommenderade minimumet. Ett exempel finns i Återskapa index för att förbättra segmentkvaliteten senare i den här artikeln.
För en tabell med ett ordnat grupperat kolumnlagringsindex ALTER INDEX REBUILD sorteras data på nytt med tempdb. Övervaka tempdb under återskapandeåtgärder. Om du behöver mer tempdb-utrymme skalar du upp databaspoolen. Skala ned igen när indexet har återskapats.
För en tabell med ett ordnat grupperat kolumnlagringsindex ALTER INDEX REORGANIZE sorteras inte data på nytt. Om du vill sortera om data använder du ALTER INDEX REBUILD.
Mer information om sorterade grupperade kolumnlagringsindex finns i Prestandajustering med ordnat grupperat kolumnlagringsindex.
Causes of poor columnstore index quality (Orsaker till låg columnstore-indexkvalitet)
Om du har identifierat tabeller med dålig segmentkvalitet vill du identifiera rotorsaken. Nedan visas några andra vanliga orsaker till dålig segmentkvalitet:
- Minnestryck när index skapades
- Stora mängder DML-åtgärder
- Små eller sippriga inläsningsåtgärder
- För många partitioner
Dessa faktorer kan göra att ett kolumnlagringsindex har betydligt mindre än de optimala 1 miljon raderna per radgrupp. De kan också leda till att rader går till deltaradsgruppen i stället för en komprimerad radgrupp.
Minnestryck när index skapades
Antalet rader per komprimerad radgrupp är direkt relaterat till radens bredd och mängden minne som är tillgängligt för att bearbeta radgruppen. När rader skrivs till columnstore-tabeller när minnet är hårt belastat, kan columnstore-segmentens kvalitet påverkas. Därför är bästa praxis att ge den session som skriver till dina columnstore-indextabeller åtkomst till så mycket minne som möjligt. Eftersom det finns en kompromiss mellan minne och samtidighet beror vägledningen för rätt minnesallokering på data i varje rad i tabellen, de informationslagerenheter som allokerats till systemet och antalet samtidighetsfack som du kan ge till sessionen som skriver data till tabellen.
Stora mängder DML-åtgärder
En stor mängd DML-åtgärder som uppdaterar och tar bort rader kan medföra ineffektivitet i kolumnarkivet. Detta gäller särskilt när de flesta raderna i en radgrupp ändras.
- Om du tar bort en rad från en komprimerad radgrupp markeras raden bara som borttagen logiskt. Raden förblir i den komprimerade radgruppen tills partitionen eller tabellen återskapas.
- Om du infogar en rad läggs raden till i en intern radlagringstabell som kallas för en deltaradsgrupp. Den infogade raden konverteras inte till columnstore förrän deltaradsgruppen är full och markeras som stängd. Radgrupper stängs när de når den maximala kapaciteten på 1 048 576 rader.
- Uppdatering av en rad i columnstore-format bearbetas som en logisk borttagning och sedan en infogning. Den infogade raden kan lagras i deltaarkivet.
Batchindelade åtgärder för uppdatering och infogning som överskrider masströskelvärdet på 102 400 rader per partitionsjusterad distribution går direkt till kolumnlagringsformatet. Men om du antar en jämn fördelning måste du ändra mer än 6,144 miljoner rader i en enda åtgärd för att detta ska ske. Om antalet rader för en viss partitionsjusterad fördelning är mindre än 102 400 går raderna till deltaarkivet och stannar kvar tills tillräckligt många rader har infogats eller ändrats för att stänga radgruppen eller om indexet har återskapats.
Små eller sippriga inläsningsåtgärder
Små belastningar som flödar till en dedikerad SQL-pool kallas ibland även för trickle-belastningar. De representerar vanligtvis en nästan konstant dataström som matas in av systemet. Men eftersom den här strömmen är nästan kontinuerlig är volymen av rader inte särskilt stor. Oftast ligger datan betydligt under det tröskelvärde som krävs för en direkt inläsning till kolumnlagringsformat.
I dessa situationer är det ofta bättre att landa data först i Azure Blob Storage och låta dem ackumuleras innan de läses in. Den här tekniken kallas ofta för mikrobatchbearbetning.
För många partitioner
En annan sak att tänka på är hur partitioneringen påverkar dina klustrade kolumnlagringstabeller. Innan partitionering delar den dedikerade SQL-poolen redan in dina data i 60 databaser. Partitionering delar ytterligare dina data. Om du partitionera dina data bör du tänka på att varje partition behöver minst 1 miljon rader för att dra nytta av ett grupperat kolumnlagringsindex. Om du partitionerade tabellen i 100 partitioner behöver tabellen minst 6 miljarder rader för att kunna dra nytta av ett grupperat kolumnlagringsindex (60 distributioner 100 partitioner 1 miljon rader). Om tabellen med 100 partitioner inte har 6 miljarder rader kan du antingen minska antalet partitioner eller använda en heaptabell i stället.
När tabellerna har lästs in med vissa data följer du stegen nedan för att identifiera och återskapa tabeller med suboptimala grupperade kolumnlagringsindex.
Återskapa index för att förbättra segmentkvaliteten
Steg 1: Identifiera eller skapa användare som använder rätt resursklass
Ett snabbt sätt att omedelbart förbättra segmentkvaliteten är att återskapa indexet. SQL som returneras av vyn ovan innehåller en ALTER INDEX REBUILD-instruktion som kan användas för att återskapa dina index. När du återskapar indexen måste du allokera tillräckligt med minne till sessionen som återskapar ditt index. Det gör du genom att öka resursklassen för en användare som har behörighet att återskapa indexet i den här tabellen till det rekommenderade minimumet.
Nedan visas ett exempel på hur du allokerar mer minne till en användare genom att öka resursklassen. Information om hur du arbetar med resursklasser finns i Resursklasser för arbetsbelastningshantering.
EXEC sp_addrolemember 'xlargerc', 'LoadUser';
Steg 2: Återskapa klustrade kolumnlagringsindex med högre resursklassanvändare
Logga in som användare från steg 1 (LoadUser), som nu använder en högre resursklass, och kör ALTER INDEX-uttrycken. Se till att den här användaren har ALTER-behörighet till tabellerna där indexet återskapas. De här exemplen visar hur du återskapar hela kolumnlagringsindexet eller hur du återskapar en enskild partition. I stora tabeller är det mer praktiskt att återskapa index en enskild partition i taget.
I stället för att återskapa indexet kan du också kopiera tabellen till en ny tabell med hjälp av CTAS. Vilket sätt är bäst? För stora mängder data är CTAS vanligtvis snabbare än ALTER INDEX. För mindre datavolymer är ALTER INDEX enklare att använda och kräver inte att du byter ut tabellen.
-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;
-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;
-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);
Att återskapa ett index i en dedikerad SQL-pool är en offlineåtgärd. Mer information om hur du återskapar index finns i avsnittet ALTER INDEX REBUILD i Columnstore Indexes Defragmentation och ALTER INDEX.
Steg 3: Kontrollera att kvaliteten på klustrade kolumnlagersegment har förbättrats
Kör frågan som identifierade tabellen med dålig segmentkvalitet och kontrollera att segmentkvaliteten har förbättrats. Om segmentkvaliteten inte förbättras kan det bero på att raderna i tabellen är extra breda. Överväg att använda en högre resursklass eller DWU när du återskapar dina index.
Återskapa index med CTAS och partitionsväxling
I det här exemplet används instruktionen CREATE TABLE AS SELECT (CTAS) och partitionsväxling för att återskapa en tabellpartition.
-- Step 1: Select the partition of data and write it out to a new table using CTAS
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]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);
Mer information om hur du återskapar partitioner med CTAS finns i Använda partitioner i en dedikerad SQL-pool.
Nästa steg
Mer information om hur du utvecklar tabeller finns i Utveckla tabeller.