Übersicht über In-Memory OLTP und Verwendungsszenarien

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

In-Memory OLTP ist die führende Technologie in SQL Server und SQL-Datenbank zur Optimierung der Leistung von Transaktionsverarbeitung, Datenaufnahme, Datenlast und vorübergehenden Datenszenarien. Dieser Artikel bietet eine Übersicht über die Technologie und beschreibt Verwendungsszenarios für In-Memory-OLTP. Nutzen Sie diese Informationen, um festzustellen, ob In-Memory-OLTP für Ihre Anwendung geeignet ist. Der Artikel endet mit einem Beispiel, das In-Memory-OLTP-Objekte zeigt. Er enthält zudem einen Verweis auf eine leistungsbezogene Demo und Verweise auf Ressourcen, die Sie für die nächsten Schritte nutzen können.

In diesem Artikel werden die OLTP-Technologie im Arbeitsspeicher sowohl in SQL Server als auch in SQL-Datenbank behandelt. Weitere Informationen speziell für Speicherdaten in Azure SQL finden Sie unter Optimieren der Leistung mithilfe von In-Memory-Technologien in Azure SQL-Datenbank und Azure SQL verwaltete Instanz und Blog: In-Memory OLTP in Azure SQL-Datenbank.

Übersicht über das In-Memory-OLTP

In-Memory-OLTP kann bei den entsprechenden Workloads für große Leistungszuwächse sorgen. Während andere Kunden in einigen Fällen eine Leistungszunahme um das 30-fache verzeichnen konnten, hängt Ihr Zuwachs vornehmlich von der Workload ab.

Woher rührt nun diese Leistungszunahme? Im Wesentlichen verbessert IN-Memory OLTP die Leistung der Transaktionsverarbeitung, indem der Datenzugriff und die Transaktionsausführung effizienter gestaltet werden, und durch Entfernen von Sperr- und Sperreskonflikten zwischen gleichzeitig ausgeführten Transaktionen. In-Memory OLTP ist nicht schnell, da es im Arbeitsspeicher vorhanden ist. es ist schnell, da es um die Daten im Arbeitsspeicher optimiert ist. Datenspeicherung, Zugriff und Verarbeitungsalgorithmen wurden von Grund auf neu gestaltet, um in den Genuss der neuesten Weiterentwicklungen bei der Datenverarbeitung im Arbeitsspeicher und hoher Parallelität zu kommen.

Da Daten im Arbeitsspeicher gespeichert sind, bedeutet dies jetzt nicht, dass Sie sie verlieren, wenn ein Fehler auftritt. Alle Transaktionen sind standardmäßig vollständig dauerhaft, was heißt, dass Sie dieselben Zusagen hinsichtlich Dauerhaftigkeit wie für andere Tabellen in SQL Server erhalten. Als Teil des Transaktionscommits werden alle Änderungen in das Transaktionsprotokoll auf dem Datenträger geschrieben. Wenn es zu einem beliebigen Zeitpunkt nach dem Commit der Transaktion zu einem Fehler kommt, sind Ihre Daten vorhanden, nachdem die Datenbank wieder online geschaltet wurde. Darüber hinaus funktioniert IN-Memory OLTP mit allen Funktionen für hohe Verfügbarkeit und Notfallwiederherstellung von SQL Server, z . B. Verfügbarkeitsgruppen, Failoverclusterinstanzen, Sicherung/Wiederherstellung usw.

