Delen via

Prestaties afstemmen met geordende columnstore-indexen

  • Artikel

Van toepassing op: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Door efficiënte segmentverwijdering mogelijk te maken, bieden geordende columnstore-indexen snellere prestaties door grote hoeveelheden geordende gegevens over te slaan die niet overeenkomen met het querypredicaat. Het laden van gegevens in een geordende columnstore-index en het bijhouden ervan via indexherstel kan langer duren dan in een niet-geordende index vanwege de sorteerbewerking voor gegevens, maar met geordende columnstore-indexenquery's kunnen query's later sneller worden uitgevoerd.

Wanneer gebruikers een query uitvoeren op een columnstore-tabel, controleert de optimizer de minimum- en maximumwaarden die in elk segment zijn opgeslagen. Segmenten die buiten de grenzen van het querypredicaat vallen, worden niet gelezen van schijf naar geheugen. Een query kan sneller worden voltooid als het aantal segmenten dat moet worden gelezen en de totale grootte kleiner is.

Zie Beschikbaarheid van geordende columnstore-indexenvoor meer informatie.

Zie Wat is er nieuw in columnstore-indexenvoor meer informatie over onlangs toegevoegde functies voor columnstore-indexen.

Geordende versus niet-geordende columnstore-index

In een columnstore-index worden gegevens in elke kolom van elke rijgroep gecomprimeerd in een afzonderlijk segment. Elk segment bevat metagegevens die de minimum- en maximumwaarden beschrijven, zodat segmenten die buiten de grenzen van het querypredicaat vallen, niet worden gelezen vanaf schijf tijdens het uitvoeren van de query.

Wanneer een columnstore-index niet is geordend, worden de gegevens door de opbouwfunctie voor indexen niet gesorteerd voordat deze in segmenten worden gecomprimeerd. Dat betekent dat segmenten met overlappende waardebereiken kunnen optreden, waardoor query's meer segmenten van de schijf lezen en langer duren om te voltooien.

Wanneer u een geordende columnstore-index maakt, sorteert de database-engine de bestaande gegevens op basis van de ordersleutels die u opgeeft voordat de opbouwfunctie voor indexen deze in segmenten comprimeert. Met gesorteerde gegevens wordt segmentoverlapping verminderd of geëlimineerd, waardoor query's een efficiëntere uitschakeling van segmenten hebben en dus snellere prestaties, omdat er minder segmenten zijn om van schijf te lezen.

Afhankelijk van het beschikbare geheugen, de gegevensgrootte, de mate van parallelle uitvoering, het indextype (geclusterd versus niet-geclusterd) en het type indexbuild (offline versus online), kan de sortering voor geordende columnstore-indexen vol zijn (geen segmentover overlap) of gedeeltelijk (sommige segmentover overlap). Gedeeltelijke sortering treedt bijvoorbeeld op wanneer het beschikbare geheugen onvoldoende is voor een volledige sortering. Query's die een geordende columnstore-index gebruiken, worden vaak sneller uitgevoerd dan met een niet-geordende index, zelfs als de geordende index is gebouwd met behulp van een gedeeltelijke sortering.

Volledige sortering wordt geleverd voor geordende geclusterde columnstore-indexen die zijn gemaakt of opnieuw opgebouwd met zowel ONLINE = ON als MAXDOP = 1 opties. In dit geval wordt de sortering niet beperkt door het beschikbare geheugen, omdat de tempdb database wordt gebruikt om de gegevens weg te schrijven die niet in het geheugen passen. Hierdoor kan het indexbuildproces langzamer worden vanwege de extra tempdb I/O. Bij het opnieuw samenstellen van een onlineindex kunnen query's echter de bestaande index blijven gebruiken terwijl de nieuwe geordende index opnieuw wordt opgebouwd.

Volledige sortering kan ook worden geboden voor geordende geclusterde en niet-geclusterde columnstore-indexen die zijn gemaakt of opnieuw worden opgebouwd met zowel ONLINE = OFF als MAXDOP = 1 opties als de hoeveelheid gegevens die moet worden gesorteerd voldoende klein is om volledig in het beschikbare geheugen te passen.

In alle andere gevallen is de sortering in geordende columnstore-indexen gedeeltelijk.

Notitie

Op dit moment kunnen geordende columnstore-indexen alleen online worden gemaakt of opnieuw worden opgebouwd in Azure SQL Database en in Azure SQL Managed Instance met het Always-up-to-date update-beleid.

