SQL Server 2014 Nyheter del 3 – In-Memory DW

By | augusti 24, 2014

Detta är del 3 i min miniserie om SQL Server 2014 nyheter. Övriga delar finns här.

SQL Server 2014 har två nya ”In-Memory” teknologier, som Microsoft kallar det. Själv är jag inte riktigt förtjust i terminologin eftersom det lätt kan leda till missuppfattningar att det skulle vara två varianter av samma sak. Det är det inte alls. Här är samma bild igen som i del 2 (klicka på bilden för att förstora) av In-Memory OLTP och In-Memory DW.

SQL Server In-Memory OLTP/DW

Båda teknologierna kräver att du har ordentligt med minne för din SQL Server, men därefter slutar likheterna.

In-Memory DW

Traditionellt så har data lagrats rad för rad i SQL Server. Det finns dock, i vissa fall, mycket stora fördelar med att lagra data en kolumn i taget istället. Om du exempelvis har en tabell med väldigt många kolumner och dina SQL-frågor bara berörs av några få kolumner så kan du på så sätt skippa alla andra kolumner. För stora tabeller kan detta göra en enorm skillnad i prestanda.

Kolumndatabaser är inget nytt eller unikt för SQL Server. Det som däremot är en stor fördel i SQL Server är att man slipper göra några större förändringar i sina befintliga lösningar. Dessutom är prestandan och komprimeringsgraden i absolut toppklass jämfört med lite äldre kolumndatabaslösningar.

In-Memory DW snabbare analys

Teknologin kom redan i SQL Server 2012 och hette då Columnstore indexes. I 2014 är den stora nyheten CLUSTERED Columnstore Indexes, vilket skiljer sig väsentligt åt från tidigare.

Columnstore Indexes i SQL Server 2012

I SQL Server 2012 så var Columnstore ett extra index som man kunde skapa på en tabell för att höja prestandan för vissa typer av sökningar.

  • Det var alltså ett extra (nonclustered) index man kunde lägga till.
  • Det innebär, precis som för alla andra nonclustered index, att ytterligare diskutrymme krävs. Det kan inte användas för att komprimera tabeller.
  • Det är readonly. Tabellen som man lägger det på blir då också readonly. Enda sättet att uppdatera tabellen är genom att först göra disable eller drop på indexet. Eller använda partition switching (lite mer avancerad metod).

I Books Online finns en mer detaljerad beskrivning av Nonclustered Columnstore Indexes.

Columnstore Indexes i SQL Server 2014

I SQL Server 2014 finns både nonclustered och clustered Columnstore indexes som alternativ. Clustered columnstore indexes brukar förkortas CCI och skiljer sig åt genom att

  • Tabeller med ett CCI lagras enbart kolumnvis. Det finns inget ”original” kvar som ligger radvis så som för nonclustered columnstore indexes.
  • Man kan inte ha några andra index samtidigt på en tabell med CCI.
  • Man kan inte ha någon primärnyckel på en tabell med CCI. Vill man garantera att en kolumn endast har unika värden så får man lösa det på andra sätt (finns flera lösningar).
  • Man kan heller inte ha några Foreign Keys från en tabell med ett CCI.
  • Det innebär att tabeller blir rejält komprimerade. Särskilt väl fungerar det i Data Warehouse, där många kolumner har en upprepning av samma värden. För avancerade användare: det kan löna sig att sortera tabeller på rätt sätt innan de skrivs till ett CCI för att få maximal kompression.
  • Det går att göra INSERT, UPDATE, DELETE på en CCI. Alltså inga begränsningar att man inte får ändra data. I själva verket görs förändringar i en så kallad ”delta store”. Det finns sedan en bakgrundsprocess som applicerar dessa förändringar senare på själva huvudindexet. För att prestandan skall bibehållas kan man behöva göra en rebuild på sitt CCI ibland.

Det finns en mer detaljerad beskrivning av Clustered Columnstore Indexes i Books Online.

Jag skall nu visa ett exempel på hur det fungerar.

Skapa tabeller för testdata