Wenn Sie IN-Memory OLTP in Ihrer Datenbank verwenden möchten, verwenden Sie einen oder mehrere der folgenden Objekttypen:

  • Speicheroptimierte Tabellen dienen zum Speichern von Benutzerdaten. Sie deklarieren eine Tabelle bei ihrer Erstellung als speicheroptimiert.
  • Nicht dauerhafte Tabellen dienen für kurzlebige Daten, entweder zum Zwischenspeichern oder für ein temporäres Resultset (als Ersatz für herkömmliche temporäre Tabellen). Eine nicht dauerhafte Tabelle ist eine speicheroptimierte Tabelle, die mit DURABLEY=SCHEMA_ONLY deklariert wird, was bedeutet, dass Änderungen an diesen Tabellen keine E/A-Vorgänge verursachen. Dadurch wird die Nutzung von E/A-Protokollressourcen in Fällen vermieden, in denen Dauerhaftigkeit nicht unbedingt erforderlich ist.
  • Speicheroptimierte Tabellentypen werden für Tabellenwertparameter (TVPs) sowie für temporäre Resultsets in gespeicherten Prozeduren verwendet. Diese können anstelle herkömmlicher Tabellentypen verwendet werden. Tabellenvariablen und TVPs, die mit einem speicheroptimierten Tabellentyp deklariert werden, übernehmen die Vorteile nicht dauerhafter speicheroptimierter Tabellen: effizienter Datenzugriff und keine E/A.
  • Nativ kompilierte T-SQL-Module dienen zum weiteren Verringern des Zeitaufwands einzelner Transaktionen durch Reduzieren der CPU-Zyklen, die für das Verarbeiten von Vorgängen erforderlich sind. Sie deklarieren bei seiner Erstellung, dass ein Transact-SQL-Modul nativ kompiliert werden soll. Derzeit können die folgenden T-SQL-Module nativ kompiliert werden: gespeicherte Prozeduren, Trigger und benutzerdefinierte Skalarfunktionen.

In-Memory OLTP ist in SQL Server und SQL-Datenbank integriert. Da sich diese Objekte auf ähnliche Weise wie ihre herkömmlichen Gegenstücke verhalten, können Sie häufig Leistungsvorteile erzielen und gleichzeitig nur minimale Änderungen an der Datenbank und der Anwendung vornehmen. Darüber hinaus können Sie über speicheroptimierte und herkömmliche datenträgerbasierte Tabellen in derselben Datenbank verfügen und Abfragen auf beide anwenden. Sie finden ein Transact-SQL-Skript mit einem Beispiel für jeden dieser Objekttypen am Ende dieses Artikels.

Verwendungsszenarien für OLTP im Arbeitsspeicher

In-Memory OLTP ist keine magische Go-Fast-Taste und eignet sich nicht für alle Workloads. Speicheroptimierte Tabellen verringern Ihre CPU-Auslastung nicht, wenn bei den meisten Abfragen eine Aggregation über große Datenbereiche erfolgt. Columnstore-Indizes helfen bei diesem Szenario.

Achtung

Bekanntes Problem: Bei Datenbanken mit speicheroptimierten Tabellen kann die Durchführung einer Transaktionsprotokollsicherung ohne Wiederherstellung und später das Ausführen einer Wiederherstellung eines Transaktionsprotokolls mit der Wiederherstellung zu einem nicht reagierenden Datenbankwiederherstellungsprozess führen. Dieses Problem kann sich auch auf die Protokollversandfunktionalität auswirken. Um dieses Problem zu umgehen, kann die SQL Server-Instanz vor dem Initiieren des Wiederherstellungsvorgangs neu gestartet werden.

Hier ist eine Liste von Szenarien und Anwendungsmustern, in denen wir gesehen haben, dass Kunden mit IN-Memory OLTP erfolgreich sind.

Transaktionsverarbeitung mit hohem Durchsatz und niedriger Latenz

Dies ist das wichtigste Szenario, für das wir In-Memory-OLTP entwickelt haben: die Unterstützung großer Mengen von Transaktionen mit konsistent niedriger Latenz für einzelne Transaktionen.

Gängige Workloadszenarien sind u.a. Wertpapierhandel, Sportwetten, Spiele auf Mobilgeräten und Übermittlung von Werbeanzeigen. Ein weiteres häufiges Muster ist ein „Katalog“, der häufig gelesen und/oder aktualisiert wird. Beispiel: Sie verfügen über große Dateien, die jeweils auf mehrere Clusterknoten verteilt sind, und katalogisieren den Speicherort jedes Shards jeder Datei in einer speicheroptimierten Tabelle.

