Aan de slag met columnstore-indexen voor realtime operationele analyses

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric Preview

SQL Server 2016 (13.x) introduceert realtime operationele analyses, de mogelijkheid om zowel analyses als OLTP-workloads tegelijkertijd op dezelfde databasetabellen uit te voeren. Naast het uitvoeren van analyses in realtime, kunt u ook de noodzaak van ETL en een datawarehouse elimineren.

Uitleg van operationele analyses in realtime

Normaal gesproken hebben bedrijven afzonderlijke systemen voor operationele werkbelastingen (dat wil gezegd OLTP) en analyseworkloads. Voor dergelijke systemen verplaatsen ETL-taken (Extract, Transform en Load) de gegevens regelmatig van het operationele archief naar een analysearchief. De analysegegevens worden meestal opgeslagen in een datawarehouse of datamart die is toegewezen aan het uitvoeren van analysequery's. Hoewel deze oplossing de standaard is, zijn deze drie belangrijke uitdagingen:

• Complexity. Het implementeren van ETL kan aanzienlijke codering vereisen, met name om alleen de gewijzigde rijen te laden. Het kan complex zijn om te bepalen welke rijen zijn gewijzigd.
• Cost. Voor het implementeren van ETL zijn extra hardware- en softwarelicenties vereist.
• Data Latency. Het implementeren van ETL voegt een tijdsvertraging toe voor het uitvoeren van de analyse. Als de ETL-taak bijvoorbeeld aan het einde van elke werkdag wordt uitgevoerd, worden de analysequery's uitgevoerd op gegevens die ten minste een dag oud zijn. Voor veel bedrijven is deze vertraging onaanvaardbaar omdat het bedrijf afhankelijk is van het analyseren van gegevens in realtime. Fraudedetectie vereist bijvoorbeeld realtimeanalyses voor operationele gegevens.

Operationele analyses in realtime bieden een oplossing voor deze uitdagingen.

Er is geen tijdsvertraging wanneer analyses en OLTP-workloads worden uitgevoerd in dezelfde onderliggende tabel. Voor scenario's die realtime analyses kunnen gebruiken, worden de kosten en complexiteit aanzienlijk verminderd door de noodzaak voor ETL en de noodzaak om een afzonderlijk datawarehouse te kopen en te onderhouden.

Realtime analyse maakt gebruik van een updatebare niet-geclusterde columnstore-index in een rowstore-tabel. De columnstore-index onderhoudt een kopie van de gegevens, zodat de OLTP- en analyseworkloads worden uitgevoerd op afzonderlijke kopieën van de gegevens. Dit minimaliseert de invloed van de prestaties van beide workloads die tegelijkertijd worden uitgevoerd. De database-engine onderhoudt automatisch indexwijzigingen, zodat OLTP-wijzigingen altijd up-to-datum voor analyse. Met dit ontwerp is het mogelijk en praktisch om analyses in realtime uit te voeren op up-to-datumgegevens. Dit werkt voor tabellen die zijn geoptimaliseerd voor schijven en geoptimaliseerd voor geheugen.

Aan de slag voorbeeld

Aan de slag met realtime analyse:

1. Identificeer de tabellen in uw operationele schema die gegevens bevatten die nodig zijn voor analyse.

2. Verwijder voor elke tabel alle B-tree-indexen die voornamelijk zijn ontworpen om bestaande analyses op uw OLTP-workload te versnellen. Vervang ze door één niet-geclusterde columnstore-index. Dit kan de algehele prestaties van uw OLTP-workload verbeteren, omdat er minder indexen zijn om te onderhouden.

   --This example creates a nonclustered columnstore index on an existing OLTP table.
   --Create the table
   CREATE TABLE t_account (
       accountkey int PRIMARY KEY,
       accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int
   );
   
   --Create the columnstore index with a filtered condition
   CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
   ON t_account (accountkey, accountdescription, unitsold)
   ;
   

   Met de columnstore-index in een tabel die is geoptimaliseerd voor geheugen, kunnen operationele analyses worden uitgevoerd door OLTP- en columnstore-technologieën in het geheugen te integreren om hoge prestaties te leveren voor zowel OLTP- als analyseworkloads. De columnstore-index voor een tabel die is geoptimaliseerd voor geheugen, moet de geclusterde index zijn, met andere woorden dat deze alle kolommen moet bevatten.

   -- This example creates a memory-optimized table with a columnstore index.
   CREATE TABLE t_account (
       accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
       Accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int,
       INDEX t_account_cci CLUSTERED COLUMNSTORE
       )
       WITH (MEMORY_OPTIMIZED = ON );
   

