panoramica e scenari di utilizzo di OLTP In-Memory

  • Articolo

Si applica a: SQL Server Azure SQL DatabaseIstanza gestita di SQL di Azure

In-Memory OLTP è la tecnologia principale disponibile in SQL Server e database SQL per ottimizzare le prestazioni di elaborazione delle transazioni, inserimento dati, caricamento dati e scenari di dati temporanei. Questo articolo include una panoramica della tecnologia e descrive gli scenari di utilizzo per OLTP in memoria. Usare queste informazioni per determinare se OLTP in memoria è appropriato all'applicazione usata. L'articolo si conclude con un esempio che illustra gli oggetti OLTP in memoria, un riferimento a una dimostrazione sulle prestazioni e riferimenti a risorse che è possibile usare per i passaggi successivi.

Questo articolo illustra la tecnologia OLTP In-Memory sia in SQL Server che in database SQL. Per altre informazioni specifiche per i dati in memoria in Azure SQL, vedere Ottimizzare le prestazioni usando tecnologie in memoria in Azure SQL Database e Istanza gestita di SQL di Azure e blog: In-Memory OLTP in Azure SQL Database.

Panoramica di OLTP in memoria

OLTP in memoria può fornire elevati miglioramenti delle prestazioni per i carichi di lavoro appropriati. Anche se i clienti in alcuni casi hanno potuto migliorare di 30 volte le prestazioni, il miglioramento che si ottiene dipende in realtà dal proprio carico di lavoro.

Da cosa dipende questo miglioramento delle prestazioni? In sostanza, In-Memory OLTP migliora le prestazioni dell'elaborazione delle transazioni rendendo più efficiente l'accesso ai dati e l'esecuzione delle transazioni e rimuovendo la contesa di blocco e latch tra transazioni in esecuzione simultanea. In-Memory OLTP non è veloce perché è in memoria; è veloce perché è ottimizzato per i dati in memoria. L'archiviazione dei dati, l'accesso e l'elaborazione degli algoritmi sono stati riprogettati interamente per sfruttare i miglioramenti più recenti di elaborazione in memoria e concorrenza elevata.

Ora, solo perché i dati si trovano in memoria non significa perderlo quando si verifica un errore. Per impostazione predefinita, tutte le transazioni sono completamente durevoli, ovvero si hanno le stesse garanzie di durabilità che si hanno per qualsiasi altra tabella in SQL Server: come parte del commit della transazione, tutte le modifiche vengono scritte nel log delle transazioni su disco. Se si verifica un errore in qualsiasi momento dopo il commit della transazione, i dati sono presenti quando il database torna online. Inoltre, In-Memory OLTP funziona con tutte le funzionalità di disponibilità elevata e ripristino di emergenza di SQL Server, ad esempio gruppi di disponibilità Always On, istanze del cluster di failover Always On (SQL Server) , backup/ripristino e così via.

Per usare In-Memory OLTP nel database, usare uno o più dei tipi di oggetti seguenti:

  • Letabelle con ottimizzazione per la memoria vengono usate per archiviare i dati utente. È possibile dichiarare una tabella ottimizzata per la memoria al momento della creazione.
  • Letabelle non durevoli vengono usate per i dati temporanei, per la memorizzazione nella cache o per un set di risultati intermedio (sostituendo le tabelle temporanee tradizionali). Una tabella non durevole è una tabella ottimizzata per la memoria dichiarata con DURABILITY=SCHEMA_ONLY, vale a dire che le modifiche apportate a queste tabelle non comportano operazioni di I/O. In questo modo si evita l'utilizzo di risorse di I/O del log per i casi in cui la durabilità non è un problema.
  • Itipi di tabella con ottimizzazione per la memoria vengono usati per i parametri con valori di tabella, ovvero come set di risultati intermedi nelle stored procedure. Questi tipi possono essere usati al posto dei tipi di tabella tradizionali. Le variabili di tabella e i parametri con valori di tabella che vengono dichiarati usando un tipo di tabella ottimizzata per la memoria ereditano i vantaggi delle tabelle non durevoli ottimizzate per la memoria: accesso efficiente ai dati e nessuna operazione I/O.
  • Imoduli T-SQL compilati in modo nativo vengono usati per ridurre ulteriormente il tempo impiegato per una singola transazione riducendo i cicli di CPU necessari per elaborare le operazioni. È possibile dichiarare un modulo Transact-SQL in modo da essere compilato in modo nativo al momento della creazione. Attualmente, i moduli T-SQL che possono essere compilati in modo nativo sono i seguenti: stored procedure, trigger e funzioni scalari definite dall'utente.