Als u de segmentbereiken voor een kolom wilt controleren en wilt bepalen of er sprake is van segmentover overlap, gebruikt u de volgende query, waarbij u tijdelijke aanduidingen vervangt door de namen van uw schema, tabel en kolom:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

De uitvoer van deze query voor een volledig gesorteerde columnstore-index kan er bijvoorbeeld als volgt uitzien. Houd er rekening mee dat er geen overlap is in de min_data_id en max_data_id kolommen voor verschillende segmenten.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Notitie

In een geordende columnstore-index worden de nieuwe gegevens die voortvloeien uit dezelfde batch DML- of gegevenslaadbewerkingen alleen in die batch gesorteerd. Er is geen algemene sortering die bestaande gegevens in de tabel bevat.

Als u gegevens in de index wilt sorteren nadat u nieuwe gegevens hebt ingevoegd of bestaande gegevens hebt bijgewerkt, bouwt u de index opnieuw op.

Voor een offline herbouw van een gepartitioneerde columnstore-index wordt één partitie tegelijk opnieuw opgebouwd. Gegevens in de partitie die opnieuw worden opgebouwd, zijn niet beschikbaar totdat de herbouwing voor die partitie is voltooid.

Gegevens blijven beschikbaar tijdens een online herbouwing. Zie Indexbewerkingen online uitvoerenvoor meer informatie.

Queryprestaties

De prestatiewinst van een geordende columnstore-index is afhankelijk van de querypatronen, de grootte van gegevens, hoe goed de gegevens worden gesorteerd, de fysieke structuur van segmenten en de rekenresources die beschikbaar zijn voor het uitvoeren van query's.

Query's met de volgende patronen worden doorgaans sneller uitgevoerd met geordende columnstore-indexen.

  • Queries met gelijkheid-, ongelijkheid- of bereikpredicaten.
  • Query's waarbij de predicaatkolommen en de geordende CCI-kolommen hetzelfde zijn.

In dit voorbeeld heeft tabel T1 een geclusterde columnstore-index geordend in de volgorde van Col_C, Col_Ben Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

De prestaties van query 1 en 2 kunnen profiteren van geordende columnstore-index meer dan query 3 en 4, omdat ze verwijzen naar alle geordende kolommen.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Gegevenslaadprestaties

De prestaties van het laden van gegevens in een tabel met een geordende columnstore-index zijn vergelijkbaar met een gepartitioneerde tabel. Het laden van gegevens kan langer duren dan met een niet-geordende columnstore-index vanwege de gegevenssorteringsbewerking, maar query's kunnen later sneller worden uitgevoerd.

Segmentoverlapping verminderen

Het aantal overlappende segmenten is afhankelijk van de grootte van de gegevens die moeten worden gesorteerd, het beschikbare geheugen en de maximale mate van parallelle uitvoering (MAXDOP) tijdens de geordende columnstore-indexbuild. De volgende strategieën verminderen het overlappen van segmenten, maar ze kunnen het buildproces van de index langer maken.

  • Als online indexbuild beschikbaar is, gebruikt u zowel ONLINE = ON als MAXDOP = 1 opties bij het maken van een geordende geclusterde columnstore-index. Hiermee maakt u een volledig gesorteerde index.
  • Als online indexbuild niet beschikbaar is, gebruikt u de optie MAXDOP = 1.
  • Sorteer de gegevens vooraf op de sorteersleutels vóór het laden.

Wanneer MAXDOP groter is dan 1, werkt elke thread die wordt gebruikt voor geordende columnstore-indexbuild op een subset van gegevens en sorteert deze lokaal. Er is geen wereldwijde sortering voor gegevens gesorteerd op verschillende threads. Het gebruik van parallelle threads kan de tijd verminderen om de index te maken, maar genereert meer overlappende segmenten dan wanneer u één thread gebruikt. Het gebruik van een enkelvoudige thread-bewerking levert de hoogste compressiekwaliteit op. U kunt MAXDOP opgeven met de opdracht CREATE INDEX.

Voorbeelden

Controleren op geordende kolommen en volgorde ordinaal

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Een geordende columnstore-index maken

Geclusterde gesorteerde columnstore-index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Niet-geclusterde geordende columnstore-index:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Orderkolommen toevoegen of verwijderen en een bestaande geordende columnstore-index opnieuw samenstellen

Geclusterde gesorteerde columnstore-index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Niet-geclusterde geordende columnstore-index

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Een geordende geclusterde columnstore-index online maken met volledige sortering op een heap-tabel

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Een geordende geclusterde columnstore-index online herbouwen met volledige sortering

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Verwante inhoud