Dela via

Undersökning av första områden i In-Memory OLTP

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Den här artikeln är avsedd för utvecklare som har bråttom att lära sig grunderna i In-Memory OLTP-prestandafunktioner i Microsoft SQL Server och Azure SQL Database.

För In-Memory OLTP innehåller den här artikeln följande:

  • Snabbförklaringar av funktionerna.
  • Viktiga kodexempel som implementerar funktionerna.

SQL Server och SQL Database har bara mindre variationer i sitt stöd för In-Memory tekniker.

Bland bloggare hänvisar vissa till In-Memory OLTP som Hekaton.

Fördelar med In-Memory funktioner

SQL Server tillhandahåller In-Memory funktioner som avsevärt kan förbättra prestandan för många programsystem. De mest raka övervägandena beskrivs i det här avsnittet.

Funktioner för OLTP (onlinetransaktionsbearbetning)

System som måste bearbeta ett stort antal SQL INSERT samtidigt är utmärkta kandidater för OLTP-funktionerna.

  • Våra tester visar att hastighetsförbättringar mellan 5 och 20 gånger snabbare kan uppnås genom införandet av In-Memory-funktionerna.

System som bearbetar tunga beräkningar i Transact-SQL är utmärkta kandidater.

  • En lagrad procedur som är dedikerad till tunga beräkningar kan köras upp till 99 gånger snabbare.

Senare kan du besöka följande artiklar som erbjuder demonstrationer av prestandavinster från In-Memory OLTP:

Funktioner för operational analytics

In-Memory Analytics refererar till SQL SELECTs som aggregerar transaktionsdata, vanligtvis genom att inkludera en GROUP BY-sats. Indextypen columnstore är central för driftanalys.

Det finns två huvudscenarier:

  • Batch Operational Analytics- refererar till aggregeringsprocesser som körs antingen efter kontorstid eller på sekundär maskinvara som har kopior av transaktionsdata.
  • Operational Analytics i realtid refererar till aggregeringsprocesser som körs under kontorstid och på den primära maskinvaran som används för transaktionsarbetsbelastningar.

Den här artikeln fokuserar på OLTP och inte på analys. För information om hur kolumnlagringsindex introducerar analysfunktioner i SQL, se:

Kolumnarkiv

En sekvens med utmärkta blogginlägg förklarar elegant kolumnlagringsindex från flera perspektiv. Majoriteten av inläggen beskriver vidare begreppet driftanalys i realtid, som columnstore stöder. Dessa inlägg skapades av Sunil Agarwal, programchef på Microsoft, i mars 2016.

Driftanalys i realtid

  1. Real-Time Operativa Analyser med hjälp av In-Memory Technology
  2. Real-Time Operational Analytics – Översikt över NCCI(nonclustered columnstore index)
  3. Real-Time Operational Analytics: Enkelt exempel med icke-klustrat kolumnlagringsindex (NCCI) i SQL Server 2016
  4. Real-Time Operational Analytics: DML-åtgärder och NCCI (nonclustered columnstore index) i SQL Server 2016
  5. Real-Time Operational Analytics: Filtrerat kolumnlagringsindex (NCCI - nonclustered columnstore index)
  6. Real-Time Operational Analytics: Alternativet komprimeringsfördröjning för NCCI(Nonclustered columnstore index)
  7. Real-Time Operationell Analys: Alternativet Komprimeringsfördröjning med NCCI och prestandan
  8. Real-Time Operational Analytics: Memory-Optimized Tabeller och kolumnlagringsindex

Defragmentera ett columnstore-index

  1. Columnstore-indexdefragmentering med kommandot REORGANIZE
  2. kolumnlagringsindexsammanslagningsprincip för OMORGANISERA

Massimport av data

  1. Klustrad kolumnlagring: Massinläsning
  2. Grupperat columnstore-index: Datainläsningsoptimeringar – Minimal loggning
  3. Grupperat kolumnlagringsindex: Optimering av datainläsning – Parallell bulkimport

Funktioner i In-Memory OLTP

Nu ska vi titta på huvudfunktionerna i In-Memory OLTP.

Minnesoptimerade tabeller

Nyckelordet T-SQL MEMORY_OPTIMIZED i create table-instruktionen är hur en tabell skapas för att finnas i aktivt minne i stället för på disk.

En minnesoptimerad tabell har en representation av sig själv i aktivt minne och en sekundär kopia på disken.

  • Diskkopian är avsedd för rutinmässig återställning efter en avstängning och sedan omstart av servern eller databasen. Den här minnes-plus-disk-dualiteten är helt dold för dig och din kod.

Internt kompilerade moduler

Nyckelordet T-SQL NATIVE_COMPILATION i instruktionen CREATE PROCEDURE (SKAPA procedur) är hur en inbyggt kompilerad lagrad procedur skapas. T-SQL-uttryck kompileras till maskinkod första gången den inbyggda proceduren används varje gång databasen tillgängliggörs online. T-SQL-instruktionerna uthärdar inte längre långsam tolkning av varje instruktion.

  • Vi har sett inbyggda kompileringsresultat i varaktigheter som är 1/100 av den tolkade varaktigheten.

