SQL Server 2014 Nyheter del 2 – In-Memory OLTP

By | augusti 24, 2014

Detta är del 2 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 en jämförelse (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 OLTP (Hekaton)

När SQL Server version 1.0 lanserades (omkring 1989) så var minne jättedyrt. Diskutrymme var man naturligtvis tvungen att skaffa, men man räknade inte med att tabeller eller hela databaser skulle rymmas i minnet. Databaserna delades in i block på 8 kilobyte som kallas Pages. Denna indelning i Pages styr mycket av hur SQL Server fungerar och har varit en flaskhals för hur man kan få upp hastigheterna. Exempelvis om ni testar att lägga en SQL Server databas i en RAMdisk så kommer ni fortfarande inte bort från den overhead som indelningen i Pages medför. För att få upp hastigheterna ytterligare behöver man organisera data på ett helt annat sätt.

Microsoft startade ett projekt med namnet ”Hekaton” (grekiska för hundra gånger) som hade målet att det skulle gå 100 gånger fortare. Resultatet blev, skulle jag kalla det, en helt ny databasmotor. Men till skillnad mot andra databasleverantörer så har Microsoft sett till att den hänger ihop med ”vanliga” SQL Server. Detta ger enorma fördelar om man vill slippa skriva om sina lösningar totalt.

SQL Server In-Memory flexibilitet

Man använder samma verktyg oavsett om det är en ”vanlig” SQL Server databas, In-Memory databas eller molndatabas.

SQL Server In-Memory inbyggt

Även med In-Memory OLTP så är SQL Server fortfarande ACID, d v s man riskerar inte att förlora data. Ja du läste rätt – även om teknologin kallas ”In-Memory” så skrivs data fortfarande till disk så att man inte skall förlora den vid exempelvis strömavbrott. Skillnaden är att den jobbar på ett mycket annorlunda sätt än tidigare. Bland annat använder den sig av låsningsfria strukturer (d v s ”optimistisk låsning” istället för Locks och Latches).

SQL Server In-Memory OLTP prestanda

Så hur gör man då för att använda In-Memory OLTP?

Skapa en databas med In-Memory OLTP data

CREATE DATABASE MVPRoadshow2014
GO

ALTER DATABASE MVPRoadshow2014 
ADD FILEGROUP MVPRoadshow2014_Mem CONTAINS memory_optimized_data
GO

ALTER DATABASE MVPRoadshow2014 
ADD FILE (name='MVPRoadshow2014_Mem', filename='c:\databases\MVPRoadshow2014') 
TO FILEGROUP MVPRoadshow2014_Mem

Det nya jämfört med tidigare är att man anger på en FILEGROUP att den är minnesoptimerad.

Skapa en vanlig tabell och vanlig procedur

CREATE TABLE perftest
   (id uniqueidentifier not null primary key nonclustered,
    ms int,
	txt nvarchar(50))
GO
CREATE PROCEDURE dbo.perftest_proc
WITH EXECUTE AS OWNER
AS
BEGIN
  DECLARE @i int = 10000
  WHILE @i > 0
  BEGIN
    INSERT INTO dbo.perftest(id,ms,txt) 
    VALUES (NEWID(), DATEPART(MILLISECOND,SYSDATETIME()), CONVERT(nvarchar(50),SYSDATETIME(), 121) )
	SELECT @i = @i -1
  END
END

Att köra denna procedur 10 gånger tar på min dator (hyfsat snabb med SSD-disk) cirka 11,3 sekunder.

Gör tabellen minnesoptimerad, men ändra inget i proceduren

CREATE TABLE perftest
   (id uniqueidentifier not null primary key nonclustered,
    ms int,
	txt nvarchar(50))
with (memory_optimized=on)
GO
CREATE PROCEDURE dbo.perftest_proc
WITH EXECUTE AS OWNER
AS
BEGIN
  DECLARE @i int = 10000
  WHILE @i > 0
  BEGIN
    INSERT INTO dbo.perftest(id,ms,txt) 
    VALUES (NEWID(), DATEPART(MILLISECOND,SYSDATETIME()), CONVERT(nvarchar(50),SYSDATETIME(), 121) )
	SELECT @i = @i -1
  END
END

Genom att lägga till ett ”with (memory_optimized=on)” så blir det en In-Memory tabell. Observera att inget annat är ändrat.

Visst borde det gå fortare nu jämfört med förra testet? Men nej, på min dator tog det 12,2 sekunder att köra proceduren 10 gånger. Varför det? Vi är inte färdig ännu!

Minnesoptimerad tabell och minnesoptimerad procedur

CREATE TABLE perftest
   (id uniqueidentifier not null primary key nonclustered,
    ms int,
	txt nvarchar(50))
with (memory_optimized=on)
GO
CREATE PROCEDURE dbo.perftest_proc
WITH  NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')
  DECLARE @i int = 10000
  WHILE @i > 0
  BEGIN
    INSERT INTO dbo.perftest(id,ms,txt) 
    VALUES (NEWID(), DATEPART(MILLISECOND,SYSDATETIME()), CONVERT(nvarchar(50),SYSDATETIME(), 121) )
	SELECT @i = @i -1
  END
END

För att verkligen få nytta av In-Memory OLTP behöver vi även se till att våra procedurer är In-Memory. Detta gör vi genom att deklarera procedurerna som NATIVE_COMPILATION och med ett ATOMIC-block.

När SQL Server får en sådan här procedur så genereras den om till C-kod (kolla under en mapp som heter ”xtp” på samma plats som datafilerna för databasen så får du se). Denna kompileras till en DLL och laddas in av SQL Server. När proceduren anropas så är det den färdigkompilerade koden i DLL:en som anropas. Låter det krångligt? Det är inget du som användare märker av. Däremot har procedurer som kompileras till DLL:er vissa begränsningar, som att de exempelvis enbart kan jobba mot In-Memory tabeller.

Hur lång tid tog det att köra proceduren 10 gånger nu då? 0,57 sekunder!

In-Memory OLTP med Hash-index

CREATE TABLE perftest
   (id uniqueidentifier not null primary key nonclustered HASH (id) WITH (BUCKET_COUNT = 1000000),
    ms int,
	txt nvarchar(50))
with (memory_optimized=on)
go
CREATE PROCEDURE dbo.perftest_proc
WITH  NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')
  DECLARE @i int = 10000
  WHILE @i > 0
  BEGIN
    INSERT INTO dbo.perftest(id,ms,txt) 
    VALUES (NEWID(), DATEPART(MILLISECOND,SYSDATETIME()), CONVERT(nvarchar(50),SYSDATETIME(), 121) )
	SELECT @i = @i -1
  END
END

In-Memory OLTP stödjer en ny typ av index som heter Hash-index. Det är ett index som istället för en trädstruktur använder sig av en hashfunktion och ett antal buckets. Det är supersnabbt för uppslagning av enstaka poster, men fungerar inte för intervall eller operationer såsom ”större än” eller ”mindre än”. Tänk på att man måste ha tillräckligt hög BUCKET_COUNT för att det inte skall bli kollisioner i indexet. Annars går det riktigt långsamt istället.

Att köra proceduren 10 gånger med hashindex tar 0,30 sekunder på min dator, d v s nästan dubbelt så snabbt som med det traditionella indexet. Men det finns mer att göra!

Hash-index och SCHEMA_ONLY durability

CREATE TABLE perftest
   (id uniqueidentifier not null primary key nonclustered HASH (id) WITH ( BUCKET_COUNT = 1000000),
    ms int,
	txt nvarchar(50))
with (memory_optimized=on, durability=SCHEMA_ONLY)
go
CREATE PROCEDURE dbo.perftest_proc
WITH  NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')
  DECLARE @i int = 10000
  WHILE @i > 0
  BEGIN
    INSERT INTO dbo.perftest(id,ms,txt) 
    VALUES (NEWID(), DATEPART(MILLISECOND,SYSDATETIME()), CONVERT(nvarchar(50),SYSDATETIME(), 121) )
	SELECT @i = @i -1
  END
END

Det går att stänga av skrivningen till disk, så att tabellen enbart finns i RAM-minnet. Detta gör man genom att ange durability=SCHEMA_ONLY. Om datorn då skulle få strömavbrott så går innehållet i tabellen förlorat. Genom att stänga av durability så tar det nu 0,23 sekunder att köra proceduren 10 gånger på min dator.

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

In-Memory OLTP benchmark

Skillnaden mellan det snabbaste alternativet och ”vanlig” tabell är ungefär en faktor 50. Då skrivs ingen data till disk, men det är ändå ett mycket användbart scenario för exempelvis stagingtabeller i ett Data Warehouse.

Nästa del i min miniserie kommer handla om In-Memory DW.

Referenser

Tack till Mikael Wedham för demoscript.

En mer utförlig artikel, skriven av Kalen Delaney, om In-Memory OLTP finns här.