Share via

Prestaties afstemmen met geordende columnstore-indexen

Van toepassing op: SQL Server 2022 (16.x) en latere versies Van Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric

Geordende columnstore-indexen kunnen snellere prestaties bieden door grote hoeveelheden geordende gegevens over te slaan die niet overeenkomen met het querypredicaat. Tijdens het laden van gegevens in een geordende columnstore-index en het onderhouden van de volgorde door het herbouwen van indexen duurt langer dan in een niet-geordende index, kunnen geïndexeerde query's sneller worden uitgevoerd met geordende columnstore.

Wanneer een query een columnstore-index leest, controleert de database-engine de minimum- en maximumwaarden die zijn opgeslagen in elk kolomsegment. Het proces elimineert segmenten die buiten de grenzen van het querypredicaat vallen. Met andere woorden, deze segmenten worden overgeslagen bij het lezen van gegevens van schijf of geheugen. Een query wordt sneller voltooid als het aantal segmenten dat moet worden gelezen en de totale grootte aanzienlijk kleiner is.

Met bepaalde gegevensbelastingpatronen kunnen gegevens in een columnstore-index impliciet worden geordend zonder de ORDER component op te geven. Als er bijvoorbeeld elke dag gegevens worden geladen, kunnen de gegevens per kolom worden geordend load_date . In dit geval kunnen queryprestaties al profiteren van deze impliciete volgorde. Het expliciet rangschikken van de columnstore-index door dezelfde load_date kolom in de ORDER component biedt waarschijnlijk geen extra prestatievoordeel.

Zie Geordende beschikbaarheid van columnstore-indexen in verschillende SQL-platforms en SQL Server-versies voor geordende beschikbaarheid van columnstore-indexen.

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 de gegevens in elke kolom van elke rijgroep gecomprimeerd in een afzonderlijk segment. Elk segment bevat metagegevens die de minimum- en maximumwaarden beschrijven, zodat het queryuitvoeringsproces segmenten kan overslaan die buiten de grenzen van het querypredicaat vallen.

Wanneer een columnstore-index niet is geordend, worden de gegevens door de opbouwfunctie voor indexen niet gesorteerd voordat deze in segmenten worden gecomprimeerd. Dit betekent dat segmenten met overlappende waardebereiken kunnen optreden, waardoor query's meer segmenten kunnen lezen om de vereiste gegevens te verkrijgen. Als gevolg hiervan kan het langer duren voordat query's zijn voltooid.

Wanneer u een geordende columnstore-index maakt door de ORDER clausule op te geven in de CREATE COLUMNSTORE INDEX instructie, sorteert de Database Engine de gegevens in elk segment van elke kolom in de opgegeven volgorde voordat de indexbouwer de gegevens in segmenten comprimeert. Met gesorteerde gegevens wordt overlapping van segmenten verminderd of geëlimineerd, waardoor query's een efficiëntere segmentuitschakeling en dus snellere prestaties kunnen gebruiken, omdat er minder segmenten en minder gegevens zijn om te lezen.

Segmentover overlap verminderen en queryprestaties verbeteren

Wanneer u een geordende columnstore-index bouwt, sorteert de database-engine de gegevens op best-effortbasis. 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 volgorde in een kolom in een columnstore-index vol zijn zonder segmentover overlap of gedeeltelijk met een segmentover overlap. Wanneer er minder overlappende segmenten zijn, voert een query die kan profiteren van kolomvolgorde sneller uit.

Aanbeveling

Zelfs als de volgorde in een kolom van een columnstore-index gedeeltelijk is, kunnen segmenten nog steeds worden verwijderd (overgeslagen). Een volledige volgorde is niet vereist om prestatievoordelen te behalen als een gedeeltelijke volgorde vele segmentoverlappingen vermijdt.

In de volgende tabel wordt het resulterende ordertype beschreven wanneer u een geordende columnstore-index maakt of herbouwt, afhankelijk van de opties voor indexbuild.

Vereiste voorwaarden Ordersoort
ONLINE = ON en MAXDOP = 1 Volledig
ONLINE = OFF, MAXDOP = 1en de gegevens die volledig in het geheugen van de querywerkruimte moeten worden gesorteerd Volledig
Alle andere gevallen Gedeeltelijk

In het eerste geval waarin zowel ONLINE = ON als MAXDOP = 1 van toepassing zijn, wordt de sortering niet beperkt door het geheugen van de querywerkruimte, omdat een online verwerking van een ordelijke columnstore-index de tempdb database gebruikt om de gegevens te spillen die niet in het geheugen passen. Deze methode kan het indexbuildproces langzamer maken vanwege de extra tempdb I/O, en vereist voldoende vrije ruimte in tempdb. Omdat de indexbuild echter online wordt uitgevoerd, kunnen query's de bestaande index blijven gebruiken terwijl de nieuwe geordende index wordt gebouwd.

Op dezelfde manier wordt met een offline herbouw van een gepartitioneerde columnstore-index één partitie tegelijk uitgevoerd. Andere partities blijven beschikbaar voor query's.

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 het resulteert in meer overlappende segmenten dan wanneer u één thread gebruikt.

U kunt geordende columnstore-indexen alleen online maken of herbouwen in sommige SQL-platforms en SQL Server-versies. Zie voor meer informatie het functieoverzicht voor productreleases.

In SQL Server zijn online indexbewerkingen niet beschikbaar in alle edities. Zie Edities en ondersteunde functies van SQL Server 2025 en Indexbewerkingen online uitvoeren voor meer informatie.

Voor bepaalde gegevenstypen en coderingen kan de sys.column_store_segments systeemweergave u helpen bij het vinden van het aantal segmentover overlappingen. Een voorbeeldscript op basis van deze weergave bepaalt de volgordekwaliteit voor in aanmerking komende kolommen van alle columnstore-indexen in de huidige database.

Prestaties van zoekopdracht

De prestatiewinst van een geordende columnstore-index is afhankelijk van de querypatronen, de grootte van gegevens, het aantal overlappende 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 het volgende voorbeeld bevat de tabel T1 een geclusterde columnstore-index met Col_C, Col_Ben Col_A als geordende kolommen.

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

Query 1 profiteert van de geordende columnstore-index meer dan query's 2 en 3, omdat query 1 verwijst naar alle geordende kolommen in het predicaat.

-- 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_A = 'a';

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

Gegevenslaadprestaties

De prestaties van een gegevensbelasting 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.

Nieuwe gegevens toevoegen of bestaande gegevens bijwerken

De nieuwe gegevens die voortkomen uit een DML-batch of een bulklading in een tabel met een geordende columnstore-index, worden alleen binnen die batch gesorteerd. Er is geen algemene sortering die bestaande gegevens in de tabel bevat, omdat gecomprimeerde rijgroepen in een columnstore-index onveranderbaar zijn.

Als u de overlapping van segmenten wilt verminderen nadat u nieuwe gegevens hebt ingevoegd of bestaande gegevens hebt bijgewerkt, bouwt u de columnstore-index opnieuw op.

Examples

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

Controleren op geordende kolommen en volgorde ordinaal

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       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;

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 ordening 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 ordening

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

Verwante inhoud

  • Last updated on