En inbyggd modul kan endast referera till minnesoptimerade tabeller och kan inte referera till diskbaserade tabeller.

Det finns tre typer av inbyggda kompilerade moduler:

Tillgänglighet i Azure SQL Database

In-Memory OLTP och Columnstore finns i Azure SQL Database. Mer information finns i Optimera prestanda med hjälp av In-Memory Technologies i SQL Database.

1. Kontrollera kompatibilitetsnivån >= 130

Det här avsnittet börjar en sekvens med numrerade avsnitt som tillsammans visar den Transact-SQL syntax som du kan använda för att implementera In-Memory OLTP-funktioner.

För det första är det viktigt att databasen är inställd på en kompatibilitetsnivå på minst 130. Nästa är T-SQL-koden för att visa den aktuella kompatibilitetsnivån som den aktuella databasen är inställd på.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Nästa är T-SQL-koden för att uppdatera nivån om det behövs.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Höj till ÖGONBLICKSBILD

När en transaktion omfattar både en diskbaserad tabell och en minnesoptimerad tabell kallar vi det för en transaktion mellan containrar. I en sådan transaktion är det viktigt att den minnesoptimerade delen av transaktionen fungerar på transaktionsisoleringsnivån med namnet SNAPSHOT.

Om du vill tillämpa den här nivån på ett tillförlitligt sätt för minnesoptimerade tabeller i en transaktion mellan containrar ändra databasinställningen genom att köra följande T-SQL.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Skapa en optimerad FILGRUPP

Innan du kan skapa en minnesoptimerad tabell på Microsoft SQL Server måste du först skapa en FILGRUPP som du deklarerar CONTAINS MEMORY_OPTIMIZED_DATA. FILEGROUP har tilldelats din databas. Mer information finns i:

I Azure SQL Database behöver du inte och kan inte skapa en sådan FILGRUPP.

Följande T-SQL-exempelskript aktiverar en databas för In-Memory OLTP och konfigurerar alla rekommenderade inställningar. Det fungerar med både SQL Server och Azure SQL Database: enable-in-memory-oltp.sql.

Observera att inte alla SQL Server-funktioner stöds för databaser med en MEMORY_OPTIMIZED_DATA filgrupp. Mer information om begränsningar finns i: SQL Server-funktioner som inte stöds för In-Memory OLTP-

4. Skapa en minnesoptimerad tabell

Det viktiga Transact-SQL nyckelordet är nyckelordet MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Transact-SQL INSERT- och SELECT-instruktioner mot en minnesoptimerad tabell är samma som för en vanlig tabell.

ALTER TABLE för Memory-Optimized tabeller

ALTER TABLE... ADD/DROP kan lägga till eller ta bort en kolumn från en minnesoptimerad tabell eller ett index.

  • CREATE INDEX och DROP INDEX kan inte köras mot en minnesoptimerad tabell, använd ALTER TABLE ... LÄGG TILL/SLÄPP INDEX i stället.
  • Mer information finns i Altering Memory-Optimized Tables.

Planera dina minnesoptimerade tabeller och index

5. Skapa en internt kompilerad lagrad procedur (intern process)

Det viktiga nyckelordet är NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

Nyckelordet SCHEMABINDING innebär att tabellerna som refereras i den inbyggda processen inte kan tas bort om inte den inbyggda processen tas bort först. Mer information finns i Skapa internt kompilerade lagrade procedurer.

Observera att du inte behöver skapa en internt kompilerad lagrad procedur för att få åtkomst till en minnesoptimerad tabell. Du kan också referera till minnesoptimerade tabeller från traditionella lagrade procedurer och ad hoc-batchar.

6. Kör den inbyggda processen

Fyll i tabellen med två rader med data.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Ett EXECUTE-anrop till den inbyggda kompilerade lagrade proceduren följer.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

Här är de faktiska utskriftsutdata:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Guide till dokumentationen och nästa steg

De föregående vanliga exemplen ger dig en grund för att lära dig de mer avancerade funktionerna i In-Memory OLTP. Följande avsnitt är en guide till de särskilda överväganden som du kan behöva känna till och var du kan se information om var och en.

Hur In-Memory OLTP-funktioner fungerar så mycket snabbare

Följande underavsnitt beskriver kortfattat hur de In-Memory OLTP-funktionerna fungerar internt för att ge bättre prestanda.

Så här fungerar minnesoptimerade tabeller snabbare

Dubbel natur: En minnesoptimerad tabell har dubbel karaktär: en representation i aktivt minne och den andra på hårddisken. Varje transaktion utförs i båda representationerna av tabellen. Transaktioner fungerar mot den mycket snabbare aktiva minnesrepresentationen. Minnesoptimerade tabeller drar nytta av den högre hastigheten för aktivt minne jämfört med disken. Dessutom gör den större smidigheten i aktivt minne praktiskt en mer avancerad tabellstruktur som är optimerad för hastighet. Den avancerade strukturen är också sidlös, så den undviker overhead och konkurrens av lås och spinlocks.

