Condividi tramite

Indagine sulle aree iniziali di OLTP basato su memoria

  • Articolo

Si applica a:SQL Serverdatabase SQL di AzureIstanza 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.

I sistemi che elaborano complessi calcoli in Transact-SQL sono eccellenti candidati.

  • Una stored procedure dedicata a calcoli pesanti può essere eseguita fino a 99 volte più velocemente.

È 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 in tempo reale, supportata dalla tecnologia 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 utilizzo di un indice columnstore cluster noncluster (NCCI) in SQL Server 2016
  4. Analisi operativa in tempo reale: operazioni DML e gli indici columnstore non clusterizzato (NCCI) in SQL Server 2016
  5. Analisi operativa in tempo reale: indici filtrati columnstore non clusterizzati (NCCI)
  6. Analisi operativa in tempo reale: opzione Ritardo di compressione per indici columnstore non clusterizzato (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 il comando REORGANIZE

Importazione in blocco di dati

  1. Indici Columnstore Clusterizzati: Caricamento in Blocco
  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

Diamo un'occhiata alle principali caratteristiche 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 soffrono più di 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 dettagli, vedere Ottimizzazione delle prestazioni con tecnologie in memoria nel 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 a 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 cross-container, modificate l'impostazione del database eseguendo il seguente T-SQL.

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. Il FILEGROUP è assegnato al tuo 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 procedura memorizzata 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 nativamente per accedere a una tabella con memoria ottimizzata. È anche possibile fare riferimento a tabelle ottimizzate per la memoria da procedure memorizzate 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' );

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

Come le tabelle ottimizzate per la memoria migliorano le prestazioni.

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 senza pagine, quindi evita il sovraccarico e la 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 lock su alcuna 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 la conferma 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.

Come le procedure native eseguono più velocemente

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 linee guida complete sui compromessi 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:

Partiziona la tua grande tabella: Un modo per soddisfare la richiesta di grandi quantità di memoria attiva consiste nel partizionare la tua grande tabella in parti in memoria che archiviano righe di dati calde recenti e in altre parti su disco che archiviano righe fredde legacy, come ordini di vendita già spediti e completati. Questo partizionamento è un processo manuale di progettazione e implementazione. Vedere:

Vantaggi e svantaggi delle procedure native

  • Una procedura memorizzata compilata nativamente non può 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 prima che il processo nativo inizi a funzionare.

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 pianificare per garantire una quantità di memoria attiva sufficiente per la tabella pianificata ottimizzata per la memoria 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 in linea, ma vengono sempre chiamate.
    • La distinzione compilata è meno significativa dell'overhead di chiamate ripetute inerente a tutte le UDF.
    • Tuttavia, l'overhead delle chiamate 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: