Indagine sulle aree iniziali di OLTP in memoria

  • Articolo

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

Questo articolo è per gli sviluppatori che hanno premura di imparare in poco tempo le basi delle caratteristiche di prestazione di OLTP in memoria di Microsoft SQL Server e Database SQL di Azure.

Per OLTP in memoria, questo articolo illustra gli argomenti seguenti:

  • Spiegazione concise delle funzionalità.
  • Esempi di codice di base che implementano le funzionalità.

SQL Server e Database SQL presentano solo alcune piccole variazioni nel supporto delle tecnologie in memoria.

In ambiti informali, alcuni blogger chiamano la funzionalità OLTP in memoria Hekaton.

Vantaggi delle funzionalità in memoria

SQL Server fornisce funzionalità in memoria che possono migliorare notevolmente le prestazioni di molti sistemi di applicazione. In questa sezione vengono descritte le considerazioni più semplici.

Funzionalità per OLTP (Online Transactional Processing, elaborazione transazionale Online)

Le funzionalità OLTP sono più adatte ai sistemi che devono elaborare un numero elevato di SQL INSERT allo stesso tempo.

  • Le statistiche mostrano che il miglioramento nella velocità di elaborazione aumenta da 5 a 20 volte se si usano le funzionalità in memoria.

Sono più adatti ai sistemi che elaborano complessi calcoli in Transact-SQL.

  • Una stored procedure dedicata a calcoli elevati e complessi può essere eseguita fino a 99 volte più veloce.

È consigliabile leggere gli articoli seguenti che offrono alcune dimostrazioni di miglioramento delle prestazioni con OLTP in memoria:

Funzionalità per l'analisi operativa

L'analisi in memoria si riferisce agli SQL SELECT che aggregano dati transazionali, tipicamente con l'inclusione di una clausola GROUP BY. Il tipo di indice denominato columnstore è fondamentale per l'analisi operativa.

Ci sono due scenari principali:

  • Analisi operativa dei batch si riferisce ai processi di aggregazione che vengono eseguiti dopo gli orari di lavoro oppure su dispositivi secondari che dispongono di copie dei dati transazionali.
  • Analisi operativa in tempo reale si riferisce a processi di aggregazione che vengono eseguiti durante gli orari di lavoro e nei dispositivi hardware primari usati per i carichi di lavoro transazionali.

Questo articolo è incentrato su OLTP e non sull'analisi. Per informazioni sulle funzionalità di analisi introdotte dagli indici columnstore in SQL, vedere:

Columnstore

Una sequenza di accurati post di blog spiega in modo elegante gli indici columnstore da diverse prospettive. La maggior parte dei post descrive ulteriormente il concetto di analisi operativa, supportata da columnstore. Questi post sono stati redatti da Sunil Agarwal, un Program Manager Microsoft, nel mese di marzo 2016.

Analisi operativa in tempo reale

  1. Analisi operativa in tempo reale con tecnologia In-Memory
  2. Real-Time Operational Analytics - Overview nonclustered columnstore index (NCCI) (Analisi operativa in tempo reale: panoramica sugli indici columnstore non cluster (NCCI))
  3. Analisi operativa in tempo reale: un semplice esempio di uso di indici columnstore non cluster e cluster (NCCI) in SQL Server 2016
  4. Analisi operativa in tempo reale: operazioni DML e indici columnstore non cluster (NCCI) in SQL Server 2016
  5. Analisi operativa in tempo reale: indici columnstore non cluster (NCCI) filtrati
  6. Analisi operativa in tempo reale: opzione Ritardo di compressione per indici columnstore non cluster (NCCI)
  7. Analisi operativa in tempo reale: opzione Ritardo di compressione con indici columnstore non cluster (NCCI) e relative prestazioni
  8. Analisi operativa in tempo reale: tabelle con ottimizzazione per la memoria e indici columnstore

Deframmentare un indice columnstore

  1. Deframmentazione degli indici columnstore tramite il comando REORGANIZE
  2. Criteri di unione degli indici columnstore per REORGANIZE

Importazione bulk di dati

  1. Indici columnstore cluster: caricamento bulk
  2. Indici columnstore cluster: ottimizzazioni del caricamento dati con registrazione minima
  3. Indici columnstore cluster: ottimizzazioni del caricamento dati con importazione in blocco parallela

Funzionalità di OLTP in memoria

Funzionalità principali di OLTP in memoria.

Tabelle ottimizzate per la memoria

La parola chiave MEMORY_OPTIMIZED di T-SQL nell'istruzione CREATE TABLE indica come viene creata una tabella nella memoria attiva, invece che sul disco.

Una tabella con ottimizzazione per la memoria ha una sola rappresentazione nella memoria attiva e una copia secondaria sul disco.

  • La copia su disco è destinata al recupero di routine dopo un riavvio del server o del database. Questa dualità memoria-disco è completamente nascosta agli utenti e al codice.

Moduli compilati in modo nativo

La parola chiave NATIVE_COMPILATION di T-SQL nell'istruzione CREATE PROCEDURE indica come viene creata una stored procedure compilata in modo nativo. Le istruzioni di T-SQL vengono compilate con codice macchina quando si usa per la prima volta la procedura nativa ogni volta che il database viene avviato online. Le istruzioni di T-SQL non presentano più una lenta interpretazione di ogni istruzione.

  • Come si è visto, la durata dei risultati della compilazione nativa arriva a 1/100 della durata interpretata.

Un modulo nativo può fare riferimento solo a tabelle ottimizzate per la memoria e non a tabelle basate su disco.

Esistono tre tipi di moduli compilati in modo nativo:

Disponibilità nel database SQL di Azure

Le funzionalità OLTP in memoria e columnstore sono disponibili nel database SQL Azure. Per altre informazioni vedere Introduzione alle tecnologie in memoria (anteprima) in database SQL.

1. Assicura un livello di compatibilità >= 130

Questa sezione include una sequenza di sezioni numerate che illustrano insieme la sintassi di Transact-SQL da usare per implementare le funzionalità OLTP in memoria.

Innanzitutto, è importante che il database sia impostato su un livello di compatibilità di almeno 130. Di seguito è riportato il codice T-SQL per visualizzare il livello di compatibilità corrente del database su cui è impostato il database.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Il codice T-SQL seguente consente di aggiornare il livello, se necessario.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Elevare a SNAPSHOT

Quando una transazione include una tabella basata su disco e una tabella ottimizzata per la memoria viene detta transazione tra contenitori. In questo tipo di transazione è essenziale che la parte ottimizzata per la memoria sia operativa al livello di isolamento della transazione denominato SNAPSHOT.

Per applicare in modo affidabile questo livello per le tabelle ottimizzate per la memoria in una transazione tra contenitori, modificare l'impostazione del database con l'oggetto T-SQL seguente.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Creare un FILEGROUP ottimizzato

In Microsoft SQL Server prima di creare una tabella ottimizzata per la memoria è necessario creare prima di tutto un FILEGROUP dichiarato come CONTAINS MEMORY_OPTIMIZED_DATA. FILEGROUP è assegnato al database. Per ulteriori informazioni vedere:

Nel database SQL di Azure non è necessario e non è possibile creare un FILEGROUP.

Lo script T-SQL di esempio seguente abilita un database per OLTP in memoria e configura tutte le impostazioni consigliate. Funziona con SQL Server e il database SQL di Azure: enable-in-memory-oltp.sql.

Si noti che non tutte le funzionalità di SQL Server sono supportate per i database con un filegroup MEMORY_OPTIMIZED_DATA. Per informazioni dettagliate sulle limitazioni vedere Funzionalità di SQL Server non supportate per OLTP in memoria

4. Creare una tabella ottimizzata per la memoria

La parola chiave principale di Transact-SQL è MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Le istruzioni Transact-SQL INSERT e SELECT eseguite in una tabella ottimizzata per la memoria sono uguali a quelle per una tabella normale.

ALTER TABLE per le tabelle con ottimizzazione per la memoria

