SQL Server 2014 In-Memory OLTP eller ”what the heck is a hekaton?”

By | november 13, 2014

För en introduktion In-Memory OLTP har jag tidigare skrivit en miniserie om SQL Server 2014 nyheter. Den kan läsas här.

När SQL Server version 1.0 lanserades (omkring 1989) så var RAM-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.

Detta ledde till att Microsoft startade ett projekt med kodnamnet ”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.

Hekaton Athlete

Microsofts höll projektet mycket hemligt, men vissa spår syntes redan i SQL Server 2012, vilket ledde till att en uppmärksam branschkollega bloggade om detta Hekaton-mysterium. Det hela ledde sedan till att vi i SQL Server användarföreningen tryckte en T-shirt med texten ”What the heck is a Hekaton”. Inför lanseringen av SQL Server 2014 döpte marknadsfolket på Microsoft slutligen om Hekaton till In-Memory OLTP. Ett krångligare och tråkigare namn enligt min mening.

Bilden nedan visar den övergripande arkitekturen i SQL Server 2014 med In-Memory OLTP / Hekaton till vänster. En av de allra viktigaste sakerna är att man i SQL Server 2014 kan blanda vanliga tabeller och procedurer med In-Memory tabeller och procedurer. Det kan man inte i någon annan In-Memory plattform vad jag känner till. En lista över supporterad och icke-supporterad SQL Server funktionalitet i In-Memory OLTP finns här.

Hekaton arkitektur

Myter om In-Memory OLTP (Hekaton)

Jag har sprungit på ett antal myter om In-Memory OLTP (Hekaton) som jag tänker vederlägga i denna bloggpost.

  • ”Hekaton är en In-Memory teknologi, vilket innebär att all data försvinner om man drar ut sladden”
  • ”Hekaton är en NoSQL databas”
  • ”In-Memory OLTP är bara en förbättrad DBCC PINTABLE”
  • ”Du behöver inte göra några anpassningar för att flytta dina applikationer till Hekaton”
  • ”Alla applikationer går 100 gånger snabbare”

Lagring på disk

Även med In-Memory OLTP så är SQL Server fortfarande ACID (atomic, consistent, isolated, durable), 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 bara att den jobbar på ett helt annorlunda sätt än tidigare.

Tre typer av filer används: datafiler, deltafiler och transaktionslog. Datafilerna innehåller ursprunglig data (INSERTs) som skrivs till tabeller medan deltafilerna håller reda på alla UPDATEs och DELETEs. I alla tre fallen läses och skrivs data sekventiellt (till skillnad från vanliga SQL Server tabeller där man för OLTP-system brukar tala om ”random reads” och ”random writes”). Detta är en av förklaringarna till att In-Memory OLTP är så pass mycket snabbare. Man kan också snabba upp ytterligare genom att välja SCHEMA_ONLY, som betyder att man kommer tappa all sin data vid en omstart av servern.

För att kunna använda In-Memory OLTP måste man skapa en särskild Memory Optimized Filegroup. Se bilden nedan (klicka för en förstoring).

Hekaton_filegroup

Man behöver sedan också lägga till en ”fil”. I själva verket är det en ”container” man skapar som kommer att ha många datafiler och deltafiler. Notera att man skall välja FILESTREAM DATA som File Type.

Hekaton_files

Datafiler och deltafiler är alltid ihopkopplade parvis i så kallade CFPs (Checkpoint File Pairs). För att frigöra utrymme så merge:ar (”defragmenterar”) SQL Server dessa automatiskt vid behov. Man kan också manuellt starta en merge vid behov.

Ett sätt att öka prestandan för In-Memory OLTP är att skapa flera ”containers” utspridda på olika diskar. Då kommer all data att skrivas till dessa enligt ”round-robin”, d v s i tur och ordning, med datafiler varannan gång och deltafiler varannan gång. För att få en jämn belastning bör man ha minst 4 containers (”filer”) och se till att få en jämn spridning av datafilerna.

In-Memory OLTP använder den vanliga transaktionsloggen. Inga inställningar man behöver göra där alltså förutom att transaktionsloggen kommer att ta större diskutrymme.