I detta scenario har jag två tabeller: FactA och DimA. Vi kan tänka oss detta som ett mycket enkelt Data Warehouse. För enkelhets skull så skapar jag ingen tabell DimB, utan använder även DimA där jag skulle ha använt DimB.

CREATE TABLE dbo.FactA
(
	FactA_ID INT NOT NULL,
	DimA_ID INT NOT NULL,
	DimB_ID INT NOT NULL,
	Belopp DECIMAL(8,2) NOT NULL
);

CREATE TABLE dbo.DimA
(
	DimA_ID INT PRIMARY KEY CLUSTERED,
	Beskrivning NVARCHAR(100) NOT NULL
);

Fylla på med testdata i DimA

INSERT INTO dbo.DimA(DimA_ID, Beskrivning)
VALUES (1, 'SQL Server'), (2, 'MVP'), (3, 'Johan'), (4, '2014');

Fylla på med 100 miljoner rader med testdata i FactA

Jag skapar först en tabell som heter #Tmp med 1 miljon rader och kopierar sedan in den 100 gånger i FactA via loopen nedan. Totalt tar det cirka 100 sekunder på min dator att skapa detta 100 miljoner rader med testdata.

WITH Tmp AS
(
	SELECT 1 AS ID
	UNION ALL
	SELECT ID+1
	FROM Tmp
	WHERE ID < 1000
)
SELECT 
	(t2.ID*1000 + t1.ID - 1000) AS ID, 
	((t1.ID + t2.ID) % 4) + 1 DimA_ID, 
	((t1.ID + t2.ID * 3) % 4) + 1 DimB_ID, 
	RAND(ABS(CHECKSUM(NEWID()))) Belopp
INTO #Tmp
FROM Tmp t1
CROSS JOIN Tmp t2
OPTION (MAXRECURSION 0);

DECLARE @i int = 0;
WHILE (@i < 100)
BEGIN
	PRINT @i;

	INSERT INTO dbo.FactA WITH (TABLOCK) (FactA_ID, DimA_ID, DimB_ID, Belopp)
	SELECT ID + @i * 1000000, DimA_ID, DimB_ID, Belopp
	FROM #Tmp;

	SET @i = @i + 1;
END

Provköra utan columnstore index

Låt oss testa en ganska typisk Data Warehouse-fråga där vi summerar Belopp och grupperar på DimA.

select d.Beskrivning, count(*), sum(belopp)
from dbo.FactA AS f
join dbo.DimA AS d on f.DimA_ID = d.DimA_ID
group by d.Beskrivning;

Denna tar cirka 20 sekunder att köra på min dator och tabellen FactA tar cirka 2,5 Gigabyte i diskutrymme. Vi får en Execution Plan enligt nedan (klicka på den för att förstora).

SQL Server 2014 Roadshow plan 1

Provköra med nonclustered columnstore index

För att lägga på ett nonclustered columnstore index (sådant som finns sedan SQL Server 2012) så skriver man

create nonclustered columnstore index ix on dbo.FactA(Belopp, DimA_ID, DimB_ID);

Om vi nu provkör igen (exakt samma SQL-fråga som tidigare) så tar det cirka 0,2 sekunder och tabellen FactA tar cirka 2,8 Gigabyte i diskutrymme. Execution plan ser ut enligt nedan.

SQL Server MVP Roadshow 2014 plan 2

Provköra med clustered columnstore index

Till sist testar vi ett clustered columnstore index (sådant som endast finns i SQL Server 2014).

create clustered columnstore index ix on dbo.FactA;

Exakt samma SQL-fråga som tidigare tar nu cirka 0,2 sekunder och tabellen FactA tar cirka 0,4 Gigabyte i diskutrymme. Execution plan ser ut enligt nedan.

SQL Server Roadshow 2014 plan 3

Resultat tester In-Memory OLTP

Jag har sammanställt mina tester i ett diagram och fick då följande (klicka på figuren för att förstora den).

SQL Server In-Memory DW benchmark

Med SQL Server 2014 kan du alltså få både hög kompression, hög prestanda och göra uppdateringar i dina tabeller. Det går i detta exempel 100 gånger snabbare att läsa (men långsammare att skriva) till tabellen.