Columnstore-indexen - prestaties van queries

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

Dit artikel bevat aanbevelingen voor het bereiken van de snelle queryprestaties met columnstore-indexen.

Columnstore-indexen kunnen tot wel 100 keer betere prestaties behalen voor analyse- en datawarehousingworkloads, en tot wel 10 keer betere gegevenscompressie dan traditionele rowstore-indexen. Deze aanbevelingen helpen uw query's de snelle queryprestaties te realiseren die columnstore-indexen kunnen bieden.

Aanbevelingen voor het verbeteren van queryprestaties

Hier volgen enkele aanbevelingen voor het bereiken van de hoge prestaties die columnstore-indexen zijn ontworpen om te bieden.

1. Organiseer gegevens om meer rijengroepen uit een volledige tabelscan te elimineren

• Kies zorgvuldig de invoegvolgorde. In traditionele datawarehouses worden de gegevens inderdaad ingevoegd in chronologische volgorde en worden analyses uitgevoerd in een tijdsdimensie. Bijvoorbeeld het analyseren van de verkoop per kwartaal. Voor dit soort werkbelasting vindt de verwijdering van de rijgroep automatisch plaats. In SQL Server 2016 (13.x) vindt u het aantal rijengroepen dat is overgeslagen als onderdeel van queryverwerking.

• Gebruik een geclusterde index in rowstore. Als het algemene querypredicaat zich in een kolom bevindt (bijvoorbeeld C1) die niet is gerelateerd aan de invoegvolgorde, maakt u een geclusterde rijopslagindex op kolom C1. Verwijder vervolgens de geclusterde index rowstore en maak een geclusterde columnstore-index. Als u de geclusterde columnstore-index expliciet maakt met behulp MAXDOP = 1van, is de resulterende geclusterde columnstore-index perfect gerangschikt op kolom C1. Als u opgeeft MAXDOP = 8, ziet u overlapping van waarden tussen acht rijengroepen. Als de tabel een niet-geclusterde columnstore-index (NCCI) heeft, worden de rijen al geordend op basis van de geclusterde indexsleutel als de tabel een geclusterde index heeft. In dit geval wordt de niet-geclusterde columnstore-index ook automatisch geordend. Een columnstore-index houdt de volgorde van rijen niet inherent bij. Wanneer nieuwe rijen worden ingevoegd of oudere rijen worden bijgewerkt, moet u het proces mogelijk herhalen omdat de prestaties van de analysequery kunnen verslechteren.

• Tabelpartitionering implementeren. U kunt de columnstore-index partitioneren en vervolgens partitieverwijdering gebruiken om het aantal rijengroepen te verminderen dat moet worden gescand. In een feitentabel worden bijvoorbeeld aankopen opgeslagen die zijn gedaan door klanten. Een veelvoorkomend querypatroon is het vinden van kwartaalaankopen per customer. In dit geval combineert u de kolom volgorde invoegen met partitioneren op customer kolom. Elke partitie bevat rijen voor elke customer, geordend bij invoeging. Overweeg ook het gebruik van tabelpartitionering als u oudere gegevens uit de columnstore wilt verwijderen. Het uitschakelen en afkappen van partities die niet nodig zijn, is een efficiënte strategie om gegevens te verwijderen zonder fragmentatie te genereren.