Nedan är ett exempel på hur man kan konfigurera för In-Memory OLTP med hjälp av T-SQL:

CREATE DATABASE Johan
GO

ALTER DATABASE Johan
ADD FILEGROUP Johan_Mem CONTAINS memory_optimized_data
GO

ALTER DATABASE Johan
ADD FILE (name='Johan_Mem_1', filename='c:\databases\Johan') 
TO FILEGROUP Johan_Mem

Microsoft har beskrivit lagringen av In-Memory tabeller ännu mer i detalj här.

NoSQL

Eftersom In-Memory OLTP är en relationsdatabas som uppfyller ACID-kravet och dessutom använder SQL som språk så är det en riktig SQL-databas. Ingen tvekan alls om den saken.

”In-Memory OLTP är bara en förbättrad DBCC PINTABLE”

DBCC PINTABLE var en funktion i gamla versioner av SQL Server som tvingade en vanlig tabell att stanna kvar i RAM-minnet. Det var bara en inställning man kunde göra på tabeller, inte en helt ny databasmotor som i In-Memory OLTP. Påståendet är alltså helt fel.

Anpassningar till In-Memory OLTP

Först vill jag säga att In-Memory OLTP är rik på funktionalitet. En komplett lista finns här. Nedan är några exempel.

  • In-Memory tabeller med de flesta datatyper i SQL Server, förutom XML och LOB-typer som exempelvis varchar(max).
  • Möjlighet att komma åt In-Memory tabeller både via vanliga stored procedures och In-Memory stored procedures (även kallade ”native” stored procedures).
  • Transaktioner, inklusive olika nivåer på transaktionsisolering såsom REPEATABLE READ och SERIALIZABLE.
  • Importera och exportera data med hjälp av bcp.
  • Recovery i händelse av krasch.
  • Service Broker.
  • AlwaysOn High Availability.
  • Log shipping.
  • Replikering.
  • Resource Governor.
  • DACPAC och BACPAC.

För att migrera befintliga applikationer eller utveckla för In-Memory OLTP finns en hel del att tänka på.

  • Man behöver inte migrera alla tabeller och procedurer. Man kan blanda vanliga tabeller med In-Memory tabeller.
  • Endast BIN2-collations stödjs i nuläget. Exempel på en sådan är Finnish_Swedish_100_BIN2. Man behöver inte ändra collation på databasen utan det räcker att ändra i tabellerna. Alla BIN2-collations är case sensitive, så SQL-kod som är skriven för en collation som är inte är case sensitive kan få stora problem.
  • Foreign Keys stödjs inte i nuläget. Inte heller UNIQUE-constraints eller CHECK-constraints. Kontroller får ske med programkod istället.
  • Tabellrader får vara maximalt 8060 bytes långa.
  • Computed Columns stödjs inte. Man kan dock skapa exempelvis en vy istället.
  • Inga av max-datatyperna, såsom varchar(max), stödjs. Inte heller XML-datatypen tillåts. Man får antingen dela upp dem på flera rader eller spara dem i en separat vanlig tabell.
  • Inga triggers tillåts på In-Memory tabeller.
  • Man kan inte göra någon ALTER TABLE. Istället måste man skapa en ny tabell, kopiera över data och radera den gamla.
  • Man kan inte heller göra ALTER PROCEDURE. Istället är det DROP PROCEDURE och CREATE PROCEDURE som gäller.

En komplett lista över funktionalitet som inte supportas finns här. Microsoft har också en mer omfattande guide hur man anpassar till In-Memory OLTP.

Index

In-Memory tabeller måste ha minst 1 index och kan ha högst 8 index. Det finns två typer: hashindex och icke-klustrade index.

Hashindex

Hashindex är användbara för ”point-lookups”, vilket är när en rad i taget skall letas upp. Indexet har ingen sorteringsordning och kan därför inte användas för att t ex söka ett intervall. Hashindex kräver också en ”bucket count” när man skapar dem som skall vara ungefär lika stor som antalet rader eller större. Om bucket count är för låg så blir prestandan sämre. Det går i nuläget inte att ändra bucket count utan att skapa om tabellen.

