Dela via

Prestandaoptimering med ordnade kolumnlagrade index

Gäller för: SQL Server 2022 (16.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL Database i Microsoft Fabric

Ordnade kolumnlagringsindex kan ge snabbare prestanda genom att hoppa över stora mängder sorterade data som inte matchar frågepredikatet. När det tar längre tid än i ett icke-ordnat index att läsa in data i ett ordnat kolumnlagringsindex och upprätthålla ordningen via återskapande av index, kan indexerade frågor köras snabbare med ordnat kolumnarkiv.

När en fråga läser ett kolumnlagringsindex kontrollerar databasmotorn de lägsta och högsta värden som lagras i varje kolumnsegment. Processen eliminerar segment som ligger utanför gränserna för frågepredikatet. Med andra ord hoppar det över dessa segment när du läser data från disk eller minne. En fråga slutförs snabbare om antalet segment som ska läsas och deras totala storlek är betydligt mindre.

Med vissa datainläsningsmönster kan data i ett kolumnlagringsindex sorteras implicit utan att ORDER satsen anges. Om data läses in varje dag kan data till exempel sorteras efter en load_date kolumn. I det här fallet kan frågeprestanda redan dra nytta av den här implicita ordningen. Att sortera kolumnlagringsindexet efter samma load_date kolumn explicit i ORDER-satsen kommer troligen inte ge någon extra prestandafördel.

Information om ordnad tillgänglighet för columnstore-index i olika SQL-plattformar och SQL Server-versioner finns i Ordnad tillgänglighet för columnstore-index.

Mer information om nyligen tillagda funktioner för kolumnlagringsindex finns i Nyheter i kolumnlagringsindex.

Ordnat jämfört med icke-ordnat kolumnlagringsindex

I ett kolumnlagringsindex komprimeras data i varje kolumn i varje radgrupp till ett separat segment. Varje segment innehåller metadata som beskriver dess lägsta och högsta värden, så frågekörningsprocessen kan hoppa över segment som ligger utanför gränserna för frågepredikatet.

När ett kolumnlagringsindex inte är ordnat sorterar indexskaparen inte data innan det komprimeras till segment. Det innebär att segment med överlappande värdeintervall kan inträffa, vilket gör att frågor läser fler segment för att hämta nödvändiga data. Därför kan det ta längre tid att slutföra frågor.

När du skapar ett ordnat columnstore-index genom att ORDER ange satsen i instruktionen CREATE COLUMNSTORE INDEX sorterar databasmotorn data i varje segment i varje orderkolumn innan indexverktyget komprimerar data till segment. Med sorterade data minskas eller elimineras segmentöverlappning, vilket gör det möjligt för frågor att använda en effektivare segmenteliminering och därmed snabbare prestanda eftersom det finns färre segment och mindre data att läsa.

Minska segmentöverlappningen och förbättra frågeprestanda

När du skapar ett ordnat columnstore-index sorterar databasmotorn data efter bästa förmåga. Beroende på tillgängligt minne, datastorleken, graden av parallellitet, indextypen (klustrad jämfört med icke-grupperad) och typen av indexversion (offline jämfört med online) kan ordningen i en kolumn i ett kolumnlagringsindex vara full utan segmentöverlappning eller partiell med viss segmentöverlappning. När det finns färre överlappande segment körs en fråga som kan dra nytta av kolumnordningen snabbare.

Tips/Råd

Även om ordningen i en kolumn i ett kolumnlagringsindex är partiell kan segment fortfarande elimineras (hoppas över). En fullständig order krävs inte för att få prestandafördelar om en partiell order undviker många segmentöverlappningar.

I följande tabell beskrivs den resulterande ordertypen när du skapar eller återskapar ett ordnat kolumnlagringsindex, beroende på alternativen för indexgenerering.

Förutsättningar Ordertyp
ONLINE = ON och MAXDOP = 1 Fullständig
ONLINE = OFF, MAXDOP = 1 och de data som ska sorteras helt passar i frågearbetsminnet Fullständig
Alla andra fall Partial

I det första fallet när både ONLINE = ON och MAXDOP = 1, begränsas sorteringen inte av frågearbetsytans minne eftersom en onlineversion av ett ordnat columnstore-index använder tempdb databasen för att spilla data som inte får plats i minnet. Den här metoden kan göra indexgenereringsprocessen långsammare på grund av ytterligare tempdb I/O och kräver tillräckligt med ledigt utrymme i tempdb. Men eftersom indexversionen utförs online kan frågor fortsätta att använda det befintliga indexet medan det nya ordnade indexet skapas.

På samma sätt, med en offline ombyggnad av en partitionerad kolumnlagringsindex, görs ombyggnaden en partition i taget. Andra partitioner är fortfarande tillgängliga för frågor.

När MAXDOP är större än 1 arbetar varje tråd som används för att bygga ett ordnat kolumnlagringsindex på en delmängd data och sorterar den lokalt. Det finns ingen global sortering mellan data sorterade efter olika trådar. Att använda parallella trådar kan minska tiden för att skapa indexet, men det resulterar i fler överlappande segment än när du använder en enda tråd.

Du kan bara skapa eller återskapa ordnade kolumnlagringsindex online på vissa SQL-plattformar och SQL Server-versioner. Mer information finns i Funktionssammanfattning för produktversioner.

I SQL Server är onlineindexåtgärder inte tillgängliga i alla utgåvor. Mer information finns i Utgåvor och funktioner som stöds i SQL Server 2025 och Utför indexåtgärder online.

För vissa datatyper och kodningar kan sys.column_store_segments systemvyn hjälpa dig att hitta antalet segment överlappningar. Ett exempelskript baserat på den här vyn avgör orderkvaliteten för berättigade kolumner för alla kolumnlagringsindex i den aktuella databasen.

Frågeprestanda

Prestandavinsten från ett ordnat kolumnlagringsindex beror på frågemönstren, storleken på data, antalet överlappande segment och de beräkningsresurser som är tillgängliga för frågekörning.

Frågor med följande mönster körs vanligtvis snabbare med sorterade columnstore-index:

  • Frågor som har likhet, ojämlikhet eller intervallpredikat.
  • Frågor där predikatkolumnerna och de ordnade CCI-kolumnerna är desamma.

I följande exempel har tabellen T1 ett grupperat kolumnlagringsindex med Col_C, Col_Boch Col_A som ordnade kolumner.

CREATE CLUSTERED COLUMNSTORE INDEX OrderedCCI
ON T1
ORDER (Col_C, Col_B, Col_A);

Fråga 1 drar nytta av det ordnade kolumnlagringsindexet mer än frågorna 2 och 3, eftersom fråga 1 refererar till alla ordnade kolumner i predikatet.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c'
      AND Col_B = 'b'
      AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b'
      AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_A = 'a'
      AND Col_C = 'c';

Prestanda för dataöverföring

Prestandan för en datainläsning i en tabell med ett ordnat kolumnlagringsindex liknar en partitionerad tabell. Inläsning av data kan ta längre tid än med ett icke-ordnat kolumnlagringsindex på grund av datasorteringsåtgärden, men frågor kan köras snabbare efteråt.

Lägga till nya data eller uppdatera befintliga data

De nya data som härrör från en DML-batch eller en massinläsningsåtgärd i en tabell med ett ordnat kolumnlagringsindex sorteras endast inom den batchen. Det finns ingen global sortering som innehåller befintliga data i tabellen eftersom komprimerade radgrupper i ett kolumnlagringsindex är oföränderliga.

Om du vill minska segment överlappningen när du har infogat nya data eller uppdaterat befintliga data återskapar du kolumnlagringsindexet.

Examples

Skapa ett ordnat kolumnlagringsindex

Klustrat ordnat columnstore-index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1, Column2);

Icke-grupperat ordnat kolumnlagringsindex:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1(Column1, Column2, Column3)
ORDER(Column1, Column2);

Sök efter ordnade kolumner och ordningsföljd

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
     INNER JOIN sys.columns AS c
         ON i.object_id = c.object_id
        AND c.column_id = i.column_id
WHERE column_store_order_ordinal > 0;

Lägga till eller ta bort orderkolumner och återskapa ett befintligt ordnat kolumnlagringsindex

Klustrat ordnat columnstore-index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1, Column2)
WITH (DROP_EXISTING = ON);

Icke-grupperat ordnat kolumnlagringsindex:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1(Column1, Column2, Column3)
ORDER(Column1, Column2)
WITH (DROP_EXISTING = ON);

Skapa ett ordnat grupperat columnstore-index online med full ordning på en heap-tabell

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Återskapa ett ordnat grupperat columnstore-index online med fullständig ordning

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Relaterat innehåll

  • Last updated on