• Vermijd het verwijderen van grote hoeveelheden gegevens. Het verwijderen van gecomprimeerde rijen uit een rijgroep is geen synchrone bewerking. Het zou duur zijn om een rijgroep te decomprimeren, de rij te verwijderen en deze vervolgens opnieuw tecomprimeren. Wanneer u daarom gegevens verwijdert uit gecomprimeerde rijgroepen, worden deze rijgroepen nog steeds gescand, ook al retourneren ze minder rijen. Als het aantal verwijderde rijen voor meerdere rijgroepen groot genoeg is om te worden samengevoegd in minder rijengroepen, verhoogt het opnieuw ordenen van de columnstore de kwaliteit van de index en verbetert de queryprestaties. Als uw gegevensverwijderingsproces meestal hele rijengroepen leeg maakt, kunt u overwegen om tabelpartitionering te gebruiken. Schakel partities uit die niet meer nodig zijn en kap ze af in plaats van rijen te verwijderen.

  Note

  Vanaf SQL Server 2019 (15.x) wordt de tuple-mover geholpen door een samenvoegtaak op de achtergrond. Deze taak comprimeert automatisch kleinere OPEN-deltarijgroepen die al enige tijd bestaan, zoals bepaald door een interne drempelwaarde, of voegt GECOMPRIMEERDE rijgroepen samen van waaruit een groot aantal rijen is verwijderd. Dit verbetert de kwaliteit van de columnstore-index in de loop van de tijd. Als het verwijderen van grote hoeveelheden gegevens uit de columnstore-index vereist is, kunt u overwegen om die bewerking in kleinere verwijderbatches in de loop van de tijd te splitsen. Met batchverwerking kan de samenvoegtaak op de achtergrond de taak van het samenvoegen van kleinere rijengroepen afhandelen en de indexkwaliteit verbeteren. Vervolgens hoeft u geen onderhoudsvensters voor indexreorganisatie te plannen na het verwijderen van gegevens. Zie Columnstore-indexen voor meer informatie over columnstore-termen en -concepten: overzicht.

2. Plan voldoende geheugen om columnstore-indexen parallel te maken

Het maken van een columnstore-index is standaard een parallelle bewerking, tenzij het geheugen beperkt is. Het parallel maken van de index vereist meer geheugen dan het serieel maken van de index. Wanneer er voldoende geheugen is, duurt het maken van een columnstore-index 1,5 keer zo lang als het bouwen van een B-boomstructuur op dezelfde kolommen.

Het geheugen dat nodig is voor het maken van een columnstore-index, is afhankelijk van het aantal kolommen, het aantal tekenreekskolommen, de mate van parallelle uitvoering (DOP) en de kenmerken van de gegevens. Als uw tabel bijvoorbeeld minder dan één miljoen rijen bevat, gebruikt Database Engine slechts één thread om de columnstore-index te maken.

Als uw tabel meer dan één miljoen rijen heeft, maar Database Engine geen groot genoeg geheugentoekenning kan krijgen om de index met MAXDOP te maken, verlaagt Database Engine automatisch MAXDOP als dat nodig is. In sommige gevallen moet DOP worden verlaagd tot één om de index te bouwen onder beperkt geheugen in de beschikbare geheugentoekenning.

Sinds SQL Server 2016 (13.x) werkt de query altijd in de batchmodus. In eerdere releases wordt batchuitvoering alleen gebruikt wanneer DOP groter is dan één.

Uitleg over de prestaties van Columnstore

Columnstore-indexen bereiken hoge queryprestaties door snelle batchmodusverwerking in het geheugen te combineren met technieken die de I/O-vereisten aanzienlijk verminderen. Omdat analysequery's grote aantallen rijen scannen, zijn ze meestal I/O-gebonden, waardoor het verminderen van I/O tijdens het uitvoeren van query's essentieel is voor het ontwerp van columnstore-indexen. Zodra gegevens in het geheugen zijn gelezen, is het essentieel om het aantal in-memory bewerkingen te verminderen.

Columnstore-indexen verminderen I/O en optimaliseren in-memory bewerkingen door middel van hoge gegevenscompressie, columnstore-verwijdering, verwijdering van rijengroepen en batchverwerking.

Data compression

Columnstore-indexen bereiken maximaal 10 keer meer gegevenscompressie dan rowstore-indexen. Dit vermindert de I/O die nodig is om analysequery's uit te voeren en verbetert daarom de queryprestaties.