Inga lås: Den minnesoptimerade tabellen förlitar sig på ett optimistiskt tillvägagångssätt för de konkurrerande målen dataintegritet kontra samtidighet och hög genomströmning. Under transaktionen placerar tabellen inte lås på någon version av de uppdaterade dataraderna. Detta kan avsevärt minska konkurrensen i vissa system med höga volymer.

Radversioner: I stället för lås lägger den minnesoptimerade tabellen till en ny version av en uppdaterad rad i själva tabellen, inte i tempdb. Den ursprungliga raden behålls tills efter transaktionen har committerats. Under transaktionen kan andra processer läsa den ursprungliga versionen av raden.

  • När flera versioner av en rad skapas för en diskbaserad tabell lagras radversioner tillfälligt i tempdb.

Mindre loggning: Före- och efterversionerna av de uppdaterade raderna lagras i den minnesoptimerade tabellen. Radparet innehåller mycket av den information som traditionellt skrivs till loggfilen. Detta gör att systemet kan skriva mindre information, och mindre ofta, till loggen. Ändå säkerställs transaktionsintegriteten.

Hur inbyggda procs presterar snabbare

Om du konverterar en vanlig tolkad lagrad procedur till en inbyggt kompilerad lagrad procedur minskar antalet instruktioner som ska köras under körningen avsevärt.

Kompromisser med In-Memory funktioner

Som vanligt inom datavetenskap är de prestandavinster som tillhandahålls av In-Memory funktioner en kompromiss. De bättre funktionerna ger fördelar som är mer värdefulla än de extra kostnaderna för funktionen. Du hittar omfattande vägledning om kompromisserna på:

I resten av det här avsnittet visas några av de viktigaste övervägandena för planering och kompromisser.

Kompromisser med minnesoptimerade tabeller

Uppskatta minne: Du måste uppskatta mängden aktivt minne som den minnesoptimerade tabellen kommer att förbruka. Datorsystemet måste ha tillräcklig minneskapacitet för att vara värd för en minnesoptimerad tabell. Mer information finns i:

Partitionera din stora tabell: Ett sätt att möta efterfrågan på mycket aktivt minne är att partitionera din stora tabell i delar i minnet som lagrar senaste heta datarader jämfört med andra delar på disken som lagrar kalla äldre rader (till exempel försäljningsorder som har levererats och slutförts helt). Den här partitioneringen är en manuell process för design och implementering. Se:

Kompromisser med inbyggda processer

  • En internt kompilerad lagrad procedur kan inte komma åt en diskbaserad tabell. En intern process kan endast komma åt minnesoptimerade tabeller.
  • När en intern process körs för första gången efter att servern eller databasen senast har aktiverats igen måste den inbyggda processen kompileras om en gång. Detta orsakar en fördröjning innan den inbyggda processen börjar köras.

Avancerade överväganden för minnesoptimerade tabeller

Index för Memory-Optimized-tabeller skiljer sig på vissa sätt från index på traditionella disktabeller. Hash-index är endast tillgängliga i minnesoptimerade tabeller.

Du måste planera för att se till att det finns tillräckligt med aktivt minne för din planerade minnesoptimerade tabell och dess index. Se:

En minnesoptimerad tabell kan deklareras med DURABILITY = SCHEMA_ONLY:

  • Den här syntaxen instruerar systemet att ta bort alla data från den minnesoptimerade tabellen när databasen tas offline. Endast tabelldefinitionen sparas.
  • När databasen är online igen läses den minnesoptimerade tabellen in i aktivt minne, tom på data.
  • SCHEMA_ONLY-tabeller kan vara ett överlägset -alternativ till temporära tabeller i tempdb, när det handlar om många tusen rader.

Tabellvariabler kan också deklareras som minnesoptimerade. Se:

Avancerade överväganden för inbyggda kompilerade moduler

De typer av internt kompilerade moduler som är tillgängliga via Transact-SQL är:

En internt kompilerad användardefinierad funktion (UDF) körs snabbare än en tolkad UDF. Här följer några saker att tänka på med UDF:er:

  • När en T-SQL SELECT använder en UDF anropas UDF alltid en gång per returnerad rad.
    • UDF:er körs aldrig i linje, utan anropas alltid.
    • Den kompilerade skillnaden är mindre betydande än omkostnaderna för upprepade anrop som är inbyggda i alla UDF:er.
    • Ändå är omkostnaderna för UDF-samtal ofta acceptabla på den praktiska nivån.

För testdata och förklaring om prestanda för interna UDF:er, se:

Dokumentationsguide för minnesoptimerade tabeller

Se dessa andra artiklar som beskriver särskilda överväganden för minnesoptimerade tabeller:

Dokumentationsguide för inbyggda processer

I följande artikel och dess underordnade artiklar i innehållsförteckningen (TOC) förklaras detaljerna om nativt kompilerade lagrade procedurer.

Här är artiklar som erbjuder kod för att demonstrera de prestandavinster du kan uppnå med hjälp av In-Memory OLTP: