Ottimizzare le prestazioni usando tecnologie in memoria in Istanza gestita di SQL di Azure

  • Articolo

Si applica a:Azure SQL Managed Instance

Le tecnologie in memoria consentono di migliorare le prestazioni dell'applicazione e di ridurre potenzialmente i costi dell'istanza gestita di SQL. OLTP in memoria è disponibile nel livello di servizio Business Critical di Istanza gestita di SQL di Azure.

Quando usare tecnologie in memoria

Usando le tecnologie in memoria, è possibile ottenere miglioramenti delle prestazioni con diversi carichi di lavoro:

  • Transazionale (elaborazione transazionale online (OLTP) in cui la maggior parte delle richieste legge o aggiorna un set di dati più piccolo, ad esempio le operazioni di creazione/lettura/aggiornamento/eliminazione (CRUD).
  • Analisi (elaborazione analitica online (OLAP) in cui la maggior parte delle query dispone di calcoli complessi per la creazione di report e anche processi pianificati regolarmente che eseguono operazioni di caricamento (o caricamento bulk) e/o scrivono modifiche ai dati nelle tabelle esistenti. Spesso, i carichi di lavoro OLAP vengono aggiornati periodicamente dai carichi di lavoro OLTP.
  • Misto (elaborazione ibrida transazione/analitica o HTAP), in cui vengono eseguite query OLTP e OLAP sullo stesso set di dati.

Le tecnologie in memoria possono migliorare le prestazioni di questi carichi di lavoro mantenendo in memoria i dati che devono essere elaborati, tramite la compilazione nativa delle query o un'elaborazione avanzata come l'elaborazione batch e le istruzioni SIMD disponibili sull'hardware sottostante.

Panoramica

Istanza gestita di SQL di Azure supporta le tecnologie in memoria seguenti:

  • OLTP in memoria aumenta il numero di transazioni al secondo e riduce la latenza per l'elaborazione delle transazioni. Gli scenari che traggono vantaggio da OLTP in memoria sono: elaborazione delle transazioni a velocità effettiva elevata, ad esempio trading e giochi, inserimento di dati da eventi o dispositivi IoT, memorizzazione nella cache, caricamento dei dati e scenari di variabili di tabella e tabelle temporanee.
  • Gli indici columnstore cluster riducono fino a 10 volte il footprint della memoria e migliorano le prestazioni delle query di reporting e analisi. È possibile usare gli indici con tabelle dei fatti nei data mart per inserire più dati nel database e migliorare le prestazioni. Gli indici possono anche essere usati con i dati cronologici nel database operativo per archiviare ed essere in grado di eseguire una query su una quantità di dati 10 volte superiore.
  • Gli indici columnstore non cluster per HTAP consentono di ottenere in tempo reale informazioni approfondite sull'azienda eseguendo una query direttamente sul database operativo, senza la necessità di eseguire un processo ETL dispendioso e attendere che il data warehouse venga popolato. Gli indici columnstore non cluster consentono l'esecuzione rapida delle query di analisi nei database OLTP, riducendo l'impatto sul carico di lavoro operativo.
  • Gli indici columnstore cluster ottimizzati per la memoria per HTAP consentono di elaborare le transazioni e al contempo di eseguire le query di analisi sugli stessi dati in tempi estremamente rapidi.

Gli indici columnstore e OLTP in memoria sono stati introdotti rispettivamente in SQL Server nel 2012 e nel 2014. database SQL di Azure, Istanza gestita di SQL di Azure e SQL Server condividono la stessa implementazione di tecnologie in memoria.

Nota

Per un'esercitazione dettagliata per illustrare i vantaggi delle prestazioni della tecnologia OLTP in memoria, usando il AdventureWorksLT database di esempio e ostress.exe, vedere Esempio in memoria in Istanza gestita di SQL di Azure.

Vantaggi della tecnologia in memoria

Grazie all'elaborazione di query e transazioni più efficiente, anche le tecnologie in memoria consentono di ridurre i costi. Una volta nel livello di servizio Business Critical di Istanza gestita di SQL di Azure, in genere non è necessario aggiornare l'istanza gestita di SQL per ottenere miglioramenti delle prestazioni. In alcuni casi, si potrebbe anche essere in grado di ridurre il piano tariffario, pur continuando a visualizzare miglioramenti delle prestazioni con le tecnologie in memoria.

Questo articolo descrive gli aspetti degli indici OLTP e columnstore in memoria specifici di Istanza gestita di SQL di Azure e include anche esempi:

  • Viene analizzato l'impatto di queste tecnologie sulla memoria e i limiti sulle dimensioni dei dati.
  • Verrà illustrato come gestire lo spostamento dei database che sfruttano queste tecnologie tra i diversi piani tariffari.
  • Verranno visualizzati due esempi che illustrano l'uso di OLTP in memoria, nonché indici columnstore.

Per altre informazioni su OLTP in memoria in SQL Server, vedere:

OLTP in memoria

La tecnologia OLTP in memoria fornisce operazioni di accesso ai dati estremamente veloci, mantenendo tutti i dati in memoria. Usa inoltre indici specializzati, la compilazione nativa delle query e un accesso ai dati privo di latch per migliorare le prestazioni del carico di lavoro OLTP. Esistono due modi per organizzare i dati OLTP in memoria:

  • Il formato rowstore ottimizzato per la memoria, in cui ogni riga è un oggetto di memoria distinto. Si tratta di un formato OLTP classico in memoria ottimizzato per carichi di lavoro OLTP ad alte prestazioni. Esistono due tipi di tabelle ottimizzate per la memoria che possono essere usate nel formato rowstore ottimizzato per la memoria:

    • Le tabelle durevoli (SCHEMA_AND_DATA), in cui le righe inserite nella memoria vengono mantenute dopo il riavvio del server. Questo tipo di tabelle si comporta come una tabella rowstore tradizionale con i vantaggi aggiuntivi delle ottimizzazioni in memoria.
    • Tabelle nondurable (SCHEMA_ONLY) in cui le righe non vengono mantenute dopo il riavvio. Questo tipo di tabella è progettato per i dati temporanei (ad esempio, la sostituzione di tabelle temporanee) o per le tabelle in cui è necessario caricare rapidamente i dati prima di spostarli in una tabella persistente (le cosiddette tabelle di staging).

  • Il formato columnstore ottimizzato per la memoria, in cui dati sono organizzati in un formato a colonne. Questa struttura è progettata per gli scenari HTAP in cui è necessario eseguire query di analisi sulla stessa struttura dei dati in cui è in esecuzione il carico di lavoro OLTP.

Nota

La tecnologia OLTP in memoria è progettata per le strutture di dati che possono risiedere completamente in memoria. Poiché i dati in memoria non possono essere scaricati su disco, assicurarsi di usare un'istanza gestita di SQL con memoria sufficiente. Per altre informazioni, vedere Dimensioni dei dati e limite di archiviazione per OLTP in memoria.

Dimensioni dei dati e limite di archiviazione per OLTP in memoria

OLTP in memoria include tabelle ottimizzate per la memoria, usate per l'archiviazione dei dati utente. Queste tabelle devono rientrare nella memoria. Questa idea viene definita archiviazione OLTP in memoria.

Il livello di servizio Business Critical include una determinata quantità di memoria OLTP in memoria massima, determinata dal numero di vCore.

Gli elementi seguenti vengono conteggiati per il limite di archiviazione OLTP in memoria:

  • Righe di dati utente attive nelle tabelle ottimizzate per la memoria e variabili di tabella. Le versioni precedenti delle righe non vengono conteggiati per il limite.
  • Indici nelle tabelle ottimizzate per la memoria.
  • Costi operativi delle operazioni ALTER TABLE.

Se si raggiunge il limite, si riceve un errore di superamento della quota e non sarà più possibile inserire o aggiornare dati. Per risolvere il problema, eliminare i dati o aumentare il piano tariffario del database o del pool.

Per informazioni dettagliate sul monitoraggio dell'utilizzo dell'archiviazione OLTP in memoria e sulla configurazione degli avvisi quando si raggiunge quasi il limite, vedere Monitorare l'archiviazione in memoria.

Modificare la configurazione hardware o il numero di vCore

Il downgrade della configurazione hardware o del numero di vCore può influire negativamente sull'istanza gestita di SQL.

I dati nelle tabelle ottimizzate per la memoria devono rientrare nel limite di archiviazione OLTP in memoria per la configurazione hardware e il numero di vCore. Se si tenta di ridurre le prestazioni a un'impostazione che non dispone di spazio di archiviazione OLTP in memoria sufficiente, l'operazione ha esito negativo.

Determinare se gli oggetti in memoria esistono

È possibile comprendere a livello di codice se un determinato database nell'istanza gestita di SQL supporta OLTP in memoria. È possibile eseguire la query Transact-SQL seguente:

SELECT DatabasePropertyEx(DB_NAME(), 'IsXTPSupported');

Se la query restituisce 1, OLTP in memoria è supportato in questo database.

Le query seguenti identificano tutti gli oggetti usando la tecnologia in memoria:

SELECT * FROM sys.tables WHERE is_memory_optimized=1
SELECT * FROM sys.table_types WHERE is_memory_optimized=1
SELECT * FROM sys.sql_modules WHERE uses_native_compilation=1

Columnstore in memoria

La tecnologia columnstore in memoria consente di archiviare ed eseguire query su una grande quantità di dati nelle tabelle. La tecnologia columnstore usa un formato di archiviazione dei dati basato su colonne e l'elaborazione batch delle query allo scopo di ottenere prestazioni delle query fino a 10 volte superiori nei carichi di lavoro OLAP rispetto all'archiviazione tradizionale orientata alle righe. È anche possibile migliorare fino a 10 volte la compressione dei dati rispetto alla dimensione dei dati non compressi.

Esistono due tipi di modelli di columnstore che è possibile usare per organizzare i dati:

  • Columnstore cluster, in cui tutti i dati nella tabella sono organizzati in un formato a colonne. In questo modello, tutte le righe nella tabella vengono inserite in formato a colonne, che esegue la compressione dei dati e consente di eseguire rapidamente query analitiche e report sulla tabella. A seconda della natura dei dati, è possibile ottenere una riduzione delle dimensioni da 10 a 100 volte. Il modello columnstore cluster consente anche l'inserimento rapido di grandi quantità di dati (caricamento bulk) perché grandi batch di dati maggiori di 100.000 righe vengono compressi prima che vengano archiviati su disco. Questo modello è una scelta appropriata per i classici scenari di data warehouse.
  • Columnstore non cluster, in cui i dati vengono archiviati in una tabella rowstore tradizionale ed è presente un indice in formato columnstore usato per le query di analisi. Questo modello consente l'elaborazione analitica e transazionale ibrida (HTAP), che offre la possibilità di eseguire analisi in tempo reale ad alte prestazioni su carichi di lavoro transazionali. Le query OLTP vengono eseguite sulla tabella rowstore ottimizzata per l'accesso a un set di righe limitato, mentre le query OLAP vengono eseguite sull'indice columnstore, che rappresenta la scelta migliore per le analisi. Query Optimizer sceglie in modo dinamico il formato rowstore o columnstore in base alla query. Gli indici columnstore non cluster non riducono le dimensioni dei dati perché il set di dati originale viene mantenuto nella tabella rowstore originale senza alcuna modifica. Tuttavia, le dimensioni dell'indice columnstore aggiuntivo dovrebbero essere significativamente inferiori rispetto all'indice ad albero B equivalente.

Nota

La tecnologia columnstore in memoria mantiene in memoria solo i dati necessari per l'elaborazione, mentre i dati che non possono essere contenuti nella memoria sono archiviati su disco. Di conseguenza, la quantità di dati nelle strutture columnstore in memoria può superare la quantità di memoria disponibile.

Dimensioni dei dati e archiviazione per gli indici columnstore

Gli indici columnstore non devono essere contenuti nella memoria. Pertanto, l'unico limite per le dimensioni degli indici è la dimensione massima complessiva del database. Per altre informazioni, vedere Istanza gestita di SQL di Azure limiti delle risorse. Istanza gestita di SQL di Azure supporta gli indici columnstore in tutti i livelli.

Quando si usano gli indici columnstore cluster, viene impiegata la compressione a colonne per l'archiviazione delle tabelle di base. Ciò può ridurre notevolmente il footprint di archiviazione dei dati utente, ovvero è possibile inserire più dati nel database. Usando la compressione a colonne dell'archivio, è possibile inserire una quantità ancora maggiore di dati. La quantità di compressione che è possibile ottenere dipende dalla natura dei dati, ma generalmente si aggira intorno a 10 volte (10X) la compressione tradizionale.

Ad esempio, se si dispone di un database con dimensioni massime di 1 terabyte (TB) e si raggiunge una compressione 10X tramite columnstore, nel database è possibile inserire un totale di 10 TB di dati utente.

Quando si usano indici columnstore non cluster, la tabella di base è ancora archiviata nel formato rowstore tradizionale. Di conseguenza, i risparmi di archiviazione non sono significativi come per gli indici columnstore cluster. Tuttavia, se si sostituiscono molti indici tradizionali non cluster con un singolo indice columnstore, è comunque possibile visualizzare un risparmio complessivo nel footprint di archiviazione per la tabella.

Contenuto correlato