• Columnstore-indexen lezen gecomprimeerde gegevens van schijf, wat betekent dat er minder bytes aan gegevens moeten worden gelezen in het geheugen.

• Columnstore-indexen slaan gegevens op in gecomprimeerde vorm in het geheugen, waardoor I/O wordt verminderd door het lezen van dezelfde gegevens in het geheugen te voorkomen. Met 10 keer compressie kunnen columnstore-indexen bijvoorbeeld 10 keer meer gegevens in het geheugen bewaren, vergeleken met het opslaan van de gegevens in niet-gecomprimeerde vorm. Met meer gegevens in het geheugen is het waarschijnlijker dat de columnstore-index de gegevens vindt die nodig zijn in het geheugen zonder onnodige leesbewerkingen van de schijf.

• Columnstore-indexen comprimeren gegevens op kolommen in plaats van op rijen, bereiken hoge compressiesnelheden en verminderen de grootte van de gegevens die op schijf zijn opgeslagen. Elke kolom wordt onafhankelijk gecomprimeerd en opgeslagen. Gegevens in een kolom hebben altijd hetzelfde gegevenstype en hebben meestal vergelijkbare waarden. Columnstore-gegevenscompressietechnieken zijn zeer geschikt voor het bereiken van hogere compressiesnelheden wanneer waarden vergelijkbaar zijn.

In een feitentabel worden bijvoorbeeld klantadressen opgeslagen en is er een kolom voor country-region. Het totale aantal mogelijke waarden is minder dan 200. Sommige van deze waarden worden vaak herhaald. Als de feitentabel 100 miljoen rijen bevat, wordt de country-region kolom eenvoudig gecomprimeerd en is er weinig opslagruimte nodig. Rij-per-rij-compressie kan op deze manier geen gebruikmaken van de gelijkenis tussen kolomwaarden en moet meer bytes gebruiken om de waarden in de country-region-kolom te comprimeren.

Column elimination

Columnstore-indexen slaan het lezen over in kolommen waarnaar niet wordt verwezen door de query. Kolomuitschakeling vermindert de I/O verder voor het uitvoeren van query's en verbetert daarom de queryprestaties.

• Kolomverwijdering is mogelijk omdat de gegevens per kolom zijn geordend en gecomprimeerd. Wanneer gegevens daarentegen per rij worden opgeslagen, worden de kolomwaarden in elke rij fysiek opgeslagen en kunnen ze niet eenvoudig worden gescheiden. De queryprocessor moet in een hele rij lezen om specifieke kolomwaarden op te halen, waardoor I/O toeneemt omdat extra gegevens onnodig in het geheugen worden gelezen.

Als een tabel bijvoorbeeld 50 kolommen heeft en de query slechts vijf van deze kolommen gebruikt, haalt de columnstore-index alleen de vijf kolommen van de schijf op. Het slaat het lezen in de andere 45 kolommen over, waardoor I/O met nog eens 90%wordt verminderd, ervan uitgaande dat alle kolommen van vergelijkbare grootte zijn. Als dezelfde gegevens worden opgeslagen in een rijarchief, moet de queryprocessor de resterende 45 kolommen lezen.

Rowgroup elimination

Voor volledige tabelscans komt een groot percentage van de gegevens meestal niet overeen met de criteria voor het querypredicaat. Met behulp van metagegevens kan de columnstore-index het lezen overslaan in de rijgroepen die geen gegevens bevatten die vereist zijn voor het queryresultaat, allemaal zonder werkelijke I/O. Deze mogelijkheid, ook wel rowgroup-verwijdering genoemd, vermindert I/O voor volledige tabelscans en verbetert daarom de queryprestaties.

Wanneer moet een columnstore-index een volledige tabelscan uitvoeren?

