Freigeben über

Memory-Optimized Tabellenvariablen

Neben speicheroptimierten Tabellen (für effizienten Datenzugriff) und nativ kompilierten gespeicherten Prozeduren (für effiziente Abfrageverarbeitung und Geschäftslogikausführung In-Memory) führt OLTP eine dritte Art von Objekt ein: den speicheroptimierten Tabellentyp. Eine Tabellenvariable, die mit einem speicheroptimierten Tabellentyp erstellt wird, ist eine speicheroptimierte Tabellenvariable.

Speicheroptimierte Tabellenvariablen bieten die folgenden Vorteile im Vergleich zu datenträgerbasierten Tabellenvariablen:

  • Die Variablen werden nur im Arbeitsspeicher gespeichert. Der Datenzugriff ist effizienter, da der speicheroptimierte Tabellentyp denselben speicheroptimierten Algorithmus und Datenstrukturen verwendet, die für speicheroptimierte Tabellen verwendet werden, insbesondere, wenn die Variablen in nativ kompilierten gespeicherten Prozeduren verwendet werden.

  • Bei speicheroptimierten Tabellenvariablen gibt es keine tempdb-Auslastung. Tabellenvariablen werden nicht in tempdb gespeichert und verwenden keine Ressourcen in tempdb.

Die typischen Verwendungsszenarien für speicheroptimierte Tabellenvariablen sind:

  • Speichern von Zwischenergebnissen und Erstellen einzelner Resultsets basierend auf mehreren Abfragen in nativ kompilierten gespeicherten Prozeduren.

  • Übergeben von Tabellenwertparametern an nativ kompilierte und interpretierte gespeicherte Prozeduren.

  • Ersetzen von festplattenbasierten Tabellenvariablen und teilweise #temp-Tabellen, die lokal Teil einer gespeicherten Prozedur sind. Dies ist besonders nützlich, wenn im System viel tempdb-Contention besteht.

  • Tabellenvariablen können verwendet werden, um Cursor in nativ kompilierten gespeicherten Prozeduren zu simulieren, die Ihnen dabei helfen können, Oberflächenbereichseinschränkungen in nativ kompilierten gespeicherten Prozeduren zu umgehen.

Wie speicheroptimierte Tabellen generiert SQL Server eine DLL für jeden speicheroptimierten Tabellentyp. (Die Kompilierung wird aufgerufen, wenn der speicheroptimierte Tabellentyp erstellt wird und nicht beim Erstellen speicheroptimierter Tabellenvariablen.) Diese DLL enthält die Funktionen für den Zugriff auf Indizes und das Abrufen von Daten aus den Tabellenvariablen. Wenn eine speicheroptimierte Tabellenvariable basierend auf dem Tabellentyp deklariert wird, wird in der Benutzersitzung eine Instanz der Tabellen- und Indexstrukturen erstellt, die dem Tabellentyp entsprechen. Die Tabellenvariable kann dann auf die gleiche Weise wie datenträgerbasierte Tabellenvariablen verwendet werden. Sie können Zeilen in die Tabellenvariable einfügen, aktualisieren und löschen, und Sie können die Variablen in Transact-SQL Abfragen verwenden. Sie können die Variablen auch in systemeigene kompilierte und interpretierte gespeicherte Prozeduren als Tabellenwertparameter (TVP) übergeben.

Das folgende Beispiel zeigt einen speicheroptimierten Tabellentyp aus dem AdventureWorks-basierten In-Memory OLTP-Beispiel (SQL Server 2014 In-Memory OLTP-Beispiel).

CREATE TYPE Sales.SalesOrderDetailType_inmem
   AS TABLE
(
   OrderQty         smallint   NOT NULL,
   ProductID        int        NOT NULL,

   SpecialOfferID   int        NOT NULL
      INDEX  IX_SpecialOfferID  NONCLUSTERED,

   LocalID          int        NOT NULL,

   INDEX IX_ProductID HASH (ProductID)
      WITH ( BUCKET_COUNT = 8 )
)
WITH ( MEMORY_OPTIMIZED = ON );

Das Beispiel zeigt, dass die Syntax von speicheroptimierten Tabellentypen mit den folgenden Ausnahmen mit datenträgerbasierten Tabellentypen vergleichbar ist:

  • MEMORY_OPTIMIZED=ON gibt an, dass der Tabellentyp speicheroptimiert ist.

  • Der Typ muss mindestens einen Index aufweisen. Wie bei speicheroptimierten Tabellen können Sie Hash- und nicht gruppierte Indizes verwenden.

    Bei einem Hashindex sollte die Bucketanzahl etwa ein bis zwei mal die Anzahl der erwarteten eindeutigen Indexschlüssel sein. Weitere Informationen finden Sie unter Bestimmen der richtigen Bucketanzahl für Hashindizes.

  • Die Datentyp- und Einschränkungseinschränkungen für speicheroptimierte Tabellen gelten auch für speicheroptimierte Tabellentypen. In SQL Server 2014 werden z. B. Standardeinschränkungen unterstützt, CHECK-Einschränkungen werden jedoch nicht unterstützt.

Wie speicheroptimierte Tabellen, speicheroptimierte Tabellenvariablen,

  • Unterstützen Sie keine parallelen Pläne.

  • Muss in den Arbeitsspeicher passen und keine Datenträgerressourcen verwenden.