In-Memory OLTP è integrato in SQL Server e database SQL. Poiché questi oggetti si comportano in modo simile alle controparti tradizionali, è spesso possibile ottenere vantaggi in termini di prestazioni, apportando solo modifiche minime al database e all'applicazione. Inoltre, nello stesso database è possibile avere sia le tabelle ottimizzate per la memoria che le tabelle tradizionali basati su disco ed eseguire le query in entrambi i tipi di tabella. Nella parte inferiore di questo articolo è disponibile uno script Transact-SQL con un esempio per ognuno di questi tipi di oggetti.

Scenari di utilizzo per In-Memory OLTP

In-Memory OLTP non è un pulsante magic go-fast e non è adatto per tutti i carichi di lavoro. Ad esempio, le tabelle ottimizzate per la memoria non abbassano l'utilizzo della CPU se la maggior parte delle query esegue aggregazioni su intervalli di dati di grandi dimensioni. Gli indici columnstore sono utili per questo scenario.

Ecco un elenco di scenari e modelli di applicazione in cui i clienti hanno avuto successo con In-Memory OLTP.

Elaborazione di transazioni con velocità effettiva elevata e bassa latenza

È lo scenario principale per cui è stato creato OLTP in memoria: supporto di volumi elevati di transazioni con bassa latenza costante per le singole transazioni.

Scenari di carico di lavoro comuni sono: intermediazione di strumenti finanziari, scommesse sportive, giochi per dispositivi mobili e servizi pubblicitari. Un altro modello comune è quello di un "catalogo" letto e/o aggiornato di frequente. Un esempio è la posizione in cui sono presenti file di grandi dimensioni, ognuno distribuito su più nodi del cluster e si cataloga il percorso di ogni partizione di ogni file in una tabella ottimizzata per la memoria.

Considerazioni sull'implementazione

Usare tabelle ottimizzate per la memoria per le tabelle delle transazioni principali, ovvero le tabelle con le transazioni più critiche per le prestazioni. Usare le stored procedure compilate in modo nativo per ottimizzare l'esecuzione della logica associata alla transazione aziendale. Quanto maggiore è la distribuzione della logica nelle stored procedure del database, tanto maggiore sarà il vantaggio che si ottiene dall'uso di OLTP in memoria.

Per iniziare a usare questo approccio in un'applicazione esistente:

  1. Usare il report di analisi delle prestazioni delle transazioni per identificare gli oggetti di cui eseguire la migrazione.
  2. Usare gli advisor per l'ottimizzazione della memoria e la compilazione nativa per facilitare la migrazione.

Inserimento di dati, tra cui IoT (Internet delle cose)

OLTP in memoria è consigliato per inserire contemporaneamente grandi volumi di dati da molte origini diverse. È spesso utile inserire i dati in un database SQL Server rispetto ad altre destinazioni, perché SQL Server esegue rapidamente query sui dati e consente di ottenere informazioni dettagliate in tempo reale.

Modelli di applicazione comuni sono:

  • L'inserimento di letture ed eventi dei sensori in modo da consentire le notifiche nonché l'analisi cronologica.
  • La gestione degli aggiornamenti batch, anche da più origini, riducendo al minimo l'impatto sul carico di lavoro di lettura simultaneo.

Considerazioni sull'implementazione

Usare una tabella ottimizzata per la memoria per l'inserimento dei dati. Se l'operazione di inserimento è costituita principalmente da inserimenti (anziché aggiornamenti) e il footprint di memoria di OLTP in memoria dei dati è un problema, procedere come segue:

  • Usare un processo per eseguire regolarmente l'offload dei dati in una tabella basata su disco con un indice columnstore cluster, usando un processo che esegue INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; o
  • Usare una tabella temporale ottimizzata per la memoria per gestire i dati cronologici: in questo modo, i dati cronologici risiedono su disco e lo spostamento dei dati viene gestito dal sistema.