Vanaf SQL Server 2016 (13.x) kunt u een of meer reguliere niet-geclusterde rowstore- of B-tree-indexen maken op een geclusterde columnstore-index. De niet-geclusterde B-boomstructuurindexen kunnen een query met een gelijkheidspredicaat of een predicaat met een klein bereik van waarden versnellen. Voor complexere predicaten kan de queryoptimalisatie een volledige tabelscan kiezen. Zonder de mogelijkheid om rijgroepen over te slaan, kan een volledige tabelscan tijdrovend zijn, met name voor grote tabellen.

Wanneer profiteert een analysequery van de verwijdering van rijengroep voor een volledige tabelscan?

Een retailbedrijf modelleerde bijvoorbeeld de verkoopgegevens met behulp van een feitentabel met een geclusterde columnstore-index. Elke nieuwe verkoop slaat verschillende kenmerken van de transactie op, inclusief de datum waarop een product wordt verkocht. Interessant is dat, hoewel columnstore-indexen geen gesorteerde volgorde garanderen, rijen in deze tabel in een datumsorteerde volgorde worden geladen. Na verloop van tijd groeit deze tabel. Hoewel de detailhandel verkoopgegevens voor de afgelopen tien jaar kan bewaren, hoeft een analysequery mogelijk slechts een aggregaties voor het afgelopen kwartaal te berekenen. Columnstore-indexen kunnen de toegang tot de gegevens voor de vorige 39 kwartalen elimineren door alleen de metagegevens voor de datumkolom te bekijken. Dit is een 97% vermindering van de hoeveelheid gegevens die in het geheugen wordt gelezen en verwerkt.

Welke rijengroepen worden overgeslagen in een volledige tabelscan?

Om te bepalen welke rijengroepen moeten worden verwijderd, gebruikt de columnstore-index metagegevens om de minimum- en maximumwaarden van elk kolomsegment voor elke rijgroep op te slaan. Wanneer geen van de kolomsegmentbereiken voldoet aan de criteria voor het querypredicaat, wordt de hele rijgroep overgeslagen zonder dat er werkelijke I/O wordt uitgevoerd. Dit werkt omdat de gegevens meestal in een gesorteerde volgorde worden geladen. Hoewel het sorteren van rijen niet gegarandeerd is, bevinden vergelijkbare gegevenswaarden zich vaak binnen dezelfde rijgroep of een aangrenzende rijgroep.

Zie de ontwerprichtlijnen voor columnstore-indexen voor meer informatie over rijgroepen.

Uitvoering van batchmodus

Batchmodus-uitvoering verwerkt rijen in groepen, doorgaans tot 900 tegelijk, om de efficiëntie te verbeteren. De query SELECT SUM(Sales) FROM SalesData berekent bijvoorbeeld de totale verkoop uit de SalesData tabel. In de batchmodus verwerkt de query-engine de gegevens in groepen van 900 rijen. Deze aanpak vermindert de kosten voor toegang tot metagegevens en andere soorten overhead door ze over alle rijen in een batch te spreiden, in plaats van de overhead voor elke rij te maken. Bovendien werkt de batchmodus indien mogelijk met gecomprimeerde gegevens en verwijdert u een aantal exchange-operators die worden gebruikt in de rijmodus, waardoor analytische query's aanzienlijk worden versneld.

Niet alle operators voor het uitvoeren van query's kunnen worden uitgevoerd in de batchmodus. DML-bewerkingen (Data Manipulat Language), zoals invoegen, verwijderen of bijwerken, worden bijvoorbeeld één rij tegelijk uitgevoerd. De batchmodusoperator, zoals Scannen, Samenvoegen, Aggregeren, Sorteren en andere, kunnen de queryprestaties verbeteren. Omdat de columnstore-index is geïntroduceerd in SQL Server 2012 (11.x), is er een duurzame inspanning om de operators uit te breiden die kunnen worden uitgevoerd in de batchmodus. In de volgende tabel ziet u de operators die in batchmodus draaien volgens de productversie.

¹ Is van toepassing op SQL Server 2016 (13.x), SQL Database Premium-lagen, Standard-lagen - S3 en hoger, en alle vCore-lagen, en Analytics Platform System (PDW)

Zie de architectuurhandleiding voor queryverwerking voor meer informatie.

Aggregate pushdown

Een normaal uitvoeringspad voor aggregatieberekening om de in aanmerking komende rijen op te halen uit het SCAN-knooppunt en de waarden in batchmodus samen te voegen. Hoewel dit goede prestaties levert, vanaf SQL Server 2016 (13.x), kan de statistische bewerking naar het SCAN-knooppunt worden gepusht. Met aggregatiedruk worden de prestaties van geaggregeerde berekeningen met orders van grootte verbeterd bovenop de batchmodus-uitvoering, mits aan de volgende voorwaarden wordt voldaan:

• De aggregaties zijn MIN, MAXen SUMCOUNTCOUNT(*).
• De aggregatieoperator moet bovenop het SCAN-knooppunt of SCAN-knooppunt met GROUP BY zijn.
• Deze verzameling is geen afzonderlijk aggregaat.
• De statistische kolom is geen tekenreekskolom.
• De statistische kolom is geen virtuele kolom.
• Het invoer- en uitvoergegevenstype moet een van de volgende zijn en moet binnen 64 bits passen:
  • tinyint, int, bigint, smallint, bit
  • smallmoney, geld, decimaal en numeriek met precisie <= 18
  • smalldate, date, datetime, datetime2, time

Aggregatie-pushdown wordt bijvoorbeeld uitgevoerd in beide van de volgende queries:

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Pushdown van tekenreekspredicaat

Bij het ontwerpen van een datawarehouseschema is het aanbevolen schemamodellering om stervormige schema's of snowflake-schema's te gebruiken die bestaan uit een of meer feitentabellen en veel dimensietabellen.

Een feit kan bijvoorbeeld een record zijn die een verkoop van een bepaald product in een specifieke regio vertegenwoordigt, terwijl de dimensie een set regio's, producten enzovoort vertegenwoordigt. De feiten- en dimensietabellen zijn verbonden via een primaire/vreemde-sleutelrelatie. De meestgebruikte analysequery's voegen een of meer dimensietabellen samen met de feitentabel.

Laten we een dimensietabel Productsoverwegen. Een typische primaire sleutel wordt ProductCodemeestal weergegeven als tekenreeks. Voor de prestaties van query's is het raadzaam om surrogaatsleutels te maken, meestal een kolom met gehele getallen , om te verwijzen naar de rij in de dimensietabel uit de feitentabel.

De columnstore-index voert analysequery's uit met joins en predicaten waarbij numerieke of op gehele getallen gebaseerde sleutels efficiënt worden gebruikt. SQL Server 2016 (13.x) heeft de prestaties van analysequery's met tekstkolommen aanzienlijk verbeterd door de predicaten met tekstkolommen door te voeren naar het SCAN-knooppunt.

Pushdown voor tekenreekspredicatie maakt gebruik van de primaire/secundaire woordenlijst die is gemaakt voor kolommen om de queryprestaties te verbeteren. Denk bijvoorbeeld aan een segment van een tekenreekskolom in een rijgroep die bestaat uit 100 afzonderlijke tekenreekswaarden. Elke afzonderlijke tekenreekswaarde wordt gemiddeld 10.000 keer verwezen, uitgaande van één miljoen rijen. Met string-predicaten-pushdown voert de query-uitvoering het predicaat uit tegen de waarden in de woordenlijst. Als het predicaat in aanmerking komt, worden alle rijen die naar de woordenlijstwaarde verwijzen, automatisch gekwalificeerd. Dit verbetert de prestaties op twee manieren:

