Columnstore-indekser og partisjonering: Store tabeller i SQL Server
Når en tabell passerer noen hundre millioner rader, begynner vanlige indekser å komme til kort. Rapporter som summerer over et helt år tar minutter, og det å slette gamle data blir en nattlig kamp mot transaksjonsloggen. SQL Server har to verktøy for store tabeller: columnstore-indekser, som gjør analysespørringer dramatisk raskere, og partisjonering, som gjør store tabeller enklere å forvalte. De løser forskjellige problemer, og det er lett å forveksle dem. Denne guiden forklarer når hvert av dem lønner seg – og når de ikke gjør det.
- Columnstore lagrer data kolonne for kolonne, komprimert. Det gir ofte 10 ganger mindre data og mye raskere aggregering over mange rader.
- Columnstore passer for analyse og rapportering, ikke for oppslag på enkeltrader.
- Partisjonering er først og fremst et forvaltningsverktøy: lasting og sletting av hele perioder på sekunder. Det gjør sjelden spørringer raskere av seg selv.
- Kombinasjonen – en partisjonert tabell med clustered columnstore – er standardoppskriften for store faktatabeller.
Radlagring mot kolonnelagring
En vanlig tabell (rowstore) lagrer hele rader sammen på 8 KB-sider. Det er perfekt for å hente eller endre én ordre med alle kolonnene. Men skal du summere Beløp for 500 millioner salgsrader, må SQL Server lese alle sidene – også alle de andre kolonnene du ikke bryr deg om.
En columnstore-indeks snur dette på hodet. Dataene deles i radgrupper på inntil 1 048 576 rader, og innenfor hver radgruppe lagres hver kolonne for seg som et komprimert segment. Det gir tre store gevinster:
- Mindre I/O: Spørringen leser bare kolonnene den bruker.
- Komprimering: Like verdier i samme kolonne komprimerer svært godt, ofte 5–10 ganger.
- Segmenteliminering: Hvert segment har min- og maksverdi. Spør du etter mars 2026, hoppes segmenter som bare inneholder 2024 over.
Batch mode
Den fjerde – og ofte største – gevinsten er batch mode. I stedet for å behandle én rad om gangen behandler operatorene i kjøreplanen rundt 900 rader om gangen, med vektoriserte CPU-instruksjoner. Aggregering og join-er kan bli mange ganger raskere. Fra SQL Server 2019 (kompatibilitetsnivå 150) kan batch mode også brukes på vanlige rowstore-tabeller når optimereren mener det lønner seg. Du ser det i kjøreplanen som Actual Execution Mode: Batch på operatorene.
Clustered eller nonclustered columnstore?
| Type | Hva det er | Passer for |
|---|---|---|
| Clustered columnstore (CCI) | Hele tabellen lagres som columnstore. Ingen rowstore-kopi. | Faktatabeller i datavarehus, logg- og hendelsestabeller, historikk som mest leses i bulk. |
| Nonclustered columnstore (NCCI) | En columnstore-kopi av utvalgte kolonner ved siden av en vanlig tabell. | Sanntidsanalyse på en OLTP-tabell: oppslag går mot rowstore, rapporter mot columnstore. |
-- Faktatabell som columnstore fra start
CREATE TABLE dbo.Salg
(
SalgId bigint NOT NULL,
Dato date NOT NULL,
ButikkId int NOT NULL,
ProduktId int NOT NULL,
Antall int NOT NULL,
Beløp decimal(12,2) NOT NULL,
INDEX CCI_Salg CLUSTERED COLUMNSTORE
);
-- Analysekopi på en eksisterende OLTP-tabell
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Ordrelinje
ON dbo.Ordrelinje (OrdreDato, ProduktId, Antall, Pris);
Fra SQL Server 2022 kan en clustered columnstore også være sortert (ORDER (Dato)). Da overlapper segmentene mindre, og segmentelimineringen blir langt mer effektiv for filtre på den kolonnen.
Deltastore og hvorfor lasting bør skje i bulk
Columnstore er optimalisert for store innsettinger. Setter du inn færre enn 102 400 rader om gangen, havner de i en deltastore – en vanlig rowstore-struktur – som en bakgrunnsprosess (tuple mover) senere komprimerer. Oppdateringer er i praksis sletting pluss innsetting, og slettede rader blir bare markert som slettet.
Mange små innsettinger og hyppige oppdateringer gir derfor dårlig komprimerte, «halvfulle» radgrupper. Sjekk tilstanden slik:
SELECT OBJECT_NAME(object_id) AS Tabell,
partition_number,
state_desc,
COUNT(*) AS Radgrupper,
SUM(total_rows) AS Rader,
SUM(deleted_rows) AS SlettedeRader,
AVG(total_rows) AS SnittPerRadgruppe
FROM sys.dm_db_column_store_row_group_physical_stats
GROUP BY object_id, partition_number, state_desc
ORDER BY Tabell, partition_number;
Er det mange små radgrupper eller mange slettede rader, slår ALTER INDEX ... REORGANIZE sammen radgrupper og fjerner slettede rader. Les mer om indeksvedlikehold i guiden om vedlikehold.
Partisjonering
Partisjonering deler én logisk tabell i flere fysiske deler etter en kolonne – nesten alltid en dato. Tabellen ser lik ut for spørringene, men SQL Server kan håndtere hver partisjon for seg. Partisjonering er tilgjengelig i alle utgaver fra SQL Server 2016 SP1.
-- 1. Funksjonen bestemmer grensene (én partisjon per måned)
CREATE PARTITION FUNCTION pfMåned (date)
AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01');
-- 2. Skjemaet bestemmer filgruppene
CREATE PARTITION SCHEME psMåned
AS PARTITION pfMåned ALL TO ([PRIMARY]);
-- 3. Tabellen (eller indeksen) opprettes på skjemaet
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Salg
ON dbo.Salg
WITH (DROP_EXISTING = ON)
ON psMåned (Dato);
Sliding window: inn med ny måned, ut med den eldste
Den store gevinsten er at hele partisjoner kan flyttes inn og ut som en ren metadataoperasjon. Å slette fem år gamle data tar sekunder i stedet for timer, og genererer nesten ingen logg:
-- Tøm den eldste partisjonen umiddelbart
TRUNCATE TABLE dbo.Salg WITH (PARTITIONS (1));
-- Slå sammen den tomme grensen
ALTER PARTITION FUNCTION pfMåned() MERGE RANGE ('2026-01-01');
-- Lag plass til neste måned (gjør dette mens den nye partisjonen er tom)
ALTER PARTITION SCHEME psMåned NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pfMåned() SPLIT RANGE ('2026-04-01');
-- Last data i en stagingtabell med samme struktur, og bytt den inn
ALTER TABLE dbo.Salg_Staging SWITCH TO dbo.Salg PARTITION 4;
Partisjonseliminering
Filtrerer spørringen på partisjoneringskolonnen, leser SQL Server bare de aktuelle partisjonene. Det kan hjelpe, men en vanlig indeks på datoen gir ofte samme effekt. Filtrerer spørringen ikke på partisjoneringskolonnen, kan partisjonering til og med gjøre den tregere, fordi hver partisjon må søkes for seg.
Vanlige misforståelser
- «Partisjonering gjør alt raskere.» Nei. Det er et forvaltningsverktøy. For ytelse på enkeltspørringer er riktige indekser nesten alltid viktigere.
- «Columnstore passer overalt.» Nei. Oppslag på én rad via primærnøkkel er tregere mot columnstore enn mot en vanlig B-tre-indeks, og hyppige enkeltoppdateringer gir fragmentering.
- «Flere partisjoner er bedre.» Tusenvis av partisjoner gir dyrere kompilering og mer arbeid for optimereren. Velg en granularitet som passer forvaltningen – ofte måned.
- «Columnstore trenger ikke statistikk.» Jo, statistikken må fortsatt holdes oppdatert.
Vanlige spørsmål
Hvor stor må en tabell være før columnstore lønner seg?
Som tommelfingerregel noen millioner rader eller mer, og spørringer som leser en stor andel av dem. Under én million rader får du bare én radgruppe, og gevinsten er liten.
Kan jeg ha primærnøkkel på en clustered columnstore-tabell?
Ja, fra SQL Server 2016 kan du legge en vanlig nonclustered B-tre-indeks (for eksempel en primærnøkkel) oppå en clustered columnstore. Det gir raske oppslag på enkeltrader, men koster ved lasting.
Hjelper partisjonering mot blokkering?
Det kan, med LOCK_ESCALATION = AUTO, som lar SQL Server eskalere låser til partisjonsnivå i stedet for hele tabellen. Les mer i guiden om blokkering og deadlocks.
Hvordan ser jeg at columnstore faktisk brukes?
Se i den faktiske kjøreplanen etter Columnstore Index Scan og batch mode. Sammenlign gjerne før og etter med Query Store.