ALTER TABLE...ADD/DROP può aggiungere o rimuovere una colonna da una tabella ottimizzata per la memoria o un indice.

Pianificare indici e tabelle ottimizzate per la memoria

5. Creare una stored procedure compilata in modo nativo (procedura nativa)

La parola chiave principale è NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

La parola chiave SCHEMABINDING indica che le tabelle a cui si fa riferimento nella procedura nativa non possono essere eliminate a meno che non si elimini prima la procedura nativa. Per informazioni dettagliate, vedere Creazione di stored procedure compilate in modo nativo.

Si noti che non è necessario creare stored procedure compilate in modo nativo per accedere a una tabella con ottimizzazione per la memoria. È anche possibile fare riferimento a tabelle con ottimizzazione per la memoria da stored procedure tradizionali e batch ad hoc.

6. Eseguire la procedura nativa

Popolare la tabella con due righe di dati.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Eseguire quindi una chiamata EXECUTE alla stored procedure compilata in modo nativo.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

Di seguito è riportato l'output effettivo di PRINT:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Guida alla documentazione e passaggi successivi

Gli esempi semplici descritti in precedenza forniscono una base per l'apprendimento delle funzionalità più avanzate di OLTP in memoria. Le sezioni seguenti rappresentano una guida relativa a particolari considerazioni che potrebbe essere necessario conoscere e forniscono dettagli sulle singole funzionalità.

In che modo le funzionalità OLTP in memoria consentono un funzionamento molto più rapido?

Le sottosezioni seguenti descrivono brevemente come funzionano internamente le funzionalità OLTP in memoria per fornire prestazioni migliori.

Prestazioni più rapide delle tabelle ottimizzate per la memoria

Duplice natura: una tabella ottimizzata per la memoria a una duplice natura: una rappresentazione nella memoria attiva e una nel disco rigido. Per ogni transazione viene eseguito il commit per entrambe le rappresentazioni della tabella. Le transazioni usano la rappresentazione nella memoria attiva più veloce. Le tabelle con ottimizzazione per la memoria possono sfruttare la maggiore velocità della memoria attiva rispetto al disco. Inoltre, la maggiore flessibilità della memoria attiva semplifica l'uso di una struttura di tabella più avanzata ottimizzata per la velocità. La struttura avanzata è anche priva di pagine, quindi evita problemi di sovraccarico e contesa associati a latch e spinlock.

Nessun blocco: la tabella ottimizzata per la memoria si basa su un approccio ottimistico volto a raggiungere contemporaneamente gli obiettivi di integrità dei dati e di concorrenza e alta velocità effettiva. Durante la transazione, la tabella non inserisce blocchi in alcune versione delle righe aggiornate dei dati. Questo consente di ridurre notevolmente le contese in alcuni sistemi con volumi elevati.

Versioni di riga: al posto dei blocchi, la tabella ottimizzata per la memoria aggiunge una nuova versione di una riga aggiornata nella tabella stessa, non in tempdb. La riga originale viene mantenuta fino a dopo il commit della transazione. Durante la transazione, altri processi possono leggere la versione originale della riga.

  • Quando vengono create più versioni di una riga per una tabella basata su disco, queste versioni vengono archiviate temporaneamente in tempdb.

Meno attività di registrazione: la versione precedente e quella successiva all'aggiornamento delle righe vengono mantenute nella tabella ottimizzata per la memoria. La coppia di righe fornisce gran parte delle informazioni solitamente scritte nel file di log. Ciò consente al sistema di scrivere nel log una quantità minore di informazioni e con una frequenza inferiore. L'integrità transazionale viene comunque garantita.

Prestazioni più rapide delle procedure native

La conversione di una stored procedure interpretata regolarmente in una stored procedure compilata in modo nativo riduce notevolmente il numero di istruzioni da eseguire durante la fase di esecuzione.

Vantaggi e svantaggi delle funzionalità in memoria

Come avviene spesso in informatica, il miglioramento delle prestazioni ottenuto con le funzionalità in memoria si basa su un compromesso. I vantaggi offerti dai miglioramenti alle funzionalità valgono più del costo aggiuntivo per l'acquisto. È possibile trovare guide linea complete su vantaggi e svantaggi in:

Nel resto di questa sezione sono elencate alcune delle principali considerazioni di pianificazione, vantaggi e svantaggi.

Vantaggi e svantaggi delle tabelle ottimizzate per la memoria

Stimare la memoria: è necessario stimare la quantità di memoria attiva che verrà utilizzata dalla tabella ottimizzata per la memoria. Il computer deve avere una capacità di memoria sufficiente per ospitare una tabella ottimizzata per la memoria. Per ulteriori informazioni vedere:

Partizionare le tabelle di grandi dimensioni: un modo per soddisfare la richiesta di grandi quantità di memoria attiva consiste nel partizionare le tabelle di grandi dimensioni in parti in memoria che archiviano righe di dati attive recenti e in altre parti su disco che archiviano righe non attive legacy , ad esempio ordini di vendita già consegnati e completati. Questo partizionamento è un processo manuale di progettazione e implementazione. Vedere:

Vantaggi e svantaggi delle procedure native

  • Con una stored procedure compilata in modo nativo non è possibile accedere a una tabella basata su disco. Una procedura nativa può accedere solo alle tabelle ottimizzate per la memoria.
  • Quando viene eseguita una procedura nativa per la prima volta dopo aver riportato online il server o il database, è necessario ricompilare la procedura nativa. Ciò causa un ritardo nell'avvio dell'esecuzione della procedura nativa.

Considerazioni avanzate sulle tabelle ottimizzate per la memoria

Gliindici delle tabelle con ottimizzazione per la memoria sono per alcuni aspetti diversi dagli indici delle tabelle tradizionali su disco. Gli indici hash sono disponibili solo in tabelle ottimizzate per la memoria.

È necessario eseguire una pianificazione per garantire una quantità di memoria attiva sufficiente per la tabella ottimizzata per la memoria pianificata e i relativi indici. Vedere:

Una tabella ottimizzata per la memoria può essere dichiarata con DURABILITY = SCHEMA_ONLY:

  • Questa sintassi indica al sistema di eliminare tutti i dati dalla tabella ottimizzata per la memoria quando il database viene portato offline. Viene conservata solo la definizione della tabella .
  • Quando il database viene riportato online, la tabella ottimizzata per la memoria viene ricaricata nella memoria attiva senza alcun dato.
  • Le tabelle SCHEMA_ONLY possono essere un'alternativa superiore alle tabelle #temporary in tempdb, quando sono coinvolte molte migliaia di righe.

Le variabili di tabella possono anche essere dichiarate come ottimizzate per la memoria. Vedere:

Considerazioni avanzate sui moduli compilati in modo nativo

I tipi di moduli compilati in modo nativo disponibili da Transact-SQL sono:

Una funzione definita dall'utente (UDF) e compilata in modo nativo viene eseguita in maniera più rapida di un'UDF interpretata. Alcuni aspetti da considerare con le UDF:

  • Quando un'istruzione SELECT di T-SQL usa un'UDF, quest'ultima viene sempre chiamata una volta per ogni riga restituita.
    • Le funzioni definite dall'utente non vengono mai eseguite inline, ma vengono sempre chiamate.
    • La distinzione compilata è meno significativa dell'overhead di chiamate ripetute inerente a tutte le UDF.
    • Comunque, l'overhead delle chiamate delle UDF è spesso accettabile a livello pratico.

Per spiegazioni e dati di prova sulle prestazioni delle UDF native, vedere:

Guida alla documentazione per le tabelle ottimizzate per la memoria

Vedere gli articoli seguenti che presentano alcune considerazioni particolari relative alle tabelle ottimizzate per la memoria:

Guida alla documentazione per le procedure native

Il seguente articolo e gli articoli correlati indicati nel sommario spiegano in dettaglio le stored procedure compilate in modo nativo.

Gli articoli seguenti offrono codice per dimostrare il miglioramento delle prestazioni che è possibile raggiungere tramite OLTP in memoria: