Über unsMediaKontaktImpressum
Klemens Konopasek 28. Januar 2015

SQL Server 2014 In-Memory OLTP: Ab in den Speicher!

Im landwirtschaftlichen Sinn betrachtet würde diese Aufforderung wohl bedeuten, man möge die Ernte für den Winter schleunigst einlagern. Im Datenbankbereich zeigt es eine neue Entwicklung auf, die gerade voll im Gange ist. Lesen von und Schreiben auf Datenträger ist einer der wesentlichen Performance-Flaschenhälse bei relationalen Datenbanksystemen. Da liegt es auf der Hand, dass die führenden Hersteller von Datenbankmanagementsystemen versuchen, neue Wege zu gehen: Die direkte Verwendung des Arbeitsspeichers ist naheliegend.

Und auch dann können wir ernten: Performance

Die Neuerung des SQL Server 2014 schlechthin sind Memory Optimized Tables. Sie sind vorweg unter dem Codenamen Hekaton bekannt geworden, die offizielle Bezeichnung ist nun SQL Server In-Memory OLTP. Kurz: Es geht dabei darum, ganze Tabellen für klassische Datenbankanwendungen zur Gänze im Arbeitsspeicher zu halten, um damit eine enorme Performancesteigerung zu erzielen. Mit klassischen Datenbanken meine ich explizit keine Data Warehouse-Datenbanken, die OLAP-Anwendungen (Online Analytical Processing) zuzuordnen sind. Mit OLTP-Anwendungen (Online Transaction Processing) sind Datenbanken gemeint, die zur Abwicklung von Transaktionen und nicht in erster Linie für Auswertungen eingesetzt werden – das normale Tagesgeschäft also.

IO ist eine der großen Performancebremsen in Datenbanksystemen. Durch die Nutzung des Arbeitsspeichers möchte man Verbesserungen bei der Performance von Anwendungen erreichen. Dies ist vor allem für Unternehmen von großer Bedeutung, die in kurzer Zeit eine enorme Anzahl an Transaktionen zu verarbeiten haben.

Viele in der Praxis eingesetzte Datenbanken weisen eine Größe von weniger als 20 GB auf. Und diese Größe lässt sich heutzutage gut im RAM abbilden. Das Plus der Lösung von Microsoft ist, dass sie zur Gänze in die vorhandene Datenbank-Engine integriert ist und es sich um kein parallel betriebenes Zusatzprodukt handelt. Daher ist nicht nur der „Look and Feel“ dieser Tabellen mit normalen Tabellen vergleichbar, auch die parallele Verwendung und die Kombination dieser Daten ist - mit kleinen Einschränkungen - möglich.

Voraussetzungen für In-Memory OLTP mit SQL Server

Der erste Schritt für den Einsatz von In-Memory OLTP ist das Erstellen einer Datenbank mit einer eigenen dafür ausgelegten Dateigruppe. Dateigruppen werden beim SQL Server dazu verwendet, physische Dateien auf einem Datenträger zu einer logischen, von der Datenbank als Speicherplatz verwendbaren Einheit zusammenzufassen. Diese Logik mit der Verwendung einer eigenen Filegroup wird beibehalten, auch wenn sich hinter dieser nun keine Dateien direkt auf einem Datenträger verbergen.

Eine speicheroptimierte Datenbank benötigt neben einer speziellen Dateigruppe auch die klassischen Elemente einer Datenbank, insbesondere das Transaktionsprotokoll. Ohne dieses gingen Daten bei einem Neustart des Systems verloren.

Mit der nachfolgenden Anweisung wird eine Datenbank mit dem Namen infoakt_ram angelegt. Sie besteht aus der für eine SQL Server-Datenbank üblichen Dateigruppe PRIMARY und der zusätzlichen Dateigruppe für den Speicher. Diese wird mit der Option CONTAINS MEMORY_OPTIMIZED_DATA definiert. Für interne Zwecke müssen Dateien auf der Harddisk gespeichert werden. Dazu muss der Name des dafür vorgesehenen Ordners mit der Option FILENAME angegeben werden. Die Dateigruppe verwendet dafür die Technologie von Filestream. Der Name, der für die Dateigruppe in der Anweisung angeben wird, wird daher zum Basisordner für die Filestream-Daten und gleicht der dafür verwendeten Struktur.