Nedan är en figur hur de fungerar (lånat av Kalen Delaneys whitepaper). Observera att det är tillåtet att ha flera hashindex per tabell.

hekaton-hashindex

Icke-klustrade index

Icke-klustrade index In-Memory fungerar ungefär på samma sätt som icke-klustrade index gör för vanliga tabeller. En skillnad är att de alltid är täckande (”covering”), d v s de inkluderar automatiskt alla kolumner. De är ett bra alternativ till hashindex när man inte på förhand vet hur många rader det kommer att bli eller man behöver göra exempelvis intervallsökningar.

hekaton-nonclusteredindex

In-Memory index skrivs aldrig till disk oavsett durability-inställningar. Istället skapas de om vid behov.

Microsoft har publicerat guidelines för när man skall välja hashindex respektive icke-klustrade index här.

Anropa In-Memory stored procedures ifrån SSIS

Om du använder SSIS så måste du anropa In-Memory stored procedures på rätt sätt. Microsoft rekommenderar att man använder en ODBC källa med en SQL-sats på formatet <procedurnamn> utan nyckelordet EXEC.

Anropa In-Memory stored procedures ifrån .NET

Om du använder dig av SqlClient så fungerar det inte att anropa In-Memory stored procedures. Lösningen är att använda ODBC, d v s namespace System.Data.Odbc.

AlwaysOn

AlwaysOn är den nya lösningen för High Availability och Disaster Recovery som introducerades i SQL Server 2012. Egentligen är det två olika teknologier:

  • AlwaysOn Failover Clustering Instances
    Kräver delad disk. Om en databasserver går ned så tar en annan över (men jobbar mot samma disk). Klustret har alltid bara en aktiv server.
  • AlwaysOn Availability Groups
    Synkroniserar data mellan olika databasservrar. En server är alltid primärnod och kan uppdatera databasen. Sekundärnoder kan bara användas för läsåtkomst. Om primärnoden går ned så kan en sekundärnod ta över.

Fungerar då AlwaysOn med In-Memory OLTP? Goda nyheter – ja, men med vissa kommentarer.

AlwaysOn Failover Clustering Instances

Failovertiden, alltså tiden tills en annan server är igång, blir längre om man använder In-Memory OLTP tabeller. Tabeller som är markerade som SCHEMA_ONLY tappar all data (samma som om man skulle starta om servern).

AlwaysOn Availability Groups

Både synkron och asynkron uppdatering av sekundärer stödjs. Failovertiden påverkas inte nämnvärt av att man använder In-Memory OLTP. Tabeller som är markerade som SCHEMA_ONLY tappar all data (samma som om man skulle starta om servern).

Mer information om AlwaysOn för In-Memory OLTP databaser finns här.

Prestanda

Jag har gjort en serie tester och sammanställt i ett diagram (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.

Observera att detta test inte på något sätt är representativt, men det visar på några saker.

  • Om man bara ändrar till In-Memory tabell utan att göra någon omskrivning alls av sina stored procedures så kan det till och med gå långsammare än innan! Anledningen är att det är en extra overhead (”interop”) när vanlig T-SQL kod skall använda sig av In-Memory objekt.
  • Om man är beredd att köra med SCHEMA_ONLY och vet ungefär hur många tabellrader det blir så att man kan dimensionera ett hashindex korrekt, samt använder native (In-Memory) stored procedures, så kan man få mycket kraftiga prestandaförbättringar. Ett bra användningsområde skulle exempelvis kunna vara ETL-processer i ett datalager.

En sak att tillägga också är att In-Memory OLTP är en ”game changer” när det gäller det gamla regeln att man måste jobba set-baserat (mängder av rader i taget) för att få en bra prestanda. I och med In-Memory OLTP så kan man bygga stored procedures som jobbar rad för rad med prestanda som är minst lika hög som traditionella set-baserade lösningar. Detta är en mycket stor skillnad som inte skall underskattas.

Referenser

För vidare läsning om SQL Server 2014 In-Memory OLTP rekommenderar jag de här länkarna: