Delen via

Columnstore-indexen in datawarehousing

  • Artikel

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

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 ondersteunt het uitvoeren van query's op 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.
  • Query's met één thread in columnstore-indexen kunnen worden uitgevoerd in de batchmodus. Voorheen konden alleen query's met meerdere threads worden uitgevoerd in de batchmodus.
  • De operator SORT wordt uitgevoerd in de batchmodus.
  • Meerdere DISTINCT-bewerkingen worden uitgevoerd in de batchmodus.
  • Vensteraggregaties worden nu uitgevoerd in batchmodus voor databasecompatibiliteitsniveau 130 en hoger.
  • Aggregate Pushdown voor een efficiënte verwerking van aggregaties. 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 definiëren 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 tabelscan nodig via de columnstore-index 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  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--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

Een columnstore-tabel staat vanwege het ontwerp geen geclusterde primaire sleutelbeperking toe. U kunt nu een niet-geclusterde index in een columnstore-tabel gebruiken om een primaire-sleutelbeperking af te dwingen. Een primaire sleutel is gelijk aan een UNIEKE beperking in een niet-NULL-kolom en SQL Server implementeert een UNIEKE beperking als een niet-geclusterde index. Door deze feiten te combineren, definieert het volgende voorbeeld een UNIEKE beperking voor de niet-NULL-kolomaccountsleutel. Het resultaat is een niet-geclusterde index die een primaire-sleutelbeperking afdwingt als een UNIEKE beperking op een niet-NULL-kolom.

Vervolgens wordt de tabel geconverteerd naar een geclusterde columnstore-index. Tijdens de conversie blijft de niet-geclusterde index behouden. Het resultaat is een geclusterde columnstore-index met een niet-geclusterde index die een primaire-sleutelbeperking afdwingt. Aangezien elke update of insert op de columnstore-tabel ook de niet-geclusterde index beïnvloedt, mislukken alle bewerkingen die de unieke beperking of de niet-NULL-voorwaarde schenden, waardoor de hele bewerking faalt.

Het resultaat is een columnstore-index met een niet-geclusterde index die een primaire-sleutelbeperking afdwingt voor beide indexen.

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

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

SQL Server 2016 (13.x) biedt gedetailleerde vergrendelingsmogelijkheden voor selectie-, update- en verwijderbewerkingen om de niet-geclusterde index op een columnstore-indexfunctie 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  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
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. Zie Isolatie van momentopnamen in SQL Servervoor meer informatie.

Verwante inhoud