• Alleen de gekwalificeerde rij wordt geretourneerd, waardoor het aantal rijen dat buiten het scanknooppunt moet stromen, wordt verminderd.
• Het aantal tekenreeksvergelijkingen wordt verminderd. In dit voorbeeld zijn slechts 100 tekenreeksvergelijkingen vereist ten opzichte van 1 miljoen vergelijkingen. Er zijn enkele beperkingen:
  • Geen doorvoer van tekenreekscriteria voor delta-rijgroepen. Er is geen woordenlijst voor kolommen in deltarijgroepen.
  • Er is geen pushdown van het tekenreekspredicaat als de woordenlijst meer dan 64 kB aan vermeldingen bevat.
  • Expressies die null-waarden evalueren, worden niet ondersteund.

Segment elimination

Gegevenstypekeuzen kunnen een aanzienlijke invloed hebben op queryprestaties op basis van algemene filterpredicaten voor query's in de columnstore-index.

In columnstore-gegevens bestaan rijgroepen uit kolomsegmenten. Er zijn metagegevens voor elk segment, zodat segmenten snel kunnen worden verwijderd zonder ze te lezen. Deze segmentuitschakeling is van toepassing op numerieke gegevenstypen, datum- en tijdgegevenstypen en het gegevenstype datetimeoffset met een schaal kleiner dan of gelijk aan twee. Vanaf SQL Server 2022 (16.x) kunnen segmentuitschakelingsmogelijkheden worden uitgebreid naar tekenreeksen, binaire, GUID-gegevenstypen en het gegevenstype datetimeoffset voor schaal groter dan twee.

Na een upgrade naar een versie van SQL Server die ondersteuning biedt voor het elimineren van tekenreeksen min/max segment (SQL Server 2022 (16.x) en hoger), profiteert de columnstore-index pas van deze functie als deze opnieuw wordt opgebouwd met behulp van een ALTER INDEX REBUILD of CREATE INDEX WITH (DROP_EXISTING = ON).

Segmentuitschakeling is niet van toepassing op LOB-gegevenstypen, zoals de lengte van het (maximale) gegevenstype.

Momenteel ondersteunt alleen SQL Server 2022 (16.x) en hoger geclusterde columnstore-rowgroup-verwijdering voor het voorvoegsel van LIKE predicaten, bijvoorbeeld column LIKE 'string%'. Segmentuitschakeling wordt niet ondersteund voor niet-voorvoegselgebruik van LIKE, zoals column LIKE '%string'.

Geordende columnstore-indexen profiteren ook van segmentverwijdering, met name voor tekenreekskolommen. In geordende columnstore-indexen is segmentverwijdering voor de eerste kolom in de indexsleutel het meest effectief, omdat deze wordt gesorteerd. Prestatieverbeteringen als gevolg van segmentuitschakeling op andere kolommen in de tabel zijn minder voorspelbaar. Zie Een geordende columnstore-index gebruiken voor grote datawarehouse-tabellen voor meer informatie over geordende columnstore-indexen. Zie Beschikbaarheid van geordende kolomindexenvoor geordende beschikbaarheid van columnstore-indexen.

Met behulp van de queryverbindingsoptie SET STATISTICS IO kunt u segmentuitschakeling in actie bekijken. Zoek naar uitvoer zoals het volgende om aan te geven dat segmentverwijdering heeft plaatsgevonden. Rijgroepen bestaan uit kolomsegmenten, dus dit kan duiden op het uitschakelen van segmenten. Het volgende SET STATISTICS IO uitvoervoorbeeld van een query, ongeveer 83% gegevens zijn overgeslagen door de query:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Related content

• Ontwerprichtlijnen voor columnstore-indexen
• Columnstore-indexen - Richtlijnen voor het laden van gegevens
• Aan de slag met Columnstore voor realtime operationele analyses
• Columnstore-indexen in data warehousing
• Indexonderhoud optimaliseren om de queryprestaties te verbeteren en het resourceverbruik te verminderen
• Columnstore-indexarchitectuur
• MAAK INDEX AAN (Transact-SQL)
• ALTER INDEX (Transact-SQL)