Home > Articoli > Modalità di elaborazione query e indici columnstore

Modalità di elaborazione query e indici columnstore

In questo articolo verranno trattati i due metodi di elaborazione delle query conosciuti come Row mode execution e Batch mode execution per SQL Server 2019. Verrà inoltre descritto un meccanismo per attivare Batch mode execution su SQL Server 2017 anche quando non si possono creare indici columnstore “effettivi”.

Row mode execution

Row mode execution è un metodo di elaborazione delle query utilizzato con le tabelle tradizionali disk-based, in cui i dati vengono archiviati in formato riga. Le tabelle che utilizzano questo tradizionale formato di archiviazione vengono anche dette “rowstore table”. Quando una query viene eseguita e accede ai dati archiviati in formato riga, gli operatori previsti nel piano di esecuzione leggono ogni riga richiesta dalla query. Da ogni riga letta, SQL Server recupera quindi le colonne necessarie, specificate nell’istruzione SELECT, in un predicato di JOIN, in un predicato di filtro, ecc..

Row mode execution è stata progettata ed è molto efficiente per gli scenari OLTP, non è ottimizzata per effettuare scansioni di grandi quantità di dati, come avviene ad esempio negli scenari OLAP.

Batch mode execution

Batch mode execution è un metodo di elaborazione delle query utilizzato per elaborare più righe contemporaneamente. Questa modalità di elaborazione è basata sull’utilizzo di vettori, ogni colonna all’interno di un batch viene archiviata come vettore in un’area di memoria separata. Batch mode execution utilizza algoritmi ottimizzati per le CPU multi-core garantendo un maggiore throughput di memoria. La prima implementazione del metodo di elaborazione batch era strettamente integrata e ottimizzata con il formato di archiviazione columnstore, si tratta tuttavia di due funzionalità indipendenti.

Quando una query viene elaborata in modalità batch e accede ai dati negli indici columnstore, gli operatori previsti nel piano di esecuzione leggono più righe contemporaneamente nei vettori di colonna. SQL Server legge solo le colonne richieste dalla query, specificate nella SELECT, in un predicato di JOIN o in un predicato di filtro. Batch mode execution è particolarmente efficiente in scenari OLAP, in cui vengono lette e aggregate grandi quantità di dati.

Batch mode execution è stata estesa nel corso del tempo e a partire da SQL Server 2019 e in Azure SQL Database non richiede la presenza di indici columnstore per essere utilizzata. Si tratta della nuova modalità di esecuzione batch su rowstore che consente di elaborare più righe contemporaneamente nei carichi di lavoro analitici senza richiedere indici columnstore.

Batch mode execution su rowstore, in SQL Server 2019 e Azure SQL Database, attiva la modalità batch per tutti gli operatori del piano di esecuzione che supportano questa funzionalità.

Alcuni di voi si starano chiedendo se è possibile abilitare la modalità di esecuzione batch su rowstore anche in SQL Server 2017 senza aggiungere indici columnstore… quello che state pensando è possibile con un trucco vediamo come..

Consideriamo la tabella dbo.bigTransactionHistory che è stata aggiunta al database di esempio AdventureWorks2017 grazie allo script di Adam Machanic pubblicato nell’articolo Thinking Big (Adventure). La tabella contiene le transazioni di magazzino dei prodotti gestiti.

Consideriamo la query seguente che estrae la giacenza dinamica dei prodotti a magazzino e restituisce il running-total calcolato sulla colonna quantità ordinato per identificativo prodotto e identificativo transazione. Il data set restituito è ordinato per identificativo prodotto e identificativo transazione.