Überlegungen zur Implementierung

Verwenden Sie speicheroptimierte Tabellen für Ihre wichtigsten Transaktionstabellen, d.h. die Tabelle mit den meisten leistungskritischen Transaktionen. Verwenden Sie nativ kompilierte gespeicherte Prozeduren zum Optimieren der Ausführung der Logik, die den Geschäftstransaktion zugeordnet ist. Je mehr Logik Sie an gespeicherte Prozeduren in der Datenbank weitergeben können, desto mehr können Sie von In-Memory-OLTP profitieren.

So fangen Sie bei einer vorhandenen Anwendung an

  1. Verwenden Sie den Bericht zur Transaktionsleistungsanalyse, um die Objekte zu bestimmen, die Sie migrieren möchten.
  2. Verwenden Sie den Speicheroptimierungsratgeber und den Nativen Kompilierungsratgeber, um bei der Migration zu helfen.

Datenerfassung, einschließlich IoT (Internet der Dinge)

In-Memory-OLTP ist für die gleichzeitige Erfassung großer Datenmengen aus vielen verschiedenen Quellen geeignet. Und es ist häufig vorteilhaft, Daten in einer SQL Server-Datenbank im Vergleich zu anderen Zielen aufzunehmen, da SQL Server Abfragen für die Daten schnell ausführt und Ihnen ermöglicht, Echtzeiteinblicke zu erhalten.

Folgende Anwendungsmuster werden häufig verwendet:

  • Erfassen von Sensormesswerten und -ereignissen sowie das Zulassen von Benachrichtigungen und Verlaufsanalysen.
  • Verwalten von Batchaktualisierungen, auch aus mehreren Quellen, bei gleichzeitiger Minimierung der Auswirkungen auf die gleichzeitige Leseworkload.

Überlegungen zur Implementierung

Verwenden Sie für die Datenerfassung eine speicheroptimierte Tabelle. Wenn die Erfassung hauptsächlich aus Einfügungen (anstelle von Aktualisierungen) besteht und der In-Memory-OLTP-Speicherbedarf von Belang ist, haben Sie diese Optionen:

  • Verwenden Sie einen Auftrag, um Daten regelmäßig in eine datenträgerbasierte Tabelle mit einem gruppierten Spaltenspeicherindex zu stapeln, indem Sie einen Auftrag verwenden, der ausgeführt wird INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; oder
  • Arbeiten Sie mit einer temporalen speicheroptimierten Tabelle zum Verwalten von Verlaufsdaten. In diesem Modus befinden sich die Verlaufsdaten auf dem Datenträger, und die Datenverschiebung wird vom System verwaltet.

Das SQL Server-Repository mit Beispielen enthält eine Smart Grid-Anwendung, die eine temporale speicheroptimierte Tabelle, einen speicheroptimierten Tabellentyp und eine nativ kompilierte gespeicherte Prozedur verwendet, um die Datenerfassung zu beschleunigen, während der In-Memory-OLTP-Speicherbedarf der Sensordaten verwaltet wird:

Zwischenspeicherung und Sitzungszustand

Die In-Memory OLTP-Technologie macht das Datenbankmodul in SQL Server- oder Azure SQL-Datenbanken zu einer attraktiven Plattform für Standard taining Session State (z. B. für eine ASP.NET Anwendung) und zum Zwischenspeichern.

ASP.NET Sitzungszustand ist ein erfolgreicher Anwendungsfall für In-Memory OLTP. Mithilfe von SQL Server konnte ein Kunde 1,2 Mio. Anforderungen pro Sekunde erreichen. Mittlerweile hat das Unternehmen begonnen, In-Memory-OLTP für die Cacheanforderungen aller Mid-Tier-Anwendungen zu nutzen. Details: Wie bwin SQL Server 2016 (13.x) In-Memory OLTP verwendet, um eine beispiellose Leistung und Skalierung zu erzielen

