Condividi tramite

Ottimizzare le prestazioni tramite le tecnologie in memoria nel database SQL di Azure

  • Articolo

Si applica a: Database SQL di Azure

Le tecnologie in memoria consentono di migliorare le prestazioni dell'applicazione e ridurre potenzialmente i costi del database.

Quando usare tecnologie in memoria

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

  • Transazionale (elaborazione transazionale online o OLTP), in cui la maggior parte delle richieste esegue la lettura o l'aggiornamento di set di dati più piccoli, ad esempio, operazioni (CRUD) crea/leggi/aggiorna/elimina.
  • 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

Il database SQL di Azure supporta delle seguenti tecnologie in memoria:

  • OLTP in memoria aumenta il numero di transazioni al secondo e riduce la latenza per l'elaborazione delle transazioni. Gli scenari che beneficiano dell'OLTP in memoria sono: elaborazione transazionale ad alta velocità di elaborazione, come l'inserimento di dati commerciali e da videogiochi, da eventi o dispositivi IoT, il caching, il caricamento di dati, le tabelle temporanee e gli scenari con variabili di tabella.
  • 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. Il database SQL di Azure, Istanza gestita di SQL di Azure e SQL Server condividono la stessa implementazione delle tecnologie in memoria.

Nota

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

Vantaggi delle tecnologie in memoria

Grazie a una più efficiente elaborazione delle query e delle transazioni, le tecnologie in memoria aiutano a ridurre i costi. In genere non è necessario aggiornare il piano tariffario del database per migliorare le prestazioni. In alcuni casi infatti le tecnologie in memoria consentono di ridurre il piano tariffario e di osservare al contempo miglioramenti delle prestazioni.

Sfruttando la tecnologia OLTP in memoria Quorum Business Solutions è riuscita a raddoppiare il carico di lavoro migliorando i valori DTU del 70%. Per altre informazioni, vedere OLTP in memoria nel database SQL di Azure.

Nota

Le tecnologie di OLTP in memoria sono disponibili nei livelli Premium (DTU) e Business Critical (vCore) del database SQL di Azure. Il livello di servizio Hyperscale supporta un subset di oggetti OLTP in memoria. Per altre informazioni, vedi Limitazioni Hyperscale.

Gli indici columnstore sono disponibili in tutti i livelli di servizio ad eccezione del livello Basic e del livello Standard quando l'obiettivo del servizio è inferiore a S3. Per maggiori informazioni, vedere Modifica dei livelli di servizio dei database che contengono indici columnstore.

Questo articolo descrive gli aspetti di OLTP in memoria e degli indici columnstore specifici del database SQL di Azure e include alcuni esempi che consentono di vedere:

  • L'impatto di queste tecnologie sulla memoria e i limiti sulle dimensioni dei dati.
  • Come gestire lo spostamento dei database che sfruttano queste tecnologie tra i diversi piani tariffari.
  • Uso illustrativo di OLTP in memoria, nonché di indici columnstore.