U bent nu klaar om realtime operationele analyses uit te voeren zonder wijzigingen aan te brengen in uw toepassing. Analysequeries worden uitgevoerd op de columnstore-index en OLTP-bewerkingen blijven werken met uw OLTP B-tree-indexen. De OLTP-workloads blijven presteren, maar er is extra overhead nodig om de columnstore-index te behouden. Zie de prestatieoptimalisaties in de volgende sectie.

Blog posts

Lees de volgende blogberichten voor meer informatie over realtime operationele analyses. Het is misschien gemakkelijker om de secties met prestatietips te begrijpen als u eerst de blogberichten bekijkt.

• Business case voor realtime operationele analyses

• Een niet-geclusterde columnstore-index gebruiken voor realtime operationele analyses

• Een eenvoudig voorbeeld van een niet-geclusterde columnstore-index

• Hoe SQL Server een niet-geclusterde columnstore-index onderhoudt op een transactionele workload

• De impact van niet-geclusterd columnstore-indexonderhoud minimaliseren met behulp van een gefilterde index

• De impact van niet-geclusterd columnstore-indexonderhoud minimaliseren met behulp van compressievertraging

• Minimaliseer de impact van een niet-geclusterd columnstore-indexonderhoud met behulp van compressievertraging - prestatienummers

• Realtime operationele analyses met tabellen die zijn geoptimaliseerd voor geheugen

• Columnstore-index en het samenvoegbeleid voor rijgroepen

Videos

De videoserie Data Exposed gaat dieper in op enkele mogelijkheden en overwegingen.

• Deel 1: Hoe Azure SQL Realtime Operational Analytics (HTAP) inschakelt
• Deel 2: Bestaande databases en toepassingen optimaliseren met operationele analyses
• Deel 3: Operationele analyses maken met Window Functions.

Prestatietip 1: gefilterde indexen gebruiken om de queryprestaties te verbeteren

Het uitvoeren van realtime operationele analyses kan van invloed zijn op de prestaties van de OLTP-workload. Deze impact moet minimaal zijn. Voorbeeld A laat zien hoe u gefilterde indexen gebruikt om de impact van niet-geclusterde columnstore-index op transactionele werkbelasting te minimaliseren terwijl u nog steeds analyses in realtime levert.

Als u de overhead van het onderhouden van een niet-geclusterde columnstore-index voor een operationele workload wilt minimaliseren, kunt u een gefilterde voorwaarde gebruiken om alleen een niet-geclusterde columnstore-index te maken op de warme of langzaam veranderende gegevens. In een toepassing voor orderbeheer kunt u bijvoorbeeld een niet-geclusterde columnstore-index maken voor de orders die al zijn verzonden. Zodra de bestelling is verzonden, verandert deze zelden en kan deze daarom worden beschouwd als warme gegevens. Bij een gefilterde index zijn voor de gegevens in de niet-geclusterde columnstore-index minder updates vereist, waardoor de impact op de transactionele workload wordt verlaagd.

Analysequery's hebben zo nodig transparante toegang tot zowel warme als dynamische gegevens om realtime analyses te bieden. Als een belangrijk deel van de operationele workload de 'dynamische' gegevens raakt, is voor deze bewerkingen geen extra onderhoud van de columnstore-index vereist. Een best practice is om een geclusterde rowstore-index te hebben voor de kolommen die worden gebruikt in de gefilterde indexdefinitie. De database-engine gebruikt de geclusterde index om snel de rijen te scannen die niet aan de gefilterde voorwaarde voldoen. Zonder deze geclusterde index is een volledige tabelscan van de tabel rowstore vereist om deze rijen te vinden, wat de prestaties van analytische query's negatief kan beïnvloeden. Als er geen geclusterde index is, kunt u een complementair gefilterde niet-geclusterde B-tree-index maken om dergelijke rijen te identificeren, maar dit wordt niet aanbevolen omdat het openen van een groot aantal rijen via niet-geclusterde B-structuurindexen duur is.

Voorbeeld A: Toegang tot dynamische gegevens uit B-tree-index, warme gegevens uit columnstore-index

In dit voorbeeld wordt een gefilterde voorwaarde (accountkey > 0) gebruikt om te bepalen welke rijen zijn opgenomen in de columnstore-index. Het doel is om de gefilterde voorwaarde en opeenvolgende query's te ontwerpen voor toegang tot vaak veranderende "dynamische" gegevens uit de B+ boomindex en om toegang te krijgen tot de stabielere "warme" gegevens uit de columnstore-index.

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

