Delen via

Columnstore-indexen in datawarehousing

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

Columnstore-indexen, in combinatie met partitionering, zijn essentieel voor het bouwen van een SQL Server-datawarehouse. Dit artikel is gericht op belangrijke use cases en voorbeelden voor datawarehousingontwerpen met de SQL Database Engine.

Belangrijkste functies voor gegevensopslag

SQL Server 2016 (13.x) heeft deze functies geïntroduceerd voor prestatieverbeteringen in columnstore:

  • AlwaysOn-beschikbaarheidsgroepen ondersteunen het opvragen van een columnstore-index op een leesbare secundaire replica.
  • Mars (Multiple Active Result Sets) ondersteunt columnstore-indexen.
  • Een nieuwe dynamische beheerweergave sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) biedt informatie over het oplossen van prestatieproblemen op rijgroepsniveau.
  • Alle queries in columnstore-indexen kunnen worden uitgevoerd in batch-modus. Voorheen konden alleen parallelle query's worden uitgevoerd in de batchmodus.
  • De operators Sort, Distinct Sort en Distinct worden uitgevoerd in de batchmodus.
  • Vensteraggregaties worden nu uitgevoerd in batchmodus voor databasecompatibiliteitsniveau 130 en hoger.
  • Aggregaten doorstuwing voor efficiënte verwerking van samengevoegde gegevens. Dit wordt ondersteund op alle databasecompatibiliteitsniveaus.
  • Pushdown van tekenreekspredicaat voor efficiënte verwerking van tekenreekspredicaten. Dit wordt ondersteund op alle databasecompatibiliteitsniveaus.
  • Isolatie van momentopnamen voor databasecompatibiliteitsniveau 130 en hoger.
  • Geordende columnstore-indexen zijn geïntroduceerd met SQL Server 2022 (16.x). Zie CREATE COLUMNSTORE INDEX en Performance tuning met geordende columnstore-indexenvoor meer informatie. Zie Beschikbaarheid van geordende kolomindexenvoor geordende beschikbaarheid van columnstore-indexen.

Zie Wat is er nieuw in columnstore-indexenvoor meer informatie over nieuwe functies in versies en platforms van SQL Server en Azure SQL.

Prestaties verbeteren door niet-geclusterde en columnstore-indexen te combineren

Vanaf SQL Server 2016 (13.x) kunt u niet-geclusterde rijenopslagindexen maken op een geclusterde columnstore-index.

Voorbeeld: De efficiëntie van tabelzoekopdrachten verbeteren met een niet-geclusterde index

Om de efficiëntie van tabelzoekopdrachten in een datawarehouse te verbeteren, kunt u een niet-geclusterde index maken die is ontworpen om query's uit te voeren die het beste presteren met tabelzoekopdrachten. Query's die zoeken naar overeenkomende waarden of een klein bereik met waarden retourneren, presteren bijvoorbeeld beter ten opzichte van een B-tree-index in plaats van een columnstore-index. Ze hebben geen volledige scan van de columnstore-index nodig en retourneren het juiste resultaat sneller door een binaire zoekopdracht uit te voeren via een B-tree-index.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Voorbeeld: Een niet-geclusterde index gebruiken om een primaire-sleutelbeperking af te dwingen voor een columnstore-tabel

Omdat een tabel maximaal één geclusterde index kan hebben, kan een tabel met een geclusterde columnstore-index geen beperking voor een geclusterde primaire sleutel hebben. Als u een primaire-sleutelbeperking wilt maken voor een columnstore-tabel, moet u deze declareren als niet-geclusterd.

In het volgende voorbeeld wordt een tabel met een niet-geclusterde primaire-sleutelbeperking gemaakt en wordt vervolgens een geclusterde columnstore-index in de tabel gemaakt. Aangezien elke invoeg- of update in de columnstore-tabel ook de niet-geclusterde index wijzigt, worden alle bewerkingen die de primaire-sleutelbeperking schenden, ertoe leiden dat de hele bewerking mislukt.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Prestaties verbeteren door vergrendeling op rij- en rijgroepniveau in te schakelen

Sql Server 2016 (13.x) biedt gedetailleerde vergrendelingsmogelijkheden voor SELECT, UPDATEen DELETE bewerkingen om de niet-geclusterde index op een columnstore-functie aan te vullen. Query’s kunnen worden uitgevoerd met vergrendeling op rijniveau tijdens index zoekopdrachten tegen een niet-geclusterde index en vergrendeling op rijgroepsniveau bij volledige tabelscans tegen de columnstore-index. Gebruik dit om een hogere lees-/schrijfconcurrentie te bereiken door op geschikte wijze vergrendelingen op rij- en rijgroepniveau toe te passen.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

Isolatie van momentopnamen en isolatie van vastgelegde momentopnamen

Gebruik momentopname-isolatie (SI) om transactionele consistentie te garanderen en RCSI (Read-Committed Snapshot Isolation) om consistentie op instructieniveau te garanderen voor query's in columnstore-indexen. Hierdoor kunnen de query's worden uitgevoerd zonder gegevensschrijvers te blokkeren. Dit niet-blokkerende gedrag vermindert ook de kans op impasses voor complexe transacties aanzienlijk. Voor meer informatie, zie isolatieniveaus op basis van rijversies in het databasesysteem.

Related content