Condividi tramite

variabili di tabella Memory-Optimized

Oltre alle tabelle ottimizzate per la memoria (per un accesso efficiente ai dati) e alle stored procedure compilate in modo nativo (per l'elaborazione efficiente delle query e l'esecuzione della logica di business) In-Memory OLTP introduce un terzo tipo di oggetto: il tipo di tabella ottimizzato per la memoria. Una variabile di tabella creata usando un tipo di tabella ottimizzata per la memoria è una variabile di tabella ottimizzata per la memoria.

Le variabili di tabella ottimizzate per la memoria offrono i vantaggi seguenti rispetto alle variabili di tabella basate su disco:

  • Le variabili vengono archiviate solo in memoria. L'accesso ai dati è più efficiente perché il tipo di tabella ottimizzato per la memoria usa lo stesso algoritmo ottimizzato per la memoria e le stesse strutture di dati usate per le tabelle ottimizzate per la memoria, soprattutto quando le variabili vengono usate nelle stored procedure compilate in modo nativo.

  • Con le variabili di tabella ottimizzate per la memoria, non esiste alcun utilizzo di tempdb. Le variabili di tabella non vengono archiviate in tempdb e non usano risorse in tempdb.

Gli scenari di utilizzo tipici per le variabili di tabella ottimizzate per la memoria sono:

  • Archiviazione di risultati intermedi e creazione di singoli set di risultati in base a più query nelle stored procedure compilate in modo nativo.

  • Passaggio dei parametri con valori di tabella nelle stored procedure nativamente compilate e interpretate.

  • Sostituzione delle variabili di tabella basate su disco fisso e, in alcuni casi, delle tabelle #temp locali a una procedura memorizzata. Ciò è particolarmente utile se nel sistema sono presenti molte contese di tempdb.

  • Le variabili di tabella possono essere usate per simulare i cursori nelle stored procedure compilate in modo nativo, che consentono di aggirare le limitazioni dell'area funzionale nelle stored procedure compilate in modo nativo.

Analogamente alle tabelle ottimizzate per la memoria, SQL Server genera una DLL per ogni tipo di tabella ottimizzato per la memoria. La compilazione viene richiamata quando viene creato il tipo di tabella ottimizzato per la memoria e non quando viene usato per creare variabili di tabella ottimizzate per la memoria. Questa DLL include le funzioni per l'accesso agli indici e il recupero di dati dalle variabili di tabella. Quando una variabile di tabella ottimizzata per la memoria viene dichiarata in base al tipo di tabella, viene creata un'istanza delle strutture di tabella e di indice corrispondenti al tipo di tabella nella sessione utente. La variabile di tabella può quindi essere usata nello stesso modo delle variabili di tabella basate su disco. È possibile inserire, aggiornare ed eliminare righe nella variabile di tabella ed è possibile usare le variabili nelle query Transact-SQL. È anche possibile passare le variabili in stored procedure compilate e interpretate in modo nativo, come parametri con valori di tabella (TVP).

L'esempio seguente illustra un tipo di tabella ottimizzato per la memoria dell'esempio OLTP In-Memory basato su AdventureWorks (esempio OLTP di SQL Server 2014 In-Memory OLTP).

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 );

L'esempio mostra che la sintassi dei tipi di tabella ottimizzata per la memoria è simile ai tipi di tabella basati su disco, con le eccezioni seguenti:

  • MEMORY_OPTIMIZED=ON indica che il tipo di tabella è ottimizzato per la memoria.

  • Il tipo deve avere almeno un indice. Come per le tabelle ottimizzate per la memoria, puoi utilizzare indici hash e noncluster.

    Per un indice hash, il numero di bucket deve essere compreso tra uno e due volte il numero di chiavi di indice univoco previste. Per altre informazioni, vedere Determinazione del numero di bucket corretto per gli indici hash.

  • Le restrizioni relative al tipo di dati e ai vincoli per le tabelle ottimizzate per la memoria si applicano anche ai tipi di tabella ottimizzati per la memoria. Ad esempio, in SQL Server 2014 sono supportati vincoli predefiniti, ma i vincoli CHECK non sono.

Come le tabelle ottimizzate per la memoria, anche le variabili di tabella sono ottimizzate per la memoria.

  • Non supportare piani paralleli.

  • Deve essere in memoria e non usare le risorse del disco.

Le variabili di tabella basate su disco esistono in tempdb. Le variabili di tabella ottimizzate per la memoria sono presenti nel database utente , ma non usano l'archiviazione e non vengono ripristinate.

Non è possibile creare una variabile di tabella ottimizzata per la memoria usando la sintassi in linea. A differenza delle variabili di tabella basate su disco, è prima necessario creare un tipo.

Parametri con valori di tabella

Lo script di esempio seguente mostra la dichiarazione di una variabile di tabella come tipo Sales.SalesOrderDetailType_inmemdi tabella ottimizzata per la memoria, l'inserimento di tre righe nella variabile e il passaggio della variabile come valore TVP in 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

I tipi di tabella ottimizzati per la memoria possono essere usati come tipo per i parametri delle stored procedure con valori di tabella (TVP) e possono essere usati dai clienti esattamente come i tipi di tabella basati su disco e TVP. Di conseguenza, la chiamata di stored procedure con TVP ottimizzati per la memoria e di stored procedure compilate in modo nativo funziona esattamente come la chiamata di stored procedure interpretate con TVP basati su disco.

Sostituzione della tabella #temp

Nell'esempio seguente vengono illustrati i tipi di tabella ottimizzati per la memoria e le variabili di tabella come sostituzione delle tabelle temporanee locali di una procedura memorizzata.

-- 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

Creazione di un singolo set di risultati

Nell'esempio seguente viene illustrato come archiviare i risultati intermedi e creare singoli set di risultati in base a più query nelle stored procedure compilate in modo nativo. L'esempio calcola l'unione 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

Utilizzo della memoria per le variabili di tabella

Il consumo di memoria per le variabili di tabella è simile alle tabelle ottimizzate per la memoria, ad eccezione degli indici non cluster. Se si inseriscono molte righe in variabili di tabella ottimizzate per la memoria con indici non cluster e se le chiavi di indice sono di grandi dimensioni, queste variabili di tabella useranno una quantità sproporzionata di memoria. Gli indici non cluster in variabili di tabella di grandi dimensioni richiedono una quantità di memoria maggiore rispetto a un indice non cluster per lo stesso numero di righe inserite in una tabella (maggiore memoria nelle pagine di indice).

La memoria per le variabili di tabella proviene dal pool di risorse di Resource Governor del database.

A differenza delle tabelle ottimizzate per la memoria, la memoria utilizzata (incluse le righe eliminate) dalle variabili di tabella viene liberata quando la variabile di tabella esce dall'ambito.

La memoria è considerata parte dell'unico utilizzatore di memoria PGPOOL del database.

Vedere anche

Supporto di Transact-SQL per OLTP in memoria

  • Last updated on