CREATE DATABASE infoakt_ram
CONTAINMENT = NONE
ON PRIMARY
( NAME='infoakt_hdd',
FILENAME='D:\DATA\ia_hdd.mdf',
SIZE=100MB,
FILEGROWTH=10MB),
FILEGROUP ULFTRASCHNELL CONTAINS MEMORY_OPTIMIZED_DATA
( NAME='infoakt_ram',
FILENAME='D:\DATA\ia_ram')
LOG ON
( NAME='infoakt_log',
FILENAME='E:\LOG\ia_hdd_log.ldf',
SIZE=30MB,
FILEGROWTH=10%)
COLLATE Latin1_General_BIN2;
GO

Ist die Datenbank einmal erstellt, kann die speicheroptimierte Dateigruppe nicht mehr entfernt werden. Zu diesem Zweck kann man nur die gesamte Datenbank löschen und neu erstellen.

Ob eine speicheroptimierte Tabelle ACID-Compliant sein soll oder nicht, wird beim Erstellen der jeweiligen Tabelle festgelegt. 

SQL SERVER 2014: In-Memory-Tables erstellen

Beim Erstellen von speicheroptimierten Tabellen muss man auf einige Dinge verzichten, die man vielleicht von herkömmlichen Tabellen zu verwenden gewohnt ist. Das liegt darin begründet, dass diese Tabellen auf Performance getrimmt sind und daher alles, was diese hemmen würde, weggelassen wurde.

Andererseits muss man auch eingestehen, dass dies die erste Version dieses neuen bahnbrechenden Features ist und manches in der Zukunft noch zu erwarten ist. Man muss sich außerdem vor Augen halten, dass dieses Feature enorme Anpassungen auch an den Fundamenten der vorhandenen Datenbankengine notwendig gemacht hat. Wir sprechen ja hier nicht von einem Zusatzfeature, sondern von einer Funktionalität, die in den bestehenden Programmkern integriert werden musste.

Einschränkungen von In-Memory-Tables

Die wichtigsten Einschränkungen auf den ersten Blick sind:

  • Speicheroptimierte Tabellen können nicht mit der Anweisung ALTER TABLE geändert werden. Alles muss daher schon bei der Anweisung CREATE TABLE berücksichtigt werden. Für eine spätere Änderung muss eine Tabelle neu erstellt werden, die Daten sind anschließend zu migrieren.
  • CLR-Datentypen (hierarchy_id, geometry, geography) und benutzerdefinierte Datentypen (UDTs) können nicht verwendet werden.
  • Es gibt keine FOREIGN KEY-, UNIQUE- und CHECK-Constraints.
  • Trigger stehen nicht zur Verfügung.
  • Kein Einsatz von berechneten Spalten.
  • Ein Index kann für Textspalten nur erstellt werden, wenn sie eine BIN2 Sortierreihenfolge verwenden. Bei der in unseren Breiten typischerweise verwendeten Einstellung Latin1_General_CI_AS ist es nicht möglich, einen Index zu erstellen. Es bietet sich bei Bedarf zum Beispiel ein Ausweichen auf Latin1_General_BIN2 an.

Speicheroptimierte Tabellen werden auch im Management Studio nicht über den grafischen Dialog erstellt. Im Kontextmenü des Objekt-Explorers verwendet man den Befehl NEU\NEUE SPEICHEROPTIMIERTE TABELLE..., um damit ein neues Abfrage-Editor-Fenster mit einer Vorlage, wie in Abb. 1 zu sehen, zu öffnen. Aus der Vorlage lassen sich sehr gut die entscheidenden Anweisungsteile entnehmen.

ACID für In-Memory-Tables

Eine speicheroptimierte Tabelle wird durch den Zusatz WITH (MEMORY_OPTIMIZED = ON) am Ende der CREATE TABLE-Anweisung erzeugt. Optional kann dabei der Parameter DURABILITY ergänzt werden. Dieser kann zwei Ausprägungen annehmen:

  • SCHEMA_ONLY: Die Datenspeicherung erfolgt nicht ACID-konform. Daten können bei Systemausfällen verloren gehen, die Struktur(=Schema) bleibt aber auf jeden Fall bestehen. Diese Option könnte man zum Beispiel für Tabellen mit Stammdaten verwenden, die sich nur sehr selten ändern.
  • SCHEMA_AND_DATA: Wird diese Option gewählt, erfolgt die Datenspeicherung ACID-konform. Daten gehen auch bei Systemausfällen nicht verloren.

Wenn der Parameter nicht angeben wird, ist SCHEMA_AND_DATA der Standartwert.

Da speicheroptimierte Tabellen weder Check-Constraints, Fremdschlüssel noch Trigger unterstützen, kann die Geschäftslogik nur durch den Einsatz von systemintern kompilierten gespeicherten Prozeduren (Natively Compiled Stored Procedure) erzwungen werden. Daher ist es in der Praxis sinnvoll, derartige Prozeduren für Schreibzugriffe anstelle von direktem INSERTUPDATE und DELETE zu verwenden.

Genau genommen ist die volle ACID-Compliance mit der Einstellung von DURABILITY noch nicht gegeben, auch wenn dies von Microsoft so bezeichnet wird. Denn hinter den vier Buchstaben verbergen sich die Begriffe Atomicity, Consistency, Isolation und Durability. Atomicity und Isolation werden mit Transaktionen erreicht, Durability mit Protokollierung und Consistency mit referenzieller Integrität. Letztere ist durch das Fehlen von Fremdschlüsseln nicht gegeben, lediglich Durability wird gewährleistet oder eben nicht. Möchte man also volle ACID-Compliance, muss die für Konsistenz notwendige Logik mit systemintern kompilierten gespeicherten Prozeduren selber implementiert werden.

Kein gruppierter Index für In-Memory-Tables

Da speicheroptimierte Tabellen keinen gruppierten Index unterstützen, muss beim Erstellen des Primärschlüssels die Option NONCLUSTERED explizit angegeben werden, da die sonst verwendete Standardvorgabe CLUSTERED einen Fehler erzeugen würde. Der Zusatz NOT NULL muss für Primärschlüsselspalten explizit angegeben werden, das wird nicht wie bei normalen Tabellen implizit so eingestellt.

BIN2-Collation der Datenbank

Da ein Index für Character-Spalten nur erstellt werden kann, wenn diese eine BIN2-Collation aufweisen, muss eine derartige Primärschlüsselspalte mit der Anweisung COLLATE mit einer solchen versehen werden. Unter Umständen kann es Sinn machen, schon beim Erstellen der Datenbank eine BIN2-Collation als Standardvorgabe für diese Datenbank zu definieren. Dann wird diese generell für alle Character-Spalten verwendet, wenn nichts anderes bei der Tabellen-Definition angegeben wird. Man erspart sich dann die separate Angabe beim Erstellen einer Tabelle.

CREATE DATABASE wawi_ram
ON PRIMARY ...
...
COLLATE Latin1_General_BIN2;

Erstellung von In-Memory-Tables: Ein Beispiel

Zum exemplarischen Erstellen der Tabellen artikelgruppen und artikel in der Datenbank infoakt_ram dienen nachfolgende Anweisungen.