Überlegungen zur Implementierung

Sie können nicht dauerhafte speicheroptimierte Tabellen als einfachen Schlüssel-Wert-Speicher nutzen, indem Sie ein Blob in Spalten des Typs „varbinary(max)“ speichern. Alternativ können Sie einen halbstrukturierten Cache mit JSON-Unterstützung in SQL Server und SQL-Datenbank implementieren. Schließlich können Sie einen vollständig relationalen Cache mithilfe nicht dauerhafter Tabellen mit einem vollständig relationalen Schema einschließlich verschiedener Datentypen und Einschränkungen erstellen.

Erste Schritte mit der Speicheroptimierung ASP.NET Sitzungsstatus mithilfe der skripts, die auf GitHub veröffentlicht wurden, um die vom integrierten SQL Server-Sitzungsstatusanbieter erstellten Objekte zu ersetzen: aspnet-session-state

Kundenfallstudie

tempdb-Objektersetzung

Verwenden Sie nicht dauerhafte Tabellen und speicheroptimierte Tabellentypen, um Ihre herkömmlichen tempdb basierten Strukturen wie temporäre Tabellen, Tabellenvariablen und Tabellenwertparameter (TVPs) zu ersetzen.

Speicheroptimierte Tabellenvariablen und nicht dauerhafte Tabellen sorgen im Vergleich mit herkömmlichen Tabellenvariablen und der Tabelle „#temp“ meist für eine Senkung der CPU-Last und die vollständige Beseitigung von Ein- und Ausgaben für Protokolle.

Überlegungen zur Implementierung

Lesen Sie zum Einstieg: Verbesserern der temporären Tabelle und Tabellenvariablenleistung mithilfe der Speicheroptimierung

Kundenfallstudie

ETL (Extrahieren, Transformieren, Laden)

ETL-Workflows sehen häufig das Laden von Daten in eine Stagingtabelle, Transformationen der Daten und das Laden in die endgültigen Tabellen vor.

Verwenden Sie für das Staging von Daten nicht dauerhafte speicheroptimierte Tabellen. Bei diesen Tabellen fallen keine E/A-Vorgänge an, wodurch der Datenzugriff effizienter wird.

Überlegungen zur Implementierung

Wenn Sie im Rahmen des Workflows Transformationen auf die Stagingtabelle anwenden, können Sie zum Beschleunigen dieser Transformationen nativ kompilierte gespeicherte Prozeduren verwenden. Wenn diese Transformationen parallel erfolgen können, verschaffen Sie sich durch die Speicheroptimierung weitere Skalierungsvorteile.

Beispielskript

Bevor Sie In-Memory-OLTP verwenden können, müssen Sie die Dateigruppe MEMORY_OPTIMIZED_DATA erstellen. Außerdem empfehlen wir den Datenbank-Kompatibilitätsgrad 130 (oder höher) und das Festlegen der Datenbankoption MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT auf ON.

Sie können mithilfe des Skripts am folgenden Speicherort die Dateigruppe im standardmäßigen Datenordner erstellen und die empfohlenen Einstellungen konfigurieren:

Das folgende Beispielskript veranschaulicht In-Memory OLTP-Objekte, die Sie in Ihrer Datenbank erstellen können.

Beginnen Sie zunächst mit der Konfiguration der Datenbank für IN-Memory OLTP.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Sie können Tabellen mit unterschiedlicher Dauerhaftigkeit erstellen:

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

Sie können einen Tabellentyp als In-Memory-Tabelle erstellen.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

Sie können eine nativ kompilierte gespeicherte Prozedur erstellen. Weitere Informationen finden Sie unter Aufrufen von nativ kompilierten gespeicherten Prozeduren über Datenzugriffsanwendungen.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO