Panoramica di OLTP in memoria e scenari di utilizzo

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

OLTP in memoria è la principale tecnologia disponibile in SQL Server e nel database SQL per ottimizzare le prestazioni di elaborazione delle transazioni, l'inserimento di dati, il caricamento di dati e gli 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.

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, OLTP in memoria migliora le prestazioni di elaborazione delle transazioni rendendo più efficiente l'accesso ai dati e l'esecuzione delle transazioni e rimuovendo la contesa latch/blocco tra le transazioni attualmente in esecuzione. OLTP in memoria 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.

Ma solo perché i dati si trovano in memoria non significa che si perdono 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 rimangono presenti quando il database torna online. OLTP in memoria funziona inoltre con tutte le funzionalità di disponibilità elevata e ripristino di emergenza di SQL Server, come gruppi di disponibilità, istanze del cluster di failore, backup e ripristino e così via.

Per utilizzare OLTP in memoria nel database, è possibile usare uno o più dei seguenti tipi di oggetti:

  • 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 che viene dichiarata con DURABILITY=SCHEMA_ONLY, vale a dire che le modifiche apportate a queste tabelle non comportano operazioni di I/O. Ciò evita l'utilizzo di risorse per le operazioni di I/O sui 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 (TVP) e come set di risultati intermedi nelle stored procedure. I tipi di tabella ottimizzati per la memoria possono essere usati invece 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.

OLTP in memoria è incorporato in SQL Server e nel database SQL. Poiché il comportamento di questi oggetti è simile a quello delle relative controparti tradizionali, spesso è possibile ottenere un miglioramento delle 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. Si veda lo script Transact-SQL di esempio per ognuno di questi tipi di oggetti più avanti in questo articolo.

Scenari di utilizzo per OLTP in memoria

OLTP in memoria non è un pulsante magico per ottenere maggiore velocità e non è appropriato per tutti i carichi di lavoro. Ad esempio, le tabelle ottimizzate per la memoria non riducono l'utilizzo della CPU se la maggior parte delle query comporta aggregazioni per grandi intervalli di dati. Per questo scenario saranno utili gli indici columnstore.

Attenzione

Problema noto: per i database con tabelle ottimizzate per la memoria, l'esecuzione di un backup del log delle transazioni senza ripristino e l'esecuzione successiva di un ripristino del log delle transazioni con ripristino può comportare un processo di ripristino del database che non risponde. Questo problema può influire anche sulla funzionalità di log shipping. Per risolvere questo problema, è possibile riavviare l'istanza di SQL Server prima di avviare il processo di ripristino.

Di seguito è riportato un elenco di scenari e modelli di applicazione in cui i clienti hanno ottenuto risultati positivi con OLTP in memoria.

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 è rappresentato dal caso in cui sono presenti file di grandi dimensioni, ognuno distribuito su diversi nodi del cluster, e la posizione di ogni partizione di ogni file viene catalogata in una tabella ottimizzata per la memoria.

Considerazioni sull'implementazione

Usa le tabelle ottimizzate per la memoria per le tabelle delle transazioni principali, ad esempio le tabelle con 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. Usa il report di analisi delle prestazioni delle transazioni per identificare gli oggetti di cui eseguire la migrazione.
  2. Usa gli advisor ottimizzazione per la memoria e 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. Ed è spesso utile inserire dati in un database di SQL Server rispetto ad altre destinazioni, perché SQL SQL Server velocizza l'esecuzione di query sui dati consentendo di ottenere informazioni 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:

  • Usa un processo per ripartire regolarmente il carico di lavoro dei dati in batch in una tabella basata su disco con un indice columnstore clustermediante un processo che esegue l'istruzione INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; oppure
  • 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 memoria rende il motore di database in SQL Server o nei database SQL di Azure una piattaforma interessante per mantenere lo stato della sessione (ad esempio, per un'applicazione ASP.NET) e per la memorizzazione nella cache.

Lo stato della sessione ASP.NET è un caso di utilizzo di successo per OLTP in memoria. 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: Modo in cui bwin usa OLTP in memoria di SQL Server 2016 (13.x) 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 nel 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.

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

Case study dei clienti

Sostituzione dell'oggetto tempdb

Usa le tabelle non durevoli e i tipi di tabella ottimizzata per la memoria per sostituire le strutture tempdb tradizionali, 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 iniziare: Miglioramento delle prestazioni della tabella temporanea e della variabile di tabella con l'ottimizzazione della memoria.

Case study dei clienti

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 possono ottenere ulteriori vantaggi di scalabilità dall'ottimizzazione per la 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 gli oggetti di OLTP in memoria che è possibile creare nel database.

Per iniziare, configura il database per OLTP in memoria.

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

Contenuto correlato