CREATE TABLE dbo.artikelgruppen
(   artgr char(2) COLLATE Latin1_General_BIN2 NOT NULL,
    bezeichnung varchar(50) NOT NULL,
    CONSTRAINT pk_artikelgruppen PRIMARY KEY NONCLUSTERED (artgr)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
CREATE TABLE dbo.artikel
(    artnr int IDENTITY(1,1),
    bezeichnung varchar(60) NOT NULL,
    gruppe char(2) COLLATE Latin1_General_BIN2 NOT NULL,
    vkpreis smallmoney NOT NULL,
    lieferant int NOT NULL,
    ekpreis smallmoney NOT NULL,
    lieferzeit smallint,
    mindestbestand int,
    hinweis varchar(500),
    bestellmenge int NOT NULL CONSTRAINT df_artikel_bestellmenge DEFAULT 0,
    mwst tinyint NOT NULL CONSTRAINT df_artikel_mwst DEFAULT 19,
    aktiv bit NOT NULL CONSTRAINT df_artikel_aktiv DEFAULT 1,
    inaktiv_am smalldatetime,
    inaktiv_von sysname NULL,
    CONSTRAINT pk_artikel PRIMARY KEY NONCLUSTERED (artnr),
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Daten werden wie gewohnt mit DML-Anweisungen Daten in diese Tabelle eingefügt, in ihr geändert und auch wieder gelöscht. Beispiel:

INSERT INTO dbo.artikelgruppen (artgr, bezeichnung) VALUES ('GA', 'Garten');

Auch der Zugriff mit SELECT-Anweisungen ist wie gewohnt möglich.

In einer Datenbank lassen sich normale und speicheroptimierte Tabellen gemischt verwenden. Man muss sich also nicht für das eine oder andere entscheiden, die Interoperabilität ist jederzeit gegeben. Allerdings müssen die Einschränkungen beim Einsatz von Fremdschlüsseln und der Sortierung für Character-Datentypen berücksichtigt werden. Zur Demonstration verwende ich ein kleines Beispiel. Dazu nutze ich die klassische Tabelle gruppen, welche prinzipiell dieselbe Struktur wie die speicheroptimierte Tabelle artikelgruppen aufweist. In einer Anweisung wird diese mit der speicheroptimierten Tabelle artikel verknüpft. Die Anweisung soll neben der Artikelnummer, der Bezeichnung und dem Verkaufspreis auch die Bezeichnung aus der Artikelgruppe für alle Artikel mit einem Verkaufspreis ab 100 Euro liefern.

SELECT a.artnr, a.bezeichnung, a.vkpreis, g.bezeichnung AS artikelgruppe
FROM dbo.artikel a
INNER JOIN dbo.gruppen g ON a.gruppe = g.artgr
WHERE a.vkpreis > 100
ORDER BY artikelgruppe, a.bezeichnung;

Diese Anweisung liefert noch kein Ergebnis, sondern eine Fehlermeldung:

Meldung 468, Ebene 16, Status 9, Zeile 1
Ein Sortierungskonflikt zwischen 'Latin1_General_BIN2' und 'Latin1_General_CI_AS' im equal to-Vorgang kann nicht aufgelöst werden.

Dies liegt daran, dass Texte nicht miteinander verglichen werden können, wenn sie nicht dieselbe Collation (Sortierung) aufweisen. In der JOIN-Bedingung wird die Spalte gruppe aus der Tabelle artikel mit der Spalte artgr aus der Tabelle artikelgruppen gleichgesetzt. Beide sind mit dem Datentyp CHAR() definiert. Erstere verwendet die Sortierung Latin1_General_CI_AS, die andere aufgrund der Einschränkung für speicheroptimierte Tabellen die Sortierung Latin1_General_BIN2. Das bedeutet nicht, dass sich diese beiden Tabellen nicht kombinieren ließen. Es muss aber die Sortierung einer der beiden Spalten für den Vergleich in die Sortierung der anderen Spalten konvertiert werden. Dies wird mit der nachgestellten Anweisung COLLATE ziel_collation erreicht.

SELECT a.artnr, a.bezeichnung, a.vkpreis, g.bezeichnung AS artikelgruppe
FROM wawi.dbo.artikel a
INNER JOIN dbo.gruppen g ON a.gruppe = g.artgr COLLATE Latin1_General_BIN2
WHERE a.vkpreis > 100
ORDER BY artikelgruppe, a.bezeichnung;

Soll dies generell vermieden werden, sollte man in Erwägung ziehen, in einer Datenbank mit speicheroptimierten Tabellen generell eine BIN2-Sortierung einheitlich für alle Character-Spalten zu verwenden. Beim Verknüpfen von Tabellen tritt hier eine weitere Einschränkung auf. Anweisungen mit speicheroptimierten Tabellen können nicht datenbankübergreifend sein.

Integrität für In-Memory-Tables: Systemintern kompilierte gespeicherte Prozedur

Einmal ehrlich: "Systemintern kompilierte gespeicherte Prozedur" ist eine furchtbar künstliche Bezeichnung. Der englische Originalbegriff "Natively Compiled Stored Procedure" ist auch nicht viel besser oder vielsagender. Hinter diesem Begriff verbirgt sich die mit dem SQL Server 2014 neue Art von Prozeduren, die speziell für die Bearbeitung von speicheroptimierten Tabellen vorgesehen ist. Auch wenn dies keine offizielle Abkürzung ist, verwende ich das Kürzel NCSP, da dieses den Text leichter lesbar macht. Der Einsatz von NCSPs bietet die Möglichkeit, Geschäftslogik für speicheroptimierte Tabellen umzusetzen, da diese ja weder Fremdschlüssel, Trigger noch Check-Constraints unterstützen.

NCSPs werden wie gewohnt mit Transact-SQL programmiert, aber wie auch schon bei speicheroptimierten Tabellen an sich gibt es einige Einschränkungen. Dies ist unter anderem damit zu erklären, dass NCSPs im Gegensatz zu konventionellen Prozeduren nicht interpretiert werden, sondern als kompilierte DLLs vorliegen und ausgeführt werden.

Natively Compiled Stored Procedure (NCSP):  Einschränkungen

Die wichtigsten Einschränkungen sind:

  • NCSPs können nicht mit ALTER PROCEDURE geändert werden. Sie müssen bei jeder Änderung gelöscht und mit CREATE PROCEDURE neu erstellt werden.
  • Innerhalb einer NCSP können keine SET-Anweisungen verwendet werden. So kann auch das sonst generell verwendete SET NOCOUNT ON nicht zum Einsatz kommen.
  • Unterabfragen (Subqueries) können nicht verwendet werden.
  • Um zu prüfen, ob es bestimmte Daten gibt, kann EXISTS() nicht eingesetzt werden, da es auch eine Form einer Unterabfrage darstellt.
  • Dynamisches SQL mit der Anweisung EXECUTE() wird nicht unterstützt.

Eine NCSP wird wie gewohnt in einem Abfrageeditorfenster des SQL Server Management Studios erstellt. Eine Vorlage dazu erhält man über den Befehl NEU\SYSTEMINTERN KOMPILIERTE GESPEICHERTE PROZEDUR... aus dem Kontextmenü im Objekt-Explorer. Um die Übersichtlichkeit zu wahren, habe ich die Vorlage im nachfolgenden Listing etwas gekürzt. Bei dieser Prozedurart macht die in der Vorlage vorangestellte Anweisung zum Löschen der Prozedur – falls es diese schon gibt – Sinn, da ja kein ALTER PROCEDURE möglich ist.

-- Drop stored procedure if it already exists
IF OBJECT_ID('schema.procedure_name>','P') IS NOT NULL
   DROP PROCEDURE schema.procedure_name
GO
CREATE PROCEDURE schema.procedure_name
    -- Add the parameters for the stored procedure here
    @param1 datatype_for_param1,
    @param2 datatype_for_param2
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
( TRANSACTION ISOLATION LEVEL = transaction_isolation_level,
  LANGUAGE = 'language')
   --Insert statements for the stored procedure here
   SELECT @param1, @param2
END
GO

 

Natively Compiled Stored Procedure (NCSP): Optionen

Folgende Optionen sind für diese Prozedurart absolut notwendig, ein weglassen würde in Fehler münden.

  • NATIVE_COMPILATION: Diese Option legt den Prozedurtyp als NCSP fest.
  • SCHEMABINDING: Diese Option legt bei Prozeduren, wie zum Beispiel auch bei Sichten fest, dass an den zugrundeliegenden Objekten keine Änderungen vorgenommen werden können. Diese Objekte sind in der Praxis in erster Linie Tabellen. Da NCSPs kompiliert abgespeichert werden, ist das verwenden dieser Option verpflichtend.
  • EXECUTE AS: Diese Klausel legt fest, in welchem Sicherheitskontext eine gespeicherte Prozedur ausgeführt wird. Typischerweise verwendet man hier die in der Vorlage vorgeschlagene Variante EXECUTE AS OWNER. Andere sicherheitstechnisch relevante Einstellungen wären AS SELF oder AS benutzername.
  • ATOMIC WITH: Dieser Block ist bei dieser Prozedurart fix vorgegeben und gibt die unteilbare Ausführung der gesamten Prozedur an. Innerhalb der Prozedur kann es zu keinem COMMIT von einem Teil der Anweisungen kommen und durch einen Fehler wird ein ROLLBACK für alle enthaltenen Anweisungen ausgeführt. So kann auch keine eigene explizite Transaktion mit der Anweisung BEGIN TRANSACTION innerhalb einer NCSP gestartet werden. SET-Anweisungen werden ja innerhalb der Prozedur nicht unterstützt, sie müssen schon eingangs für die Kompilierung festgelegt werden. Exemplarisch ist dazu die Einstellung LANGUAGE, die entscheidend für die Interpretation und Ausgabe von Datumswerten ist, in der Vorlage enthalten.

Natively Compiled Stored Procedure (NCSP): Beispiel

Wenden wir uns nun einem Beispiel zu, wie wir NCSPs für die Einbindung von Geschäftslogiken einsetzen. Ziel ist es, damit Schreibzugriffe auf speicheroptimierte Tabellen nicht durch direktes INSERT, UPDATE oder DELETE umzusetzen. Grundsätzlich könnte man auch gewöhnliche Prozeduren dazu verwenden, aber das macht keinen Sinn, da man dadurch wieder auf Performancevorteile verzichten würde. Das Beispiel bildet die Neuanlage eines Artikels über eine NCSP ab. Diese ersetzt die Funktionalität von Constraints, die bei einer normalen Tabelle folgendes erzwingen würden:

  • Keine Eingabe einer ungültigen Artikelgruppe (Fremdschlüssel).
  • Keine Eingabe eines ungültigen Lieferanten (Fremdschlüssel).
  • Der Verkaufspreis darf nicht unter dem Einkaufspreis liegen (Check-Constraint).

NOT NULL-Spalten werden wie bei einer konventionellen Tabelle vom System geprüft und bedürfen keiner besonderen Behandlung. Ziel der Beispielprozedur ist es, als Ersatz für folgende Beispielanweisung zu dienen. Da die Artikelnummer in dieser Tabelle als Identität automatisch vergeben wird, bleibt Sie in der Anweisung unberücksichtigt.

INSERT INTO dbo.artikel (bezeichnung, gruppe, vkpreis, ekpreis, lieferant)
VALUES ('bezeichnung', 'gruppe', 20, 10, 1000);

Der Prozedur werden beim Aufruf die in die Tabelle einzutragenden Werte übergeben. Die Prozedur seinerseits gibt die für einen eingefügten Artikel vergebene neue Artikelnummer als OUTPUT-Parameter zurück. Im ersten Schritt wird geprüft, ob das übergebene Kürzel für die Artikelgruppe gültig ist. Da in einer NCSP keine Unterabfragen und auch kein EXISTS verwendet werden kann, fallen die üblicherweise verwendeten Varianten zur Prüfung des Vorhandenseins aus. Weder

IF (SELECT COUNT(*) FROM dbo.artikelgruppen WHERE artgr = @gr) = 0

noch

IF EXISTS(SELECT * FROM dbo.artikelgruppen WHERE artgr = @gr)

können verwendet werden. Daher muss auf eine andere Variante zurückgegriffen werden. Es bietet sich dazu das Deklarieren einer Variablen und Zuweisen des Wertes in einer direkten SELECT-Anweisung an.

SELECT @anz = COUNT(*)
FROM dbo.artikelgruppen
WHERE artgr = @gr;

Danach kann der Inhalt der Variable in gewohnter Weise geprüft und der Code fortgesetzt werden. Weist die Variable @anz den Wert 0 auf, existiert die Gruppe nicht und eine Fehlermeldung wird mit der Anweisung THROW generiert. Diese ersetzt die Systemfehlermeldung einer Fremdschlüsselverletzung. Der Meldungstext wird zuvor in der Variable @fehler zusammengesetzt, da THROW keinen Ausdruck aufnehmen kann. Derselbe Vorgang wiederholt sich für die Prüfung der Lieferantennummer. Wesentlich einfacher gestaltet sich die Prüfung der Eingabe für Ein- und Verkaufspreis. Zu beachten ist, dass für unterschiedliche mit THROW generierte Fehler auch unterschiedliche Fehlercodes vergeben werden. Dies ist notwendig, um in einem Frontend nicht auf die Interpretation des Meldungstextes angewiesen zu sein. Werden alle Prüfungen positiv abgeschlossen, kommt es zur eigentlich wichtigen Anweisung, dem Einfügen des neuen Artikels in die Tabelle. Die dabei über die Identität automatisch vergebene Artikelnummer wird über die Funktion SCOPE_IDENTITY() ausgelesen und über den OUTPUT-Parameter @nr zurückgegeben.

CREATE PROCEDURE dbo.sp_neuer_artikel
    @bez varchar(60),
    @gr char(2),
    @vk smallmoney,
    @ek smallmoney,
    @lief int,
    @nr int OUTPUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'german')
    DECLARE @anz int
    DECLARE @fehler varchar(100)

    -- Artikelgruppe gültig?
    SELECT @anz = COUNT(*)
    FROM dbo.artikelgruppen WHERE artgr = @gr;

    IF @anz = 0
    BEGIN
        SET @fehler = 'Artikelgruppe ''' + @gr + ''' ist ungültig.';
        THROW 50101, @fehler, 1;
    END
    ELSE
    BEGIN
        -- Lieferant gültig?
        SELECT @anz = COUNT(*)
        FROM dbo.lieferanten WHERE liefnr = @lief;

        IF @anz = 0
        BEGIN
            SET @fehler = 'Lieferant ' + CAST(@lief AS varchar) + ' ist ungültig.';
            THROW 50102, @fehler, 1;
        END
        -- EK nicht unter VK?
        ELSE IF @ek >= @vk
        BEGIN
            SET @fehler = 'Der Einkaufspreis muss unter dem Verkaufspreis liegen.';
            THROW 50103, @fehler, 1;
        END
        ELSE
        BEGIN
            INSERT INTO dbo.artikel (bezeichnung, gruppe, vkpreis, ekpreis, lieferant)
            VALUES (@bez, @gr, @vk, @ek, @lief);

            SET @nr = SCOPE_IDENTITY()
        END
    END
END

Der nun folgende Aufruf fügt erfolgreich einen neuen Datensatz in die Tabelle artikel ein.

DECLARE @neu int
DECLARE @bez varchar(60) = 'Informatik Fachmagazin'
DECLARE @gr char(2) = 'ZS'
DECLARE @vk smallmoney = 3.99
DECLARE @ek smallmoney = 2.75
DECLARE @lief int = 1003

EXEC dbo.sp_neuer_artikel @bez, @gr, @vk, @ek, @lief, @neu OUTPUT
SELECT @neu AS artikelnummer;

Verwendet man einen ungültigen Wert für die Artikelgruppe, erhält man beim Aufruf die erwartete Fehlermeldung:

Meldung 50101, Ebene 16, Status 1, Prozedur sp_neuer_artikel, Zeile 10
Artikelgruppe 'AB' ist ungültig.

Falls die Forderungen von ACID gerade auch in Bezug auf Datenintegrität erfüllt werden müssen, sollten alle Schreibzugriffe auf speicheroptimierte Tabellen nach dieser Grundlogik durchgeführt werden.

Auf den ersten Blick mag diese Vorgangsweise aufwendig erscheinen. Aber wir müssen uns vor Augen halten, dass das Ziel von In-Memory-Tables die Optimierung der "Performance" ist. Diese Performance wird durch einen höheren Aufwand in der Applikationsentwicklung erkauft.

SQL Server Ressourcenpools: Speichernutzung beschränken

Beim Einsatz von In-Memory OLTP ist es sinnvoll zu konfigurieren, wie viel Arbeitsspeicher für die Datenbank zur Verfügung steht. Denn RAM wird auch für andere Prozesse benötigt und eine Datenbank im Speicher soll schließlich nicht das Gesamtsystem in Schwierigkeiten bringen.

Um die Speichernutzung zu limitieren, benötigen Sie einen Ressourcenpool. Über einen Ressourcenpool können verschiedene Ressourcen verwaltet werden, darunter auch der Arbeitsspeicher. Der Prozentsatz wird nach Maßgabe des vorhandenen Arbeitsspeichers und des Bedarfs der Datenbank festgelegt. Genaue Informationen zur Vorgangsweise beim Abschätzen des Speicherbedarfs sind hier zu finden.

Ressourcenpools werden im Objekt-Explorer unter dem Ordner Verwaltung konfiguriert. Da hier das Erstellen mit dem grafischen Dialog über das Kontextmenü nicht funktioniert, muss man auf den direkten Aufruf der entsprechenden SQL-Anweisungen und Systemprozeduren zurückgreifen.

Mit der nachfolgenden Anweisung wird ein neuer Ressourcenpool mit dem Namen infoakt_memory_pool erstellt, der 50% des Arbeitsspeichers belegt. Um exakt diesen Wert zu erreichen, verwendet man für Minimum- und Maximalwert denselben Prozentsatz.

CREATE RESOURCE POOL infoakt_memory_pool
WITH
    ( MIN_MEMORY_PERCENT = 50,
      MAX_MEMORY_PERCENT = 50 );
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Nach dem Erstellen des Ressourcenpools muss dieser noch mit der Systemprozedur sp_xtp_bind_db_resource_pool der Datenbank zugewiesen werden.

EXEC dbo.sp_xtp_bind_db_resource_pool 'infoakt_ram', ' infoakt _memory_pool'

Nach der Zuweisung darf man nicht vergessen, diese noch zu aktivieren. Das wird durch das Offline- und danach wieder Onlineschalten der Datenbank erreicht. Dabei darf keine Verbindung zu dieser Datenbank bestehen. Erledigt werden kann das über das Kontextmenü des Objekt-Explorers mit der Anweisung TASKS\OFFLINE SCHALTEN beziehungsweise TASKS\ONLINE SCHALTEN oder entsprechenden Anweisungen im Editor.

USE master
GO
ALTER DATABASE wawi_ram SET OFFLINE;
GO
ALTER DATABASE wawi_ram SET ONLINE;
GO

Die Zuordnung der Datenbank zu diesem Ressourcenpool wird über nachfolgende Anweisung angezeigt:

SELECT d.database_id, d.name, d.resource_pool_id, p.name
FROM sys.databases d
INNER JOIN sys.resource_governor_resource_pools p ON d.resource_pool_id= p.pool_id;

Der aktuelle Speicherbedarf lässt sich mit dieser Anweisung ermitteln, das Ergebnis zeigt Abb. 2. Die in meiner Arbeitsumgebung erzielten vergleichsweise niedrigen Werte lassen sich mit dem dynamisch zugeordneten Speicher in der virtuellen Umgebung erklären.

SELECT pool_id, name,
       min_memory_percent AS prozent_min,
       max_memory_percent AS prozent_max,
       max_memory_kb/1024 AS mb_max,
       used_memory_kb/1024 AS mb_verwendet
FROM sys.dm_resource_governor_resource_pools;

Sind Datenbank und Ressourcenpool nur für Testzwecke erstellt worden, können die nachfolgenden Anweisungen zum Aufräumen verwendet werden.

USE master
GO
EXEC dbo.sp_xtp_unbind_db_resource_pool 'infoakt_ram'
GO
DROP RESOURCE POOL infoakt_memory_pool;
GO
DROP DATABASE infoakt_ram;
GO

Fazit

Ganze Tabellen einer SQL Server Datenbank im Speicher zu halten, ist eine bahnbrechende neue Möglichkeit, die Performance von Datenbanklösungen signifikant zu steigern. Auch wenn es auch noch die eine oder andere Einschränkung gibt, da nicht alle von klassischen Tabellen verfügbaren Möglichkeiten genutzt werden können, sind In-Memory-Tables eine faszinierende Innovation. Schade ist es allerdings, dass diese nur mit der Enterprise- und nicht mit der Standard-Edition unterstützt werden.

In zukünftigen Versionen sind hier noch viele Verbesserungen zu erwarten. Volle ACID-Compliance ist aktuell schon durch den zusätzlichen Einsatz von NCSPs erreichbar. Zum Testen der Funktionalität kann die Evaluation-Edition, welcher mit der Enterprise-Edition featuregleich ist, verwendet werden. 

Das aus meiner Sicht größte Manko: SQL Server In-Memory OLTP steht nur bei der Enterprise Edition des SQL Server 2014 zur Verfügung.

Autor

Klemens Konopasek

Klemens Konopasek schreibt zu SQL Server-Themen. Er ist ein bekannter Buch-Autor, Trainer, Consultant und Lehrbauftragter der FH an der FH Joanneum in Graz und Kapfenberg.
>> Weiterlesen
Buch des Autors:

botMessage_toctoc_comments_9210