Dela via

Kolumnlagringsindex i datalager

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL Database i Microsoft Fabric

Kolumnlagringsindex, tillsammans med partitionering, är viktiga för att skapa ett SQL Server-informationslager. Den här artikeln fokuserar på viktiga användningsfall och exempel för datalagerdesign med SQL Database Engine.

Viktiga funktioner för datalagerhantering

SQL Server 2016 (13.x) introducerade dessa funktioner för prestandaförbättringar i columnstore:

  • Always On stöder att fråga ett columnstore-index på en läsbar sekundärreplik.
  • Flera aktiva resultatuppsättningar (MARS) stöder kolumnlagringsindex.
  • En ny dynamisk hanteringsvy sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) ger information om prestandafelsökning på radgruppsnivå.
  • Entrådade frågor i columnstore-index kan köras i batchläge. Tidigare kunde endast frågor med flera trådar köras i batchläge.
  • Operatorn SORT körs i batchläge.
  • Flera DISTINCT åtgärder körs i batchläge.
  • Fönsteraggregat körs nu i batchläge för databaskompatibilitetsnivå 130 och senare.
  • Effektiv bearbetning av aggregat med hjälp av pushdown. Detta stöds på alla databaskompatibilitetsnivåer.
  • Optimering av predikatberäkning för effektiv bearbetning av strängpredikat. Detta stöds på alla databaskompatibilitetsnivåer.
  • Isolering av ögonblicksbilder för databaskompatibilitetsnivå 130 och senare.
  • Ordnade grupperade columnstore-index introducerades med SQL Server 2022 (16.x). Mer information finns i CREATE COLUMNSTORE INDEX och Performance tuning with ordered columnstore indexes. Information om tillgänglighet för ordnat columnstore-index finns i Ordnad kolumnindextillgänglighet.

Mer information om nya funktioner i versioner och plattformar för SQL Server och Azure SQL finns i Nyheter i kolumnlagringsindex.

Förbättra prestanda genom att kombinera icke-grupperade index och kolumnlagringsindex

Från och med SQL Server 2016 (13.x) kan du definiera icke-grupperade index för radarkiv i ett grupperat kolumnlagringsindex.

Exempel: Förbättra effektiviteten för tabellsökningar med ett icke-grupperat index

För att förbättra effektiviteten för tabellsökningar i ett informationslager kan du skapa ett icke-grupperat index som är utformat för att köra frågor som presterar bäst med tabellsökningar. Till exempel fungerar frågor som letar efter matchande värden eller returnerar ett litet värdeintervall bättre mot ett B-trädindex i stället för ett kolumnlagringsindex. De kräver inte en fullständig tabellgenomsökning via kolumnlagringsindexet och returnerar rätt resultat snabbare genom att göra en binär sökning via ett B-trädindex.

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

Exempel: Använd ett icke-grupperat index för att framtvinga en primärnyckelbegränsning för en kolumnlagringstabell

Enligt utformningen tillåter en kolumnlagringstabell inte en klustrad primärnyckelbegränsning. Nu kan du använda ett icke-grupperat index i en kolumnlagringstabell för att framtvinga en primärnyckelbegränsning. En primärnyckel motsvarar ett UNIKT villkor för en icke-NULL-kolumn, och SQL Server implementerar en UNIK begränsning som ett icke-grupperat index. Genom att kombinera dessa fakta definierar följande exempel en UNIK begränsning för kontonyckeln för icke-NULL-kolumnen. Resultatet är ett icke-grupperat index som framtvingar en primärnyckelbegränsning som en UNIK begränsning för en icke-NULL-kolumn.

Därefter konverteras tabellen till ett grupperat kolumnlagringsindex. Under konverteringen bevaras det icke-klustrade indexet. Resultatet är ett grupperat columnstore-index med ett icke-grupperat index som framtvingar en primärnyckelbegränsning. Eftersom alla uppdateringar eller infogningar i kolumnlagringstabellen också påverkar det icke-grupperade indexet, gör alla åtgärder som bryter mot den unika begränsningen och icke-NULL att hela åtgärden misslyckas.

Resultatet är ett columnstore-index med ett icke-grupperat index som tillämpar en primärnyckelbegränsning för båda 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);

Förbättra prestanda genom att aktivera låsning på radnivå och radgruppsnivå

För att komplettera det icke-klustrade indexet på en kolumnlagringsindexfunktion erbjuder SQL Server 2016 (13.x) en detaljerad låsning för val, uppdatering och radering av operationer. Frågor kan köras med låsning på radnivå för indexsökningar mot ett icke-grupperat index och låsning på radgruppsnivå vid fullständiga tabellgenomsökningar mot kolumnlagringsindexet. Använd detta för att uppnå högre samtidighet mellan läsning och skrivning med hjälp av låsning på radnivå och radgruppsnivå på lämpligt sätt.

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

Isolering av ögonblicksbilder och lästillägnad ögonblicksbildisolering

Använd ögonblicksbildisolering (SI) för att garantera transaktionskonsekvens och skrivskyddad ögonblicksbildisolering (RCSI) för att garantera konsekvens på instruktionsnivå för frågor om kolumnlagringsindex. Detta gör att frågorna kan köras utan att blockera dataskrivare. Det här icke-blockerande beteendet minskar också sannolikheten för dödlägen för komplexa transaktioner avsevärt. Mer information finns i Ögonblicksbildisolering i SQL Server.

Relaterat innehåll