Per altre informazioni sulle tecnologie 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. Questo è un classico formato OLTP 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.
    • La tabelle non durevoli (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 dei dati che possono risiedere completamente in memoria. Poiché non è possibile eseguire l'offload su disco dei dati in memoria, assicurarsi di usare un database che disponga di memoria sufficiente. Per altre informazioni, vedere Limite su dimensioni dei dati e archiviazione per OLTP in memoria.

Limite su dimensioni dei dati e archiviazione per OLTP in memoria

OLTP in memoria include tabelle ottimizzate per la memoria che vengono usate per archiviare i dati utente. Queste tabelle devono rientrare nella memoria. Ogni obiettivo di servizio ha una quota di memoria o un limite per le tabelle ottimizzate per la memoria, note come archiviazione OLTP in memoria.

Ogni piano tariffario relativo a database singoli e pool elastici supportati include una certa quantità di spazio di archiviazione OLTP in memoria.

Gli elementi seguenti rientrano nel limite di archiviazione di OLTP in memoria:

  • Righe di dati utente attive nelle tabelle ottimizzate per la memoria e variabili di tabella. Le versioni precedenti della riga non vengono conteggiate nel 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 l’obiettivo di servizio del database o del pool elastico.

Per dettagli sul monitoraggio dell'uso dello spazio di archiviazione OLTP in memoria e sulla configurazione degli avvisi al raggiungimento del limite, vedere Monitorare l'archiviazione in memoria.

Informazioni sui pool elastici

Con i pool elastici, lo spazio di archiviazione OLTP in memoria è condiviso tra tutti i database nel pool. Ne consegue che l'utilizzo in un database può potenzialmente influire sugli altri database. Esistono due metodi per la risoluzione di questo problema:

  • Configurare per i database un valore Max eDTU o Max vCore inferiore al numero di eDTU o vCore configurati per l'intero pool. Questo limite massimo consente anche l'utilizzo dell'archiviazione OLTP in memoria in qualsiasi database nel pool in modo proporzionale.
  • Configurare Min eDTU o Min vCore su un valore maggiore di 0. In questo modo si garantisce che ogni database nel pool abbia a disposizione la quantità di spazio di archiviazione OLTP in memoria corrispondente al valore Min eDTU o Min vCore configurato.

Modificare i livelli di servizio dei database che usano le tecnologie OLTP in memoria

OLTP in memoria non è supportato nel piano per Utilizzo generico o nei piani Standard o Basic del database SQL di Azure. Non è pertanto possibile scalare un database che contiene oggetti OLTP in memoria di uno di questi livelli. Se si vuole ridimensionare un database in uno di questi livelli di servizio, rimuovere tutte le tabelle e i tipi di tabella ottimizzati per la memoria, nonché tutti i moduli T-SQL compilati in modo nativo o convertirli in oggetti basati su disco e normali moduli T-SQL.

Quando si riduce un database Business Critical o Premium, i dati nelle tabelle ottimizzate per la memoria devono rientrare nell'archiviazione OLTP in memoria disponibile nell'obiettivo del servizio di destinazione del database o del pool elastico. Se si tenta di ridurre il database o il pool elastico o di spostare il database in un pool elastico e l’obiettivo di servizio di destinazione non dispone di sufficiente spazio di archiviazione OLTP in memoria, l'operazione avrà esito negativo.

Determinare se gli oggetti OLTP in memoria esistono

Esiste un modo programmatico per scoprire se un determinato database 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 nel database.

Le query seguenti identificano tutti gli oggetti che devono essere rimossi prima di ridurre un database al livello di servizio Hyperscale, per utilizzo generico, Standard o Basic:

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 indici 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 tipo di indice, 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 con indici columnstore a cluster consente inoltre l'inserimento rapido di grandi quantità di dati (caricamento bulk), perché i batch di dati di grandi dimensioni con più di 100.000 righe vengono compressi prima di essere archiviati su disco. Questo tipo di indice è 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 aggiuntivo in formato columnstore usato per le query di analisi. Questo tipo di indice consente l'elaborazione analitica e transazionale ibrida (HTAP), che offre la possibilità di eseguire analisi in tempo reale rapide 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, poiché il set di dati originale viene mantenuto nella tabella rowstore originale senza apportare modifiche. Tuttavia, le dimensioni dell'indice columnstore aggiuntivo sono 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. Pertanto, la quantità di dati nelle strutture con columnstore può superare la quantità di memoria disponibile.

Dimensioni dei dati e archiviazione per gli indici columnstore

Gli indici columnstore non devono essere contenuti interamente nella memoria. L'unico limite alla dimensione degli indici è quindi la dimensione complessiva massima del database, descritta negli articoli Modello di acquisto basato su DTU e Modello di acquisto basato su vCore.

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 aumentare ulteriormente il rapporto di compressione. 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. Pertanto, il risparmio di archiviazione non è paragonabile a quello ottenuto con gli indici columnstore cluster. Tuttavia, se si sostituisce numerosi indici non in cluster tradizionali con un indice columnstore singolo, è sempre possibile riscontrare un risparmio complessivo nel footprint della memoria per la tabella. È anche possibile usare la compressione dei dati rowstore per la tabella di base.

Modifica dei livelli di servizio dei database che contengono indici columnstore

Se si usa il modello di acquisto DTU e il database contiene indici columnstore, l'applicazione potrebbe smettere di funzionare se si ridimensiona il database al di sotto dell'obiettivo del servizio S3. Gli indici columnstore sono supportati solo nei livelli di servizio Hyperscale, Business Critical e Premium, nonché nel livello di servizio Standard se si usa S3 e versioni successive. Gli indici Columnstore non sono supportati nel livello di servizio Basic. Quando si scala il database a un piano o un livello di servizio non supportato, l'indice columnstore non è più disponibile. Il sistema mantiene l'indice quando si eseguono istruzioni DML, ma non lo usa mai. Se in seguito si torna a un obiettivo o un livello di servizio supportato, l'indice columnstore torna subito disponibile all'uso.

Se si dispone di un indice columnstore clustered, l'intera tabella diventa non disponibile se il database viene ridimensionato a un livello di servizio o a un obiettivo di servizio non supportato. Eliminare tutti gli indici columnstore clustered, sostituendoli con indici cluster rowstore o heap, prima dell'operazione di ridimensionamento.

Contenuto correlato