Indexen op toegewezen SQL-pooltabellen in Azure Synapse Analytics

Aanbevelingen en voorbeelden voor het indexeren van tabellen in toegewezen SQL-pool in Azure Synapse Analytics.

Indextypen

Toegewezen SQL-pool biedt verschillende indexeringsopties, waaronder geclusterde columnstore-indexen, geclusterde indexen en niet-geclusterde indexenen een niet-indexoptie die ook wel heap wordt genoemd.

Als u een tabel met een index wilt maken, bekijkt u de documentatie CREATE TABLE (toegewezen SQL pool).

Geclusterde columnstore-indexen

Met toegewezen SQL maakt u standaard een geclusterde columnstore-index wanneer er geen indexopties zijn opgegeven in een tabel. Geclusterde columnstore-tabellen bieden zowel het hoogste niveau van gegevenscompressie als de beste algemene queryprestaties. Geclusterde columnstore-tabellen presteren over het algemeen beter dan geclusterde index- of heap-tabellen en zijn meestal de beste keuze voor grote tabellen. Daarom is geclusterde columnstore de beste plek om te beginnen wanneer u niet zeker weet hoe u uw tabel indexeert.

Als u een geclusterde columnstore-tabel wilt maken, geeft u CLUSTERED COLUMNSTORE INDEX op in de WITH-component of laat u de WITH-component uitgeschakeld:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Er zijn enkele scenario's waarbij geclusterde columnstore mogelijk geen goede optie is:

• Columnstore-tabellen bieden geen ondersteuning voor varchar(max), nvarchar(max) en varbinary(max). Overweeg in plaats daarvan heap of geclusterde index.
• Columnstore-tabellen zijn mogelijk minder efficiënt voor tijdelijke gegevens. Overweeg heap en misschien zelfs tijdelijke tabellen.
• Kleine tabellen met minder dan 60 miljoen rijen. Overweeg heap-tabellen.

Heap-tabellen

Wanneer u tijdelijk gegevens in een toegewezen pool SQL landing, kan het zijn dat het gebruik van een heap-tabel het algehele proces sneller maakt. Dit komt doordat laden naar heaps sneller zijn dan voor indextabellen en in sommige gevallen de volgende lees-uit-de-cache kan worden uitgevoerd. Als u gegevens alleen laadt om ze te faseeren voordat u meer transformaties gaat uitvoeren, is het laden van de tabel naar heap-tabel veel sneller dan het laden van de gegevens naar een geclusterde columnstore-tabel. Daarnaast laadt het laden van gegevens naar een tijdelijke tabel sneller dan het laden van een tabel naar permanente opslag. Nadat de gegevens zijn geladen, kunt u indexen in de tabel maken voor snellere queryprestaties.

Clusterkolomkolomtabellen krijgen een optimale compressie zodra er meer dan 60 miljoen rijen zijn. Voor kleine opzoektabellen, minder dan 60 miljoen rijen, kunt u heap of geclusterde index gebruiken voor snellere queryprestaties.

Als u een heap-tabel wilt maken, geeft u heap op in de WITH-component:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Geclusterde en niet-geclusterde indexen

Geclusterde indexen presteren mogelijk beter dan geclusterde columnstore-tabellen wanneer één rij snel moet worden opgehaald. Voor query's waarbij een enkele of zeer weinig rijzoekactie is vereist om zeer snel uit te voeren, kunt u een geclusterde of niet-geclusterde secundaire index overwegen. Het nadeel van het gebruik van een geclusterde index is dat alleen query's die hiervan profiteren een zeer selectief filter op de geclusterde indexkolom gebruiken. Om het filter op andere kolommen te verbeteren, kan een niet-geclusterde index worden toegevoegd aan andere kolommen. Elke index die aan een tabel wordt toegevoegd, voegt echter zowel ruimte als verwerkingstijd toe aan de belasting.