Il repository di esempi di SQL Server contiene un'applicazione intelligente della griglia che usa una tabella temporale ottimizzata per la memoria, un tipo di tabella ottimizzata per la memoria e una stored procedure compilata in modo nativo per velocizzare l'inserimento dei dati, gestendo il footprint di memoria di OLTP in memoria dei dati del sensore:

Memorizzazione nella cache e stato della sessione

La tecnologia OLTP In-Memory rende il motore di database in SQL Server o Azure SQL database una piattaforma interessante per mantenere lo stato della sessione (ad esempio, per un'applicazione ASP.NET) e per la memorizzazione nella cache.

ASP.NET stato sessione è un caso d'uso corretto per In-Memory OLTP. Con SQL Server, un cliente ha quasi raggiunto 1,2 milioni di richieste al secondo. Nel frattempo, questo cliente ha iniziato a usare OLTP in memoria per le esigenze di memorizzazione nella cache di tutte le applicazioni di livello intermedio nell'organizzazione. Dettagli: come bwin usa SQL Server 2016 (13.x) In-Memory OLTP per ottenere prestazioni e scalabilità senza precedenti

Considerazioni sull'implementazione

È possibile usare tabelle ottimizzate per la memoria non durevoli come semplice archivio chiave-valore tramite l'archiviazione di un BLOB in una colonna varbinary(max). In alternativa, è possibile implementare una cache semistrutturata con supporto JSON in SQL Server e database SQL. Infine, è possibile creare una cache relazionale completa tramite tabelle non durevoli con uno schema relazionale completo, compresi vari tipi di dati e vincoli.

Introduzione all'ottimizzazione della memoria ASP.NET stato della sessione usando gli script pubblicati in GitHub per sostituire gli oggetti creati dal provider di stato della sessione SQL Server predefinito: aspnet-session-state

Case study di un cliente

Sostituzione dell'oggetto tempdb

Usare tabelle non durevoli e tipi di tabella ottimizzati per la memoria per sostituire le strutture basate su tradizionali tempdb , ad esempio tabelle temporanee, variabili di tabella e parametri con valori di tabella .

Le variabili di tabella con ottimizzazione per la memoria e le tabelle non durevoli riducono in genere l'utilizzo di CPU ed eliminano completamente le operazioni di I/O sui log rispetto alle variabili di tabella tradizionali e alla tabella #temp.

Considerazioni sull'implementazione

Per un'introduzione, vedere: Miglioramento delle prestazioni della tabella temporanea e della variabile di tabella con l'ottimizzazione della memoria.

Case study di un cliente

ETL (Extract, Transform, Load, ovvero estrazione, trasformazione e caricamento)

I flussi di lavoro ETL includono spesso il caricamento dei dati in una tabella di staging, le trasformazioni dei dati e il caricamento nelle tabelle finali.

Usare tabelle non durevoli ottimizzate per la memoria per lo staging dei dati. Eliminano completamente tutte le operazioni di I/O e rendono più efficiente l'accesso ai dati.

Considerazioni sull'implementazione

Se si eseguono le trasformazioni nella tabella di staging come parte del flusso di lavoro, è possibile usare le stored procedure compilate in modo nativo per velocizzare tali trasformazioni. Se è possibile eseguire queste trasformazioni in parallelo, si ottengono vantaggi aggiuntivi per la scalabilità grazie all'ottimizzazione della memoria.

Script di esempio

Prima di iniziare a usare OLTP in memoria, è necessario creare il filegroup MEMORY_OPTIMIZED_DATA. È inoltre consigliabile usare il livello di compatibilità del database 130 (o superiore) e impostare l'opzione di database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT su ON.

È possibile usare lo script del collegamento seguente per creare il filegroup nella cartella dati predefinita e per configurare le impostazioni consigliate:

Lo script di esempio seguente illustra In-Memory oggetti OLTP che è possibile creare nel database.

Prima di tutto configurando il database per In-Memory OLTP.

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

È possibile creare tabelle con durabilità diverse:

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

È possibile creare un tipo di tabella come tabella in memoria.

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

È possibile creare una stored procedure compilata in modo nativo. Per altre informazioni, vedere Chiamata di stored procedure compilate in modo nativo da applicazioni di accesso ai dati.

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

Risorse per altre informazioni

Vedi anche

Passaggi successivi