Blogginlägg -
Ett effektivare datalager med SQL Server 2014
Clustered Columnstore Index
Dagens blogginlägg handlar om ett fascinerande sätt att lagra och hantera information i Microsoft SQL Server, eller vad sägs om att i datawarehousemiljö nå upp till 10x prestanda i frågor och 7x komprimering jämfört med traditionell metod? Jag pratar givetvis om Clustered Columnstore Indexes (CCI) som är en nyhet i SQL Server 2014.
Konceptet handlar i stort om att dela upp en tabell i dess kolumner och komprimera dem. Detta kommer, som vi ska se senare, få en rad fördelaktiga effekter. Idén är dock inte på något sätt ny. Det finns exempel på applikationer som använt tekniken redan sedan år 1969. Microsoft är heller inte ensam med att utnyttja Columnstores. De återfinns även i produkter från t.ex. Oracle och SAP.
Många av er kanske känner igen namnet Columnstore index. Förmodligen från SQL Server 2012 som erbjuder en nonclustered variant av samma teknik. Eller det faktum att Microsoft har själva låtit Columnstores driva en del funktioner i t.ex. PowerPivot för Excel. Den största skillnaden i den senaste versionen är att man nu tillåter modifikation av data. Tidigare har det inte gått att göra varken insert, update eller delete i samband med nonclustered columnstore index. Dessa begränsningar, misstänker jag, varit en stor anledning till att användandet hittills varit sparsamt.
I SQL Server 2014 är det fritt fram att arbeta som man är van vid från tidigare versioner av SQL Server. Innan man början kan det däremot vara nyttigt att veta hur det ser ut under huven.
Vad är Columnstore och hur funkar det?
Columnstore är en radikal ändring på hur data normalt hanteras i en databas. Istället för att lagra data rad för rad i traditionell så kallad row store delar man upp tabellen i kolumner. Nedan bifogad bild illustrerar stegen från vanlig row store tabell till färdigt komprimerat Columnstore.
- Utgå från en tabell i SQL Server, med eller utan klassiska index. Tekniken lämpar sig bra till faktatabeller i ett datalager som tenderar att vara stora, både innehålla många rader samt många kolumner.
- Dela tabellen i grupper om cirka 1 miljon rader kallat Row Groups.
- Dela sedan varje Row Group i dess ingående kolumner. Detta bildar många segment med 1 miljon värden med snarlikt data från samma kolumn i ursprungstabellen.
- Segmenten komprimeras individuellt och läggs till i ett Columnstore.
Figur1: Radbaserad tabell delas och komprimeras till ett Columnstore (Källa: Microsoft)
Som Figur1 antyder beror komprimeringen, och hur effektivt den blir på indatat. Här ligger dock en stor förklaring till varför Columnstores kan utlova flera multiplar i ökad prestanda och komprimering. Följande påstående ger förhoppningsvis en aha-upplevelse. Normalt har värden i en kolumn mycket mer gemensamt med varandra än värden på en rad. Detta medför att Columnstore är ett väldigt effektivt sätt att lagra information på. Man utnyttjar bland annat att data ofta upprepar sig i en kolumn med att ersätta de faktiska värdena med pekare till ett uppslagsverk. På bilden ovan ser vi också ett Deltastore. Det är en radbaserad struktur dit nya rader läggs till innan den blir stor nog att själv automatiskt komprimeras och införlivas i samma Columnstore. Vad som inte syns är hanteringen av borttagna rader. Det sker inte direkt mot ett Columnstore, istället markeras raden som borttagen och försvinner först när indexet byggs om.
Vad vinner man?
Ämnar man köra massor med små läs- och skrivoperationer har man kommit fel. Där kommer Columnstore-tekniken troligtvis ge direkt motverkande effekt på prestanda. Detta är dock en BI-blogg där vi antar ett mer analytiskt användande av databasen i form av ett datalager. Något som Columnstores är designade för. Givet stora faktatabeller i datalager är CCI numera standard enligt Microsoft. Ett datalager utsätts ofta för frågor som till exempel ”summera total försäljning” eller ”vilket är min/max värde i följande kolumn”. Vanligtvis måste databasen besöka hela tabellen för att kunna svara på liknande frågor. Resultat, en fruktade table scan. Är tabellen stor måste den i värsta fall läsas in från disk till minne vilket i sammanhanget tar väldigt lång tid.
Givet rätt förutsättningar kan Clustered Columnstore Indexes motverka fenomenet ovan genom:
- Mindre tabeller. Komprimeringen gör att mindre data måste läsas in från långsamt media. Mindre data ökar också chansen för att det får plats i minnet.
- Möjligheten att eliminera segment och hela kolumner. Segmenten, som nu utgör tabellen är påpassligt nog märkta med metadata angående min/max värden och antal rader. Kolumner som inte ingår i frågan elimineras helt enkelt. En fråga som bara behandlar 5 av 50 kolumner kan reducera mängden data att skyffla till 10 procent av ursprunglig storlek.
I inledningen nämnde jag att Microsoft själva skriver upp till 10 gånger så snabba frågor till 7 gånger så lite utrymme. Faktum är att det inte är svårt att slå det. Resultatet beror givetvis på hur indatat och frågan ser ut, men på min egen laptop landar komprimeringen ungefär på par medan frågeprestandan går mångdubbelt snabbare än utlovat.
Hur gör man för att testa själv?
Microsoft låter dig testa SQL Server 2014 gratis, den återfinns att ladda ner här. Förutsatt att du har en tabell att utgå från vid namn T1 återstår det inte annat än att skapa indexet:
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
GO
Ditt nya index cci_T1 spänner alltid över alla kolumner. Det är nu det enda möjliga indexet. Faktum är att indexet är din nya tabell och den gamla tabellen kan T1 droppas.
Att tänka på
Clustered Columnstore Index kan erbjuda stora vinster om de används på rätt sätt. Där finns en del begränsningar som bör undersökas innan man kör igång. Till exempel stöds inte alla datatyper ännu, ej heller constraints och triggers. Vidare återfinns funktionen bara i Enterprise-versionen av SQL Server. En annan aspekt är underhåll. Tekniken beskrivs som lättare att underhålla på grund av minskat antal index. Underhåll sker dock inte automatiskt, du (eller din databasadministratör) får själv monitorerna hur dina Columnstores mår.
Läs mer om ämnet hos Microsoft eller varför inte en serie om 49 delar hos Nikoport. Det finns också en uppsjö av material på YouTube.
Emil Persson, Business Intelligence-konsult, Enfo Pointer
Mer om Microsoft:
Ämnen
- Datorer, datateknik, programvaror