Als u een geclusterde indextabel wilt maken, geeft u clustered INDEX op in de WITH-component:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Als u een niet-geclusterde index aan een tabel wilt toevoegen, gebruikt u de volgende syntaxis:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Geclusterde columnstore-indexen optimaliseren

Geclusterde columnstore-tabellen worden in gegevens ingedeeld in segmenten. Een hoge segmentkwaliteit is essentieel voor het bereiken van optimale queryprestaties in een columnstore-tabel. Segmentkwaliteit kan worden gemeten door het aantal rijen in een gecomprimeerde rijgroep. Segmentkwaliteit is het meest optimaal wanneer er ten minste 100.000 rijen per gecomprimeerde rijgroep zijn en de prestaties verbeteren, omdat het aantal rijen per rijgroep 1.048.576 rijen benadert, wat de meeste rijen zijn die een rijgroep kan bevatten.

De onderstaande weergave kan op uw systeem worden gemaakt en gebruikt om de gemiddelde rijen per rijgroep te berekenen en eventuele suboptimale columnstore-indexen voor clusters te identificeren. De laatste kolom in deze weergave genereert een SQL-instructie die kan worden gebruikt om uw indexen opnieuw te bouwen.

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]
,    COUNT(DISTINCT rg.[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.[pdw_nodes_column_store_row_groups] 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_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]
GROUP BY
        s.[name]
,       t.[name];

Nu u de weergave hebt gemaakt, kunt u deze query uitvoeren om tabellen met rijgroepen met minder dan 100.000 rijen te identificeren. U kunt de drempelwaarde van 100.000 verhogen als u op zoek bent naar een optimale segmentkwaliteit.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Zodra u de query hebt uitgevoerd, kunt u de gegevens bekijken en de resultaten analyseren. In deze tabel wordt uitgelegd waar u in uw rijgroepanalyse naar moet zoeken.

Gevolgen van indexonderhoud

De kolom in de weergave bevat een instructie die kan worden gebruikt Rebuild_Index_SQL vColumnstoreDensity om uw ALTER INDEX REBUILD indexen opnieuw te bouwen. Zorg er bij het herbouwen van uw indexen voor dat u voldoende geheugen toekent aan de sessie die uw index herbouwt. Als u dit wilt doen, verhoogt u de resourceklasse van een gebruiker die machtigingen heeft om de index voor deze tabel opnieuw te bouwen naar het aanbevolen minimum. Zie Indexen herbouwen om de segmentkwaliteit te verbeteren verder in dit artikel voor een voorbeeld.

Voor een tabel met een geordende geclusterde columnstore-index worden de gegevens opnieuw gesorteerd ALTER INDEX REBUILD met behulp van tempdb. Tempdb bewaken tijdens herbouwbewerkingen. Als u meer tempdb-ruimte nodig hebt, schaalt u de databasegroep omhoog. Weer omlaag schalen zodra de herbouw van de index is voltooid.

Voor een tabel met een geordende geclusterde columnstore-index worden ALTER INDEX REORGANIZE de gegevens niet opnieuw gesorteerd. Gebruik om gegevens opnieuw te ALTER INDEX REBUILD sorteren.

Zie Prestaties afstemmen met geordende geclusterde columnstore-index voor meer informatie over geordende geclusterde columnstore-indexen.

Oorzaken van slechte kwaliteit voor columnstore-indexen

Als u tabellen met slechte segmentkwaliteit hebt geïdentificeerd, wilt u de hoofdoorzaak identificeren. Hieronder vindt u enkele andere veelvoorkomende oorzaken van slechte segmentkwaliteit:

1. Geheugendruk bij het bouwen van de index
2. Groot aantal DML-bewerkingen
3. Kleine of lastige belastingsbewerkingen
4. Te veel partities

Deze factoren kunnen ertoe leiden dat een columnstore-index aanzienlijk minder heeft dan de optimale 1 miljoen rijen per rijgroep. Ze kunnen er ook voor zorgen dat rijen naar de deltarijgroep gaan in plaats van naar een gecomprimeerde rijgroep.

Geheugendruk bij het bouwen van de index

Het aantal rijen per gecomprimeerde rijgroep is rechtstreeks gerelateerd aan de breedte van de rij en de hoeveelheid geheugen die beschikbaar is om de rijgroep te verwerken. Wanneer rijen naar columnstore-tabellen worden geschreven onder geheugendruk, kan dit ten koste gaan van de kwaliteit van columnstore-segmenten. Daarom is het best practice de sessie die naar uw columnstore-indextabellen schrijft toegang te geven tot zoveel mogelijk geheugen. Omdat er een balans is tussen geheugen en gelijktijdigheid, zijn de richtlijnen voor de juiste geheugentoewijzing afhankelijk van de gegevens in elke rij van uw tabel, de datawarehouse-eenheden die zijn toegewezen aan uw systeem en het aantal gelijktijdigheidssleuven dat u aan de sessie kunt geven die gegevens naar uw tabel schrijft.

Groot aantal DML-bewerkingen

Een groot aantal DML-bewerkingen die rijen bijwerken en verwijderen, kan inefficiëntie in de columnstore introduceren. Dit geldt met name wanneer de meeste rijen in een rijgroep worden gewijzigd.

• Als u een rij uit een gecomprimeerde rijgroep verwijderd, wordt de rij alleen logisch als verwijderd markeert. De rij blijft in de gecomprimeerde rijgroep totdat de partitie of tabel opnieuw is opgebouwd.
• Als u een rij invoegt, wordt de rij toegevoegd aan een interne rowstore-tabel die een deltarijgroep wordt genoemd. De ingevoegde rij wordt pas geconverteerd naar columnstore als de deltarijgroep vol is en is gemarkeerd als gesloten. Rijgroepen worden gesloten zodra ze de maximale capaciteit van 1.048.576 rijen hebben bereikt.
• Het bijwerken van een rij in columnstore-indeling wordt verwerkt als een logische delete en vervolgens een invoeging. De ingevoegde rij kan worden opgeslagen in de Delta Store.

Batchbewerkingen voor bijwerken en invoegen die de bulkdrempel van 102.400 rijen per distributie met partitieverdeling overschrijden, gaan rechtstreeks naar de columnstore-indeling. Uitgaande van een gelijkmatige verdeling moet u echter meer dan 6,144 miljoen rijen in één bewerking wijzigen om dit te laten gebeuren. Als het aantal rijen voor een bepaalde partitieverdeling kleiner is dan 102.400, gaan de rijen naar het Delta-opslagcentrum en blijven ze totdat er voldoende rijen zijn ingevoegd of gewijzigd om de rijgroep te sluiten of de index opnieuw is opgebouwd.

Kleine of lastige belastingsbewerkingen

Kleine belastingen die naar toegewezen SQL worden ook wel lastige belastingen genoemd. Ze vertegenwoordigen doorgaans een bijna constante stroom van gegevens die door het systeem worden opgenomen. Omdat deze stroom echter bijna doorlopend is, is het aantal rijen niet erg groot. Vaak liggen de gegevens aanzienlijk onder de drempelwaarde die is vereist voor een rechtstreekse belasting naar columnstore-indeling.

In dergelijke situaties is het vaak beter om de gegevens eerst in Azure Blob Storage te krijgen en ze te laten verzamelen voordat ze worden geladen. Deze techniek wordt vaak microbatching genoemd.

Te veel partities

Een ander ding om rekening mee te houden, is de impact van partitioneren op uw geclusterde columnstore-tabellen. Vóór het partitioneren verdeelt SQL-pool uw gegevens al in 60 databases. Partitioneren verdeelt uw gegevens verder. Als u uw gegevens partitioneert, moet u er rekening mee houden dat elke partitie ten minste 1 miljoen rijen nodig heeft om te kunnen profiteren van een geclusterde columnstore-index. Als u uw tabel partitioneert in 100 partities, heeft uw tabel ten minste 6 miljard rijen nodig om te profiteren van een geclusterde columnstore-index (60 distributies 100 partities 1 miljoen rijen). Als uw tabel met 100 partities geen 6 miljard rijen heeft, vermindert u het aantal partities of overweeg in plaats daarvan een heap-tabel te gebruiken.

Nadat uw tabellen met enkele gegevens zijn geladen, volgt u de onderstaande stappen om tabellen te identificeren en opnieuw te bouwen met suboptimalen van geclusterde columnstore-indexen.

Indexen herbouwen om segmentkwaliteit te verbeteren

Stap 1: identificeer of maak een gebruiker die de juiste resourceklasse gebruikt

Een snelle manier om de kwaliteit van segmenten onmiddellijk te verbeteren, is door de index opnieuw te bouwen. De SQL die door de bovenstaande weergave wordt geretourneerd, bevat de instructie ALTER INDEX REBUILD, die kan worden gebruikt om uw indexen opnieuw te bouwen. Zorg er bij het herbouwen van uw indexen voor dat u voldoende geheugen toekent aan de sessie die uw index herbouwt. Als u dit wilt doen, verhoogt u de resourceklasse van een gebruiker die machtigingen heeft om de index voor deze tabel opnieuw te bouwen tot het aanbevolen minimum.

Hieronder vindt u een voorbeeld van hoe u meer geheugen kunt toewijzen aan een gebruiker door de resourceklasse te vergroten. Zie Resourceklassen voor workloadbeheer voor informatie over het werken met resourceklassen.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Stap 2: geclusterde columnstore-indexen herbouwen met een hogere resourceklassegebruiker

Meld u aan als de gebruiker uit stap 1 ( ), die nu een hogere resourceklasse gebruikt LoadUser en voer de ALTER INDEX-instructies uit. Zorg ervoor dat deze gebruiker de machtiging ALTER heeft voor de tabellen waarin de index opnieuw wordt opgebouwd. Deze voorbeelden laten zien hoe u de hele columnstore-index opnieuw bouwt of hoe u één partitie opnieuw bouwt. Bij grote tabellen is het praktischer om indexen één partitie tegelijk opnieuw te bouwen.

U kunt de tabel ook kopiëren naar een nieuwe tabel met CTAS in plaats van de index opnieuw te bouwen. Welke manier is het beste? Voor grote hoeveelheden gegevens is CTAS meestal sneller dan ALTER INDEX. Voor kleinere hoeveelheden gegevens is ALTER INDEX eenvoudiger te gebruiken en hoeft u de tabel niet om te wisselen.

-- 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);

Het herbouwen van een index in SQL pool is een offlinebewerking. Zie de sectie ALTER INDEX REBUILD in Columnstore Indexes Defragmentationen ALTER INDEX voor meer informatie over het herbouwen van indexen.

Stap 3: controleren of de kwaliteit van geclusterde columnstore-segmenten is verbeterd

Opnieuw uitvoeren van de query die tabel met slechte segmentkwaliteit geïdentificeerd en controleren of segmentkwaliteit is verbeterd. Als de segmentkwaliteit niet is verbeterd, kan het zijn dat de rijen in uw tabel extra breed zijn. Overweeg het gebruik van een hogere resourceklasse of DWU bij het herbouwen van uw indexen.

Indexen herbouwen met CTAS en partition switching

In dit voorbeeld wordt de CREATE TABLE CTAS-instructie (AS SELECT) en partition switching gebruikt om een tabelpartitie opnieuw te bouwen.

-- 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);

Zie Using partitions in dedicated SQL pool (Partities gebruiken intoegewezen SQL pool) voor meer informatie over het opnieuw maken van partities met CTAS.

Volgende stappen

Zie Tabellen ontwikkelen voor meer informatie over het ontwikkelen van tabellen.