SELECT
  ProductID
  ,TransactionID
  ,Quantity
  ,SUM(Quantity) OVER(PARTITION BY ProductID ORDER BY TransactionID
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
  dbo.bigTransactionHistory
ORDER BY
  ProductID, TransactionID;

Il piano di esecuzione generato per la query utilizza il parallelismo, l’indice IX_ProductId_TransactionDate viene letto interamente (Index Scan) per l’assenza del predicato di filtro. L’operatore Sort ordina i dati per TransactionID, ProductID e Quantity.

L’operazione di ordinamento rappresenta l’operazione più onerosa nel piano di esecuzione ed è anche segnalata con il simbolo di warning (Sort Warning) perché utilizza il database di sistema tempdb (spill on tempdb) per l’operazione di ordinamento. I dati vengono successivamente partizionati per ProductID (Segment) e aggregati (Stream Aggregate) per calcolare il running-total sulla colonna Quantity.

Tutti gli operatori lavorano in modalità Row leggono ogni riga richiesta dalla query per recuperare le colonne necessarie. Il piano di esecuzione elaborato in modalità Row è illustrato nella figura seguente.

Nell’ambiente utilizzato per i test, la query elaborata in modalità row viene completata (senza restituzione del data-set al chiamante) in 40 secondi.

La query che abbiamo preso come esempio avrebbe un enorme beneficio se fosse eseguita in modalità batch ma non si dispone di SQL Server 2019 e non si desidera aggiungere un indice columnstore alla tabella dbo.bigTransactionHistory principalmente interessata da istruzioni INSERT, UPDATE e DELETE quindi da un carico di lavoro OLTP.

Il trucco che possiamo utilizzare è quello che prevede la creazione di indice columnstore filtrato nel cui predicato di filtro specificare una condizione contraddittoria come per esempio la richiesta di avere contemporaneamente la colonna TransactionID uguale a -1 e -2! L’indice columnstore non aggiungerà quindi nessun overhead ai comandi DML rivolti alla tabella ma attiverà la modalità di elaborazione batch!

Il seguente codice T-SQL implementa la creazione dell’indice columnstore filtrato nella tabella dbo.bigTransactionHistory.

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCX_bigTransactionHistory_TransactionID
  ON dbo.bigTransactionHistory(TransactionID)
  WHERE (TransactionID = -1 AND TransactionID = -2);

La presenza dell’indice columnstore (anche se “vuoto”) ha abilitato la modalità di esecuzione batch, il piano di esecuzione continua ad essere caratterizzato dal parallelismo ma ora gli operatori presenti, ad eccezione dell’operatore Index Seek, lavorano in modalità batch. L’operatore Sort continua ad avere il costo maggiore ma ora fornisce i dati all’operatore Window Aggregate che ha preso il posto degli operatori Segment, Sequence Project, Window Spool e Stream Aggregate come illustrato nella figura seguente.

Nell’ambiente utilizzato per i test, la query elaborata in modalità batch viene completata (senza restituzione del data-set al chiamante) in circa 20 secondi. Tempo dimezzato rispetto alla precedente elaborazione in modalità row.

Le performance della query possono essere ulteriormente migliorate creando il seguente indice per le colonne ProductID, TransactionID con la colonna Quantity nella clausola INCLUDE.

CREATE NONCLUSTERED INDEX IX_bigTransactionHistory_ProductID_TransactionID
  ON dbo.bigTransactionHistory(ProductID, TransactionID)
 INCLUDE(Quantity);

Conclusioni

La modalità di esecuzione batch in SQL Server 2019 e in Azure SQL Database non richiede la presenza di indici columnstore per essere utilizzata. In SQL Server 2017 anche in presenza di carichi di lavoro OLTP può essere attivata creando un indice columnstore filtrato nel cui predicato di filtro viene specificata una condizione contraddittoria come per esempio (TransactionID = -1 AND TransactionID = -2).

L’indice columnstore creato in questo modo non avrà costi di manutenzione ma permetterà di attivare la batch mode execution per gli operatori che supportano questo tipo di elaborazione, con notevoli vantaggi!

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

SQL Start 2022 – Agenda pubblicata!

L’agenda completa di SQL Start 2022 è disponibile qui! L’evento si terrà venerdì 10 giugno 2022. Nel …