De analysequery wordt uitgevoerd met het volgende queryplan. U kunt zien dat de rijen die niet voldoen aan de gefilterde voorwaarde, toegankelijk zijn via de geclusterde B-structuurindex.

Zie Blog: Gefilterde niet-geclusterde columnstore-index voor meer informatie.

Prestatietip 2: Verplaats analyses naar Always On leesbare secundaire server

Hoewel u het onderhoud van de columnstore-index kunt minimaliseren met behulp van een gefilterde columnstore-index, kunnen de analysequery's nog steeds aanzienlijke rekenresources (CPU, I/O, geheugen) vereisen die van invloed zijn op de prestaties van de operationele werkbelasting. Voor de meeste essentiële workloads wordt aangeraden de AlwaysOn-configuratie te gebruiken. In deze configuratie kunt u de impact van het uitvoeren van analyses elimineren door deze te offloaden naar een leesbare secundaire.

Prestatietip 3: Indexfragmentatie verminderen door dynamische gegevens in deltarijgroepen te bewaren

Tabellen met een columnstore index kunnen aanzienlijk gefragmenteerd raken (door het verwijderen van rijen) als de werkbelasting rijen die zijn gecomprimeerd bijwerkt of verwijdert. Een gefragmenteerde columnstore-index leidt tot inefficiënt gebruik van geheugen/opslag. Naast inefficiënt gebruik van resources, heeft dit ook negatieve gevolgen voor de prestaties van de analysequery vanwege extra I/O en de noodzaak om de verwijderde rijen uit de resultatenset te filteren.

De verwijderde rijen worden niet fysiek verwijderd totdat u indexdefragmentatie uitvoert met REORGANIZE opdracht of de columnstore-index opnieuw opbouwt in de hele tabel of de betrokken partitie(s). Zowel de index REORGANIZE als REBUILD zijn dure bewerkingen die middelen onttrekken die anders zouden kunnen worden gebruikt voor de workload. Als rijen te vroeg zijn gecomprimeerd, moet deze mogelijk meerdere keren opnieuw worden gecomprimeerd vanwege updates die leiden tot verspilde compressieoverhead.

U kunt indexfragmentatie minimaliseren met behulp van COMPRESSION_DELAY de optie.

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

Zie Blog: Compressievertraging voor meer informatie.

Dit zijn de aanbevolen procedures.

• Werkbelasting invoegen/query uitvoeren: Als uw workload voornamelijk gegevens invoegt en er query's op uitvoert, is de standaardwaarde COMPRESSION_DELAY van 0 de aanbevolen optie. De zojuist ingevoegde rijen worden gecomprimeerd zodra 1 miljoen rijen zijn ingevoegd in één deltarijgroep. Enkele voorbeelden van dergelijke workloads zijn een traditionele DW-workload of een select-stream-analyse wanneer u het select-patroon in een webtoepassing moet analyseren.

• OLTP-workload: Als de workload DML zwaar is (dat wil gezegd, zware combinatie van update, verwijderen en invoegen), ziet u mogelijk columnstore-indexfragmentatie door de DMV sys.dm_db_column_store_row_group_physical_statste onderzoeken. Als u ziet dat > 10% rijen zijn gemarkeerd als verwijderd in onlangs gecomprimeerde rijgroepen, kunt u de optie gebruiken COMPRESSION_DELAY om tijdsvertraging toe te voegen wanneer rijen in aanmerking komen voor compressie. Als de zojuist ingevoegde werkbelasting bijvoorbeeld 'dynamisch' blijft (dat wil zeggen, wordt het meerdere keren bijgewerkt) voor bijvoorbeeld 60 minuten, moet u ervoor kiezen COMPRESSION_DELAY om 60 te zijn.

De standaardwaarde van COMPRESSION_DELAY de optie moet voor de meeste klanten werken.

Voor geavanceerde gebruikers raden we u aan de onderstaande query uit te voeren en gegevens over % verwijderde rijen gedurende de afgelopen zeven dagen te verzamelen.

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

Als het aantal verwijderde rijen in gecomprimeerde rijgroepen > 20%is, en er een stabilisatie optreedt in oudere rijgroepen met < 5% variatie (ook wel koude rijgroepen genoemd), stel dan COMPRESSION_DELAY in op (youngest_rowgroup_created_time - huidige tijd). Deze aanpak werkt het beste met een stabiele en relatief homogene workload.

Related content

• Columnstore-indexen: overzicht
• Columnstore-indexen - Richtlijnen voor het laden van gegevens
• Columnstore-indexen - Querystatistieken
• Columnstore-indexen in gegevensopslag
• Indexonderhoud optimaliseren om de queryprestaties te verbeteren en het resourceverbruik te verminderen