In-Memory panoramica e scenari di utilizzo di OLTP

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure

OLTP in memoria è la tecnologia di primo livello disponibile in SQL Server e per Database SQL ottimizzare le prestazioni dell'elaborazione delle transazioni, dell'inserimento dei dati, del caricamento dei dati e degli 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 giusto per l'applicazione. L'articolo si conclude con un esempio che OLTP in memoria mostra gli oggetti, il riferimento a una demo perf e i riferimenti alle risorse che è possibile usare per i passaggi successivi.

Questo articolo illustra la tecnologia OLTP in memoria in e SQL Server Database SQL. Per altre informazioni specifiche sui dati in memoria in Azure SQL, vedere Ottimizzare le prestazioni usando tecnologie in memoria in database SQL di Azure e Azure SQL Istanza gestita e Blog: OLTP in memoria in database SQL di Azure.

OLTP in memoria Panoramica

OLTP in memoria può offrire un miglioramento delle prestazioni per i carichi di lavoro più significativi. 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 dell'elaborazione delle transazioni rendendo più efficiente l'accesso ai dati e l'esecuzione delle transazioni e rimuovendo i problemi di blocco e latch tra le transazioni in esecuzione simultanea. 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.

Ora, solo perché i dati sono in memoria non significa che si perde in caso di 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: durante il 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 disponibili quando il database torna online. Inoltre, funziona OLTP in memoria con SQL Servertutte le funzionalità di disponibilità elevata e ripristino di emergenza di , ad esempio Gruppi di disponibilità Always On, istanze del cluster di failover Always On (SQL Server), backup/ripristino e così via.

Per sfruttare OLTP in memoria il database, usare uno o più dei tipi di oggetti seguenti:

  • Le tabelle con ottimizzazione per la memoria vengono usate per archiviare i dati utente. È possibile dichiarare una tabella ottimizzata per la memoria al momento della creazione.
  • Le tabelle 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. In questo modo si evita di utilizzare risorse di I/O di log per i casi in cui la durabilità non è un problema.
  • I tipi 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.
  • I moduli 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.

Più avanti in questo articolo sono disponibili esempi di questi oggetti.

OLTP in memoria è incorporato in SQL Server e Database SQL. E 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. Alla fine di questo articolo è disponibile uno script Transact-SQL che mostra un esempio per ognuno di questi tipi di oggetti.

Scenari di utilizzo per OLTP in memoria

OLTP in memoria non è un pulsante magic go-fast e non è adatto a tutti i carichi di lavoro. Ad esempio, le tabelle ottimizzate per la memoria non rallentano l'utilizzo della CPU se la maggior parte delle query esegue aggregazioni su grandi intervalli di dati. 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 OLTP in memoria.

Elaborazione di transazioni con velocità effettiva elevata e bassa latenza

Questo è lo scenario principale per cui è stato creato OLTP in memoria: supporta grandi volumi di transazioni, con una bassa latenza coerente 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 è quello 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, cio' 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. Maggiore è la logica che è possibile eseguire il push verso il basso nelle stored procedure nel database, maggiore è il vantaggio che si può ottenere da 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 si vuole 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 è una buona idea per l'inserimento di grandi volumi di dati da molte origini diverse contemporaneamente. È spesso utile inserire dati in un database rispetto ad SQL Server altre destinazioni, SQL Server perché rende veloce l'esecuzione di 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'inserimento è costituito principalmente da inserimenti (anziché aggiornamenti) OLTP in memoria e il footprint di archiviazione dei dati è un problema,

  • Usare 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>
  • 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.

SQL Server Il repository degli esempi contiene un'applicazione smart grid che usa una tabella temporale ottimizzata per la memoria, un tipo di tabella ottimizzata per la memoria e un stored procedure compilato in modo nativo per velocizzare l'inserimento dei dati, OLTP in memoria gestendo al tempo stesso il footprint di archiviazione dei dati del sensore:

Memorizzazione nella cache e stato della sessione

La OLTP in memoria tecnologia rende SQL molto interessante per la gestione dello stato della sessione (ad esempio, per un'applicazione ASP.NET) e per la memorizzazione nella cache.

ASP.NET stato della sessione è un caso d'uso molto corretto per OLTP in memoria. Con SQL Server, un cliente ha quasi raggiunto 1,2 milioni di richieste al secondo. Nel frattempo, hanno iniziato a usare per OLTP in memoria le esigenze di memorizzazione nella cache di tutte le applicazioni di livello intermedio nell'azienda. Dettagli: Come viene utilizzato bwin per SQL Server 2016 (13.x) OLTP in memoria 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.

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 SQL Server predefinito: aspnet-session-state

Cliente case study

  • bwin ha aumentato la velocità effettiva ASP.NET stato sessione e ha implementato un sistema di memorizzazione nella cache di livello intermedio a livello aziendale, OLTP in memoria con in SQL Server 2016 (13.x): Come viene utilizzato bwin SQL Server 2016 (13.x) OLTP in memoriaper ottenere prestazioni e scalabilità senza precedenti.

Sostituzione dell'oggetto tempdb

Sfruttare 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 un'introduzione, vedere: Miglioramento delle prestazioni della tabella temporanea e della variabile di tabella con l'ottimizzazione della memoria.

Case study dei clienti

  • Un cliente è stato in grado di migliorare le prestazioni del 40%, semplicemente sostituendo le TVP tradizionali con TVP ottimizzate per la memoria: inserimento di dati IoT OLTP in memoria ad alta velocità tramite in Azure
  • SentryOne ha migliorato significativamente la capacità di inserimento dei dati con quasi zero latenza nella soluzione di monitoraggio scambiando le tabelle in tempdb OLTP in memoria con le tabelle come parte dei miglioramenti della scalabilità aziendale: il provider di soluzioni interrompe il limite massimo delle prestazioni con l'innovazione del monitoraggio dei dati .

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.

Considerazioni sull'implementazione

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.

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 il ridimensionamento dall'ottimizzazione della memoria.

Script di esempio

Prima di iniziare a usare OLTP in memoria, è necessario creare un filegroup MEMORY_OPTIMIZED_DATA filegroup. È 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 OLTP in memoria gli oggetti che è possibile creare nel database.

Per iniziare, configurare 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à diversa:

-- 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 un'istanza di compilata in stored procedure. 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

Vedere anche

Passaggi successivi