Datenträgerbasierte Tabellenvariablen sind in tempdb vorhanden. Speicheroptimierte Tabellenvariablen sind in der Benutzerdatenbank vorhanden (sie verbrauchen jedoch keinen Speicher und werden nicht wiederhergestellt).

Sie können keine speicheroptimierte Tabellenvariable mithilfe der Inlinesyntax erstellen. Im Gegensatz zu datenträgerbasierten Tabellenvariablen müssen Sie zuerst einen Typ erstellen.

Tabellenwertparameter

Das folgende Beispielskript zeigt die Deklaration einer Tabellenvariable als speicheroptimierten Tabellentyp Sales.SalesOrderDetailType_inmem, das Einfügen von drei Zeilen in die Variable und das Übergeben der Variablen als TVP an Sales.usp_InsertSalesOrder_inmem.

DECLARE @od Sales.SalesOrderDetailType_inmem,  
  @SalesOrderID uniqueidentifier,  
  @DueDate datetime2 = SYSDATETIME()  
  
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES  
  (1, 888, 2, 1),  
  (2, 450, 13, 1),  
  (3, 841, 1, 1)  
  
EXEC Sales.usp_InsertSalesOrder_inmem  
  @SalesOrderID = @SalesOrderID,  
  @DueDate = @DueDate,  
 @OnlineOrderFlag = 1,  
  @SalesOrderDetails = @od

Speicheroptimierte Tabellentypen können als Typ für gespeicherte Prozedurtabellenwertparameter (TVPs) verwendet werden und können von Clients genau so referenziert werden wie datenträgerbasierte Tabellentypen und TVPs. Daher funktioniert der Aufruf gespeicherter Prozeduren mit speicheroptimierten TVPs und nativ kompilierten gespeicherten Prozeduren genau wie der Aufruf interpretierter gespeicherter Prozeduren mit datenträgerbasierten TVPs.

#temp Tabellenersetzung

Das folgende Beispiel zeigt speicheroptimierte Tabellentypen und Tabellenvariablen als Ersatz für #temp Tabellen, die lokal für eine gespeicherte Prozedur sind.

-- Using SQL procedure and temp table  
CREATE TABLE #tempTable (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
  
CREATE PROCEDURE sqlProc  
AS  
BEGIN  
  TRUNCATE TABLE #tempTable  
  
  INSERT #tempTable VALUES (1)  
  INSERT #tempTable VALUES (2)  
  INSERT #tempTable VALUES (3)  
  SELECT * FROM #tempTable  
END  
GO  
  
-- Using natively compiled stored procedure and table variable  
CREATE TYPE TT AS TABLE (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
GO  
  
CREATE PROCEDURE NCSPProc  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @tableVariable TT  
  INSERT @tableVariable VALUES (1)  
  INSERT @tableVariable VALUES (2)  
  INSERT @tableVariable VALUES (3)  
  SELECT c FROM @tableVariable  
END  
GO

Erstellen einer einzelnen Ergebnismenge

Das folgende Beispiel zeigt, wie Zwischenergebnisse gespeichert und einzelne Resultsets basierend auf mehreren Abfragen in nativ kompilierten gespeicherten Prozeduren erstellt werden. Das Beispiel berechnet die Vereinigung SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2.

CREATE DATABASE hk  
GO  
ALTER DATABASE hk ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE hk ADD FILE( NAME = 'hk_mod' , FILENAME = 'c:\data\hk_mod') TO FILEGROUP hk_mod;  
  
USE hk  
GO  
  
CREATE TYPE tab1 AS TABLE (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON)  
  
CREATE TABLE dbo.t1 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
CREATE TABLE dbo.t2 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
  
INSERT INTO dbo.t1 VALUES (1), (2)  
INSERT INTO dbo.t2 VALUES (3), (4)  
GO  
  
CREATE PROCEDURE dbo.p1  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS  
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )  
  
    DECLARE @t dbo.tab1  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t1;  
  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t2;  
  
    SELECT c1 FROM @t;  
  END  
GO  
  
EXEC dbo.p1  
GO

Arbeitsspeicherverbrauch für Tabellenvariablen

Die Speicherauslastung für Tabellenvariablen ähnelt speicheroptimierten Tabellen, mit Ausnahme von nicht gruppierten Indizes. Wenn Sie viele Zeilen in speicheroptimierte Tabellenvariablen mit nicht gruppierten Indizes einfügen und wenn die Indexschlüssel groß sind, verwenden diese Tabellenvariablen einen unverhältnismäßigen Arbeitsspeicher. Nicht gruppierte Indizes für große Tabellenvariablen erfordern proportional mehr Arbeitsspeicher als ein nicht gruppierter Index würde für die gleiche Anzahl von Zeilen erforderlich sein, die in eine Tabelle eingefügt wurden (mehr Arbeitsspeicher auf den Indexseiten).

Arbeitsspeicher für Tabellenvariablen stammt aus dem Resource Governor-Ressourcenpool der Datenbank.

Im Gegensatz zu speicheroptimierten Tabellen wird der von Tabellenvariablen verbrauchte Speicher (einschließlich gelöschter Zeilen) freigegeben, wenn die Tabellenvariable den Gültigkeitsbereich überschreitet.

Der Speicher wird als Teil des einzelnen PGPOOL-Speicherverbrauchs der Datenbank berücksichtigt.

Siehe auch

Transact-SQL Unterstützung für In-Memory OLTP

  • Last updated on