SQL Server 2008

Prestazioni delle query nel data warehouse

Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

 

Panoramica:

• Ottimizzazione delle query con join a stella
• Parallelismo tra tabelle partizionate
• Compressione ROW e PAGE
• Viste indicizzate con partizionamento allineato

SQL Server 2008 offre funzionalità di data warehouse relazionale molto più potenti, ma come utilizzare questa tecnologia per creare un data warehouse funzionante per il supporto decisionale su miliardi di righe? E come

è possibile capire quali tra queste funzionalità possono garantire le migliori prestazioni per report e query di supporto decisionale o ancora quali miglioramenti delle prestazioni si possono prevedere da questa nuova versione di SQL Server®?

Con l'avvicinarsi della data di rilascio le domande si moltiplicano. Ci auguriamo che questa analisi approfondita sulle principali funzionalità di data warehousing relative alle prestazioni in SQL Server 2008 si riveli utile.

Progettazione logica del database: modellazione dimensionale

Le applicazioni line-of-business transazionali seguono in genere uno schema di database normalizzato. La progettazione logica dello schema del database per i data warehouse relazionali invece tralascia la normalizzazione seguendo piuttosto l'approccio della modellazione dimensionale, reso popolare dal libro di Ralph Kimball e Margy Ross intitolato "The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling".

Chi già conosce l'ambiente del data warehousing avrà sicuramente familiarità con i modelli di schema comuni per i data warehouse relazionali (schemi a stella e a fiocco di neve). La modellazione dimensionale distingue le tabelle delle dimensioni dalle tabelle dei fatti. Le tabelle delle dimensioni contengono i dati principali (quali prodotti, clienti, negozi o paesi) mentre quelle dei fatti contengono i dati transazionali (come vendite, ordini, acquisti o resi).

Le tabelle di fatti e dimensioni sono collegate tramite relazioni di tipo chiave primaria (PK)/chiave esterna (FK). Molti data warehouse non si avvalgono dei vincoli FK come strumento per mantenere minimi i requisiti di archiviazione e risparmiare l'overhead di archiviazione degli indici sottostanti per mantenere ridotti i costi di gestione della tabella dei fatti. Le tabelle delle dimensioni di un data warehouse sono generalmente piccole (contengono da migliaia a diversi milioni di righe). Per contro, la tabella dei fatti raggiunge grandi dimensioni contenendo da centinaia di milioni a miliardi di righe. Pertanto la progettazione logica deve prestare attenzione ai requisiti di archiviazione della tabella dei fatti.

L'aspetto delle dimensioni influisce inoltre sulla scelta della chiave della tabella delle dimensioni utilizzata per la gestione della relazione tra quest'ultima e la tabella dei fatti. Le chiavi composite, basate sulla chiave business della dimensione (ossia l'identificatore dell'entità rappresentata dalla dimensione) coprono in genere diverse colonne. Ciò rappresenta un problema per la chiave esterna corrispondente presente nella tabella dei fatti, poiché la chiave composita su più colonne viene ripetuta per ogni riga della tabella dei fatti.

Per ovviare al problema, si ricorre all'uso di piccole chiavi surrogate, da implementare nelle relazioni tra le due tabelle. La chiave surrogata è una colonna di identità contenente dati integer che funge da chiave primaria artificiale della tabella delle dimensioni. Facendo riferimento alla piccola chiave surrogata, la tabella dei fatti riduce significativamente i requisiti di archiviazione nelle tabelle dei fatti più grandi. Nella Figura 1 viene illustrato lo schema di un data warehouse con modellazione dimensionale che utilizza tabelle di fatti e dimensioni e chiavi surrogate.

Figura 1** Esempio di schema a stella con una tabella dei fatti e due tabelle delle dimensioni **(Fare clic sull'immagine per ingrandirla)

Lo schema a fiocco di neve distribuisce una o più dimensioni su più livelli (ad esempio cliente, paese e regione per la dimensione cliente) e così facendo normalizza le dimensioni più consistenti, soggette a un'eccessiva ridondanza di dati. I livelli sono rappresentati da tabelle separate che conferiscono allo schema la forma di un fiocco di neve. Per contro, lo schema a stella non distribuisce le dimensioni sulle tabelle, ma assume la forma di una stella in cui le tabelle delle dimensioni vengono raggruppate attorno a una tabella dei fatti centrale.

Negli schemi con modellazione dimensionale, siano essi a stella o a fiocco di neve, le query del supporto decisionale seguono un modello preciso: la query seleziona diverse misure di interesse dalla tabella dei fatti, unisce le righe dei fatti a una o più dimensioni utilizzando le chiavi surrogate, colloca i predicati del filtro sulle colonne business delle tabelle delle dimensioni, esegue il raggruppamento in base a una o più colonne business e aggrega le misure recuperate dalla tabella dei fatti in un determinato periodo di tempo. Questo modello è raffigurato di seguito ed è talvolta indicato con il termine query join a stella:

select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime 
on Fact.OrderDateKey = TimeKey
     join DimProduct 
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

Progettazione fisica

Molte query SQL del data warehouse relazionale seguono la struttura della query join a stella. Tuttavia, le query di supporto decisionale spesso variano nel tempo poiché chi prende decisioni tenta di comprendere meglio i dati aziendali seguendo nuove modalità. Per questo motivo i carichi di lavoro per i data warehouse tendono ad avere un'alta percentuale di query ad hoc, cosa che rende particolarmente difficile la progettazione fisica delle query di supporto decisionale e di uno schema di data warehouse con modellazione dimensionale.

Con SQL Server, chi progetta i data warehouse parte generalmente da un modello o progetto fisico che viene gradualmente messo a punto con l'evolversi dei carichi di lavoro. Si consiglia di adottare questo progetto per l'ambiente data warehouse specifico e variarlo di conseguenza. Nel farlo, è importante tenere a mente le procedure consigliate seguite nella progettazione fisica dei database, quali l'impatto sulle prestazioni causato dalla manutenzione degli indici per gli aggiornamenti e i requisiti di archiviazione degli indici.

La tabella dei fatti

Il progetto anticipa la tipica forma della query a stella e crea gli indici sulla tabella dei fatti. L'indice cluster della tabella dei fatti utilizza come chiavi indice diverse colonne di chiavi surrogate della dimensione (colonne chiavi esterne). Le colonne utilizzate di frequente devono essere incluse nell'elenco delle chiavi indice. Potrebbe essere necessario verificare che ciò fornisca un buon percorso di accesso per le query più frequenti nel carico di lavoro specifico.

Inoltre, il progetto crea nella tabella dei fatti un indice non cluster di una sola colonna per ciascuna colonna di chiavi esterne surrogate della dimensione. Ciò offre un percorso di accesso altamente efficace per le query molto selettive in una delle relative dimensioni.

L'obiettivo dell'indice cluster è garantire prestazioni elevate per la maggioranza delle query del carico di lavoro. La serie di indici non cluster mira alle query che recuperano le misure dalla tabella dei fatti per un prodotto o un cliente specifico. Questi indici impediscono, ad esempio, di dover esaminare la tabella dei fatti per recuperare i dati di vendita per un solo cliente.

Tabelle delle dimensioni

Quando si applica il progetto alle tabelle delle dimensioni, è necessario creare gli indici per ogni tabella delle dimensioni. Essi includono un indice con vincolo di chiave primaria non cluster nella colonna delle chiavi surrogate della dimensione e un indice cluster nelle colonne delle chiavi business dell'entità della dimensione. Nella tabelle di grandi dimensioni, occorre anche considerare l'aggiunta di indici non cluster nelle colonne usate di frequente nei predicati altamente selettivi.

L'indice cluster facilita le operazioni di estrazione, trasformazione e caricamento (Extract, Transform, and Load, ETL) nella finestra della manutenzione del data warehouse, processo spesso critico rispetto al tempo. Ad esempio, nel caso di dimensioni che cambiano lentamente, mentre le righe esistenti vengono aggiornate quelle non ancora presenti nella dimensione vengono aggiunte alla tabella delle dimensioni. Per riuscire, questo modello di accesso necessita di buone funzioni di ricerca e aggiornamento per la tabella delle dimensioni da eseguire in contemporanea con le operazioni ETL.

Il progetto delineato serve come punto di inizio per i progetti fisici dei data warehouse relazionali creati con SQL Server. Partendo da questa configurazione tipica del data warehouse relazionale, possiamo adesso esplorare le nuove funzionalità chiave di SQL Server 2008.

Ottimizzazione delle query join a stella

In un data warehouse relazionale con modellazione dimensionale, l'elaborazione della tabella dei fatti rappresenta il costo principale nell'esecuzione di una query join a stella. Infatti, anche le query più selettive recuperano molte più righe dalla tabella dei fatti rispetto a quella delle dimensioni. Pertanto, l'uso del percorso di accesso migliore alla tabella dei fatti è essenziale per garantire ottime prestazioni delle query.

In SQL Server, Query Optimizer seleziona automaticamente tra le alternative disponibili il percorso di accesso con il costo stimato più basso. Nel contesto del data warehouse, l'obiettivo principale è accertarsi che Query Optimizer consideri alternative valide come percorsi di accesso per il piano di esecuzione della query join a stella. Query Optimizer di SQL Server prevede diverse funzionalità per fornire automaticamente piani di esecuzione delle query join a stella con prestazioni elevate.

Come descritto nella Figura 2, le query join a stella possono essere divise in tre classi diverse. Tali categorie aiutano il motore di SQL Server a meglio identificare il piano adatto per le query in questione. Il concetto principale sul quale si basa SQL Server è la selettività delle query a fronte della tabella dei fatti. Meno sono le righe della tabella dei fatti impiegate, più selettiva è la query. La percentuale di righe recuperate dalla tabella dei fatti consente di immaginare queste classi di query. Queste percentuali rappresentano i valori di distribuzioni standard, ma non sono da interpretare come parametri fissi nella definizione dei percorsi di accesso.

Figura 2** Spettro di selettività per le query join a stella **(Fare clic sull'immagine per ingrandirla)

La prima classe riguarda le query altamente selettive, che elaborano fino al 10% delle righe della tabella dei fatti. La seconda classe, che ha una selettività media, consiste in query che elaborano tra il 10% e il 75% delle righe della tabella dei fatti. Le query della terza classe, con selettività bassa, elaborano oltre il 75% delle righe memorizzate nella tabella dei fatti. Le caselle della figura evidenziano, all'interno di ogni classe di selettività, le scelte del piano di esecuzione delle query di base.

Scelta del piano in base alla selettività

Poiché le query a stella ad alta selettività non recuperano più del 10% delle righe della tabella dei fatti, esse hanno un accesso casuale alla tabella dei fatti. I piani di query per questa classe si basano soprattutto su join di loop nidificati in combinazione con ricerche di indice (non cluster) e segnalibri nella tabella dei fatti. Poiché questi piani eseguono un volume di I/O casuale nella tabella dei dati, vengono superati da operazioni di I/O sequenziali man mano che si recuperano porzioni più grandi della tabella dei fatti. Pertanto sono necessari più piani di query poiché il numero di righe della tabella dei fatti aumenta oltre una certa soglia.

Le query a stella a media selettività elaborano una porzione significativa di righe della tabella dei fatti e per accedervi prediligono ricerche con hash join o scansioni di intervalli. SQL Server si avvale dei filtri bitmap per migliorare le prestazioni degli hash join.

Nella Figura 3 viene illustrata il modo in cui SQL Server utilizza i filtri bitmap per migliorare le prestazioni del join durante l'esecuzione di query join a stella. Viene mostrato un piano di query a fronte di due tabelle di dimensioni, Prodotto e Tempo, unite alla tabella dei fatti da chiavi surrogate. La query utilizza i predicati del filtro, come le clausole WHERE, su tutte e due le tabelle delle dimensioni in modo che una sola riga risulti idonea per ogni dimensione, come indicato dalle tabelline rosse accanto agli operatori join.

Figura 3** Piano di query join a stella con elaborazione di riduzione del join **(Fare clic sull'immagine per ingrandirla)

L'implementazione join per ogni join è un hash join, che consente a SQL Server di trasformare le informazioni sulle righe risultanti nelle tabelle delle dimensioni in quelle che chiamiamo informazioni sulla riduzione del join per entrambe le tabelle delle dimensioni. Le caselle verdi rappresentano le strutture dati di tali informazioni. Una volta popolati i dati con le tabelle delle dimensioni sottostanti, SQL Server trasferisce automaticamente le strutture dati durante l'esecuzione delle query all'operatore che elabora la tabella dei fatti, ad esempio la ricerca su tabella. L'operatore utilizza le informazioni sulle righe della tabella delle dimensioni per eliminare le righe della tabella dei fatti che non risultano idonee per le condizioni di join a fronte delle dimensioni.

SQL Server cancella tali righe in una fase iniziale dell'elaborazione, dopo averle recuperate dalla tabella dei fatti per consentire un maggiore risparmio della CPU e, potenzialmente, di I/O su disco dato che le righe cancellate non devono essere elaborate con altri operatori del piano di query. SQL Server utilizza una rappresentazione bitmap per implementare con efficacia le strutture dati con informazioni sulla riduzione del join al momento dell'esecuzione della query.

Pipiline di ottimizzazione del join a stella

Il processo di ottimizzazione impiega l'euristica standard dell'ottimizzazione delle query join per generare un set iniziale di alternative di piani di esecuzione query. Per generare più alternative del piano query vengono richiamate delle estensioni speciali.

In caso di data warehousing, l'estensione rileva lo schema a stella e a fiocco di neve e i modelli della query a stella, quindi valuta la selettività della query a fronte della tabella dei fatti. Se la forma e lo schema della query corrispondono ai modelli, SQL Server aggiunge automaticamente altri piani di query allo spazio dei piani, che viene elaborato dall'ottimizzazione basata sul costo per la scelta del piano più efficace.

Al momento dell'esecuzione della query, SQL Server monitora inoltre la selettività della riduzione del join in fase di runtime. Se il livello di selettività cambia, SQL Server riorganizza dinamicamente le strutture dati delle informazioni sulla riduzione del join, in modo da applicare per prima quella più selettiva.

Euristica del join a stella

Molti progetti fisici di data warehouse pur seguendo lo schema a stella non specificano le relazioni tra la tabella dei fatti e la tabella delle dimensioni, come nel caso sopra citato dei vincoli della chiave esterna. In assenza di vincoli specificati della chiave esterna, SQL Server deve ricorrere all'euristica per rilevare i modelli della query con schema a stella. L'euristica applicata è la seguente:

1. La più grande tra le tabelle del join n-ario è considerata la tabella dei fatti. Esistono restrizioni aggiuntive sulle dimensioni minime della tabella dei fatti. Ad esempio, se neanche la tabella più grande supera un dato valore, il join n-ario non è considerato a stella.
2. Tutte le condizioni join dei join binari in una query join a stella devono essere predicati di uguaglianza di una colonna singola. I join devono essere interni. Sebbene sembri un limite, in realtà la maggioranza dei join tra la tabella dei fatti e quella delle dimensioni sulla chiave surrogata negli schemi a stella tradizionali rientra in questa descrizione. I join che presentano condizioni e modelli più complessi rispetto a quelli citati sono esclusi dal join a stella. Ad esempio, un join a cinque vie può diventare un join a stella a tre vie (a cui se ne aggiungeranno due più avanti) se due dei join hanno predicati join più complessi.

Queste sono regole euristiche. Le circostanze effettive che portano l'euristica a selezionare una tabella delle dimensioni come tabella dei fatti sono in realtà poche. È la scelta del piano a essere influenzata non la sua correttezza. I join binari coinvolti in un join a stella vengono ordinati in ordine di selettività discendente. In questo contesto la selettività del join è definita come il rapporto tra la cardinalità in ingresso della tabella dei fatti e la cardinalità del risultato del join (la selettività del join indica di quanto una dimensione specifica riduce la cardinalità della tabella dei fatti). Come regola generale, è bene considerare dapprima i join con selettività più alta.

Il processore delle query in SQL Server applica automaticamente l'ottimizzazione alle query seguendo il modello join a stella e le condizioni sopra citate quando i piani di query risultanti presentano previsioni di costo interessanti. Pertanto, non occorre modificare l'applicazione affinché sfrutti questo significativo miglioramento. Si noti però che alcune ottimizzazioni del join a stella quali la riduzione join sono disponibili solo in SQL Server Enterprise Edition.

Risultati delle prestazioni dei join a stella

Come parte dello sviluppo dell'ottimizzazione del join a stella di SQL Server 2008 abbiamo eseguito una serie di studi sulle prestazioni basati su carichi di lavoro effettivi e benchmark. I risultati di tre di questi carichi di lavoro sono particolarmente interessanti.

Data warehouse organizzazione vendite Microsoft Questo carico di lavoro registra le prestazioni di un data warehouse utilizzato internamente per il supporto decisionale dell'organizzazione vendite di Microsoft. Abbiamo rilevato una snapshot di esempio del database con dimensioni pari a 750 GB (indici compresi). Le query di questo carico di lavoro sono molto complesse poiché molte presentano più di 10 join.

Cliente della vendita al dettaglio Questa serie di esperimenti si basa sul data warehouse di un cliente nel settore della vendita al dettaglio (con un negozio convenzionale e la presenza online). Il cliente è caratterizzato da uno schema a fiocco di neve con modellazione dimensionale e query join a stella tradizionali. Abbiamo utilizzato 100 GB di dati non elaborati per popolare una snapshot del warehouse per i nostri esperimenti.

Carico di lavoro del supporto decisionale Questa serie di esperimenti indaga sulle prestazioni di un carico di lavoro del supporto decisionale di un database con modellazione dimensionale da 100 GB. Nella Figura 4 vengono illustrati i risultati dei tre carichi di lavoro. Viene tracciata la media geometrica normalizzata del tempo di risposta alla query di tutte le query del carico di lavoro. Questa metrica è un buon indicatore delle prestazioni di query attese durante l'esecuzione di una query arbitraria dal carico di lavoro. Le barre della figura confrontano la linea base (1,0) delle prestazioni in mancanza dell'ottimizzazione del join a stella con le prestazioni ottimizzate per il join a stella. Tutte le esecuzioni sono state eseguite con SQL Server 2008.

Figura 4** Miglioramenti delle prestazioni con l'ottimizzazione del join a stella **(Fare clic sull'immagine per ingrandirla)

Come mostrato dalla figura, tutti i carichi di lavoro migliorano significativamente, dal 12% al 30%. Con il variare del singolo utilizzo, prevediamo una crescita dei carichi di lavoro del supporto decisionale rispetto al motore SQL Server del 15%-20%, in base all'estensione delle ottimizzazioni specifiche dei join a stella aggiunte a SQL Server 2008.

Parallelismo tra tabelle partizionate

Per velocizzare l'elaborazione dati in grandi data warehouse, gli amministratori di database spesso eseguono la partizione di grandi tabelle dei fatti in base alla data. I dati vengono così suddivisi in filegroup diversi, per ridurre la quantità di informazioni su cui eseguire le ricerche durante l'elaborazione delle righe in un intervallo di dati specifico e per sfruttare le prestazioni concorrenti del sistema disco sottostante quando i filegroup vengono distribuiti su un ampio numero di dischi fisici.

SQL Server 2005 ha introdotto la possibilità di eseguire la partizione di una relazione di grandi dimensioni in parti logiche più piccole per migliorare la gestione e l'amministrazione delle tabelle più grandi. Un altro vantaggio che ha apportato è stato il miglioramento notevole dell'elaborazione di query, specialmente per le applicazioni di supporto decisionale più grandi.

Purtroppo però, alcuni clienti di SQL Server 2005 hanno notato dei problemi di prestazioni associati alle query su queste tabelle partizionate, soprattutto nei computer multiprocessori con memoria condivisa parallela. L'elaborazione di query parallele su tabelle partizionate in SQL Server 2005 presenta delle istanze in cui solo un sottoinsieme dei thread disponibili viene assegnato all'esecuzione della query.

Basti pensare a un computer a 64 core in cui le query potrebbero utilizzare fino a 64 thread in parallelo e una query interessa due partizioni. In SQL Server 2005, vengono ricevuti solo 2 dei 64 thread con un utilizzo di soli 2/64 (3,1%) della potenza della CPU del computer. Sono stati riferiti casi in cui le prestazioni in tabelle partizionate sono risultata di 10 volte inferiori rispetto all'esecuzione della stessa query nello stesso computer su una versione non partizionata della medesima tabella dei fatti.

Si noti che SQL Server 2005 è stato ottimizzato per le query che interessano una sola partizione. In tal caso il processore di query assegna tutti i thread disponibili all'operazione di ricerca. Dato che questa ottimizzazione speciale causa un notevole miglioramento nell'esecuzione di query a partizione singola nei computer multicore, è logico aspettarsi lo stesso comportamento dalle query che interessano partizioni multiple.

La nuova funzione PTP (Partitioned Table Parallelism) di SQL Server 2008 migliora le prestazioni delle query in presenza di partizioni utilizzando la capacità di elaborazione dell'hardware esistente indipendentemente dal numero delle partizioni interessate dalla query e dalle dimensioni delle singole partizioni. In un ambiente data warehouse comune con una tabella dei fatti partizionata, gli utenti possono assistere a un significativo miglioramento nelle query eseguite su piani paralleli, soprattutto se il numero dei core del processore disponibili è superiore al numero delle partizioni coinvolte dalla query. Questa nuova funzionalità è subito disponibile e non necessita alcuna configurazione o modifica.

Supponiamo di avere una tabella dei fatti che rappresenta i dati di vendita organizzati per data su quattro partizioni. Lo schema della Figura 5 permette di visualizzare l'esempio. Piuttosto che un unico indice cluster per l'intero intervallo di date, come nel caso di assenza di partizioni, esiste generalmente un indice cluster per la colonna della data per le singole partizioni della tabella dei fatti. Supponiamo adesso che una query Q riepiloghi le vendite degli ultimi sette giorni. Poiché nuovi dati di vendita continuano a essere inseriti nella tabella dei fatti attraverso l'ultima partizione (P4), la query interesserà partizioni diverse, a seconda di quando viene eseguita, come indicato nella prima riga dello schema, in cui la query Q1 interessa un'unica partizione, mentre la query Q2 interessa due partizioni poiché i dati rilevanti al momento dell'esecuzione sono distribuiti sulle partizioni P3 e P4.

Figura 5** La nuova funzionalità PTP **(Fare clic sull'immagine per ingrandirla)

Si supponga di avere otto thread a disposizione. L'esecuzione di Q1 e Q2 in SQL Server 2005 potrebbe generare un comportamento inatteso. L'ottimizzazione di SQL Server 2005 opera come segue: se al momento della compilazione è noto che solo una partizione sarà coinvolta dalla query, questa sarà trattata come una singola tabella non partizionata e verrà generato un piano per accedere alla tabella con tutti i thread disponibili.

Ne risulta che Q1 che interessa la singola partizione (P3) produrrà un piano elaborato da otto thread (non raffigurato). Nel caso di Q2 invece, che interessa due partizioni, l'utilità di esecuzione assegna un singolo thread a ogni partizione anche se ne esistono di altri disponibili. Pertanto, Q2 utilizza solo una frazione della potenza CPU disponibile e, molto probabilmente, avrà prestazioni decisamente ridotte rispetto a Q1.

L'esecuzione di Q1 e Q2 in SQL Server 2008 invece comporta un utilizzo migliore dell'hardware disponibile, registra prestazioni migliori e segue un comportamento più prevedibile. Nel caso di Q1, l'utilità di esecuzione assegna nuovamente tutti gli otto thread disponibili per elaborare i dati in P2 (non raffigurato). Q2, invece, produce un piano parallelo in cui l'utilità di esecuzione assegna tutti i thread disponibili a P3 e P4 in stile round robin, producendo l'effetto illustrato nella riga in basso del diagramma, in cui ciascuna delle due partizioni riceve quattro thread. La CPU rimane completamente utilizzata e le prestazioni di Q1 e Q2 sono le stesse.

L'assegnazione round robin dei thread consente alle query di ottenere prestazioni migliori con l'aggiunta di più core del processore rispetto al numero di partizioni utilizzate dalla query. Esistono tuttavia casi in cui l'assegnazione dei thread alle partizioni non è così diretta.

Altri esempi del miglioramento delle prestazioni da SQL Server 2005 a SQL Server 2008 nello scenario delle tabelle partizionate su computer con processore multicore sono illustrati nella Figura 6. Questo grafico evidenzia le prestazioni dell'analisi per le tabelle partizionate. Nel caso specifico, condotto su un sistema a 64 core e 256 GB di RAM, è stata partizionata una sola tabella da 121 GB in 11 partizioni da 11 GB l'una. Per la serie di test rappresentati nella figura è stata utilizzata un'organizzazione file heap con avvii del buffer a caldo e a freddo. Tutte le query eseguono ricerche semplici sui dati.

Figura 6** Prestazioni delle ricerche di SQL Server con la nuova funzionalità PTP attivata **(Fare clic sull'immagine per ingrandirla)

L'asse y indica il tempo di risposta (sec) e l'asse x il grado di parallelismo (DOP, Degree Of Parallelism), cioè il numero di thread assegnati alla query. In entrambi i casi (avvii a caldo e a freddo) il tempo di risposta continua a diminuire fino a quando il valore DOP raggiunge 22. A questo punto, il sistema I/O diviene saturato per gli avvii a freddo. Ciò è dovuto principalmente al fatto che la query dell'esempio è associata a I/O. Per più carichi di lavoro associati alla CPU questa limitazione potrebbe non esistere o verificarsi solo nel caso di livelli DOP superiori.

La curva che raffigura l'avvio a caldo, tuttavia, continua a mostrare cali nel tempo di risposta proporzionali all'aumento del livello DOP. In SQL Server 2005, entrambe le curve iniziano a stabilizzarsi attorno al valore DOP 11, poiché il numero di thread per partizione si limita a 1 nel caso di più partizioni.

In pratica, la riduzione dei tempi di risposta per numeri crescenti di DOP non è mai lineare. Piuttosto, il comportamento previsto ricorda una funzione a gradini, che riflette query in attesa nelle sezioni più lente. L'aggiunta di uno o più thread a una ricerca non migliorerà quindi il tempo di completamento della query, a meno che tutte le altre ricerche abbiano ricevuto un thread aggiuntivo per completare rapidamente l'operazione.

Sono stati eseguiti altri esperimenti per testare il comportamento del nuovo PTP per altre configurazioni file e hardware. In questa circostanza, abbiamo osservato un comportamento simile in termini di crescita della velocità effettiva proporzionale all'aumento di DOP su più thread/partizioni.

Infine, la nuova funzionalità PTP di SQL Server 2008 migliora la leggibilità dei piani di query e semplifica la comprensione dell'esecuzione di alcuni carichi di lavoro. Ad esempio, in quanto parte della funzione PTP, il modo in cui i piani parallelo e seriale vengono rappresentati in Showplan XML è stato migliorato insieme alle informazioni di partizione fornite nei piani di esecuzione in fase di compilazione e runtime.

Compressione dati

Con il diffondersi della Business Intelligence, le aziende riversano sempre più dati da esaminare nei propri data warehouse. Ne risulta una crescita esponenziale dei dati da gestire. Nel 1995, la prima indagine Winter Corporation sulle dimensioni dei database ha dichiarato che il sistema più grande del mondo conteneva 1 TB di dati. Dieci anni dopo il primato si aggirava su dimensioni 100 volte superiori. Il fatto sconcertante è che le dimensioni dei data warehouse si triplicano ogni due anni. Ciò crea nuove sfide in termini di gestione di grandi quantità di dati e di offerta di livelli di prestazioni accettabili nelle query su data warehouse che sono generalmente complesse, coinvolgono molti join e aggregati e accedono a grandi quantità di dati. Inoltre, spesso molte query del carico di lavoro sono associate a operazioni I/O.

La compressione dei dati nativi contribuisce a risolvere questo problema. SQL Server 2005 SP2 ha introdotto un nuovo formato di archiviazione a lunghezza variabile, detto vardecimal per dati numerici e decimali, in grado di ridurre notevolmente le dimensioni dei database. Grazie alle ottimizzazioni dello spazio, le prestazioni delle query associate a operazioni I/O vengono migliorate in due modi. Innanzitutto il numero di pagine da leggere è inferiore. Inoltre, i dati sono conservati nel pool di buffer, migliorando la permanenza presunta delle pagine (ossia le probabilità che la pagina richiesta si trovi nel buffer). Le ottimizzazioni dello spazio ottenute tramite la compressione dei dati implicano un costo in termini di CPU dovuto alla compressione e alla decompressione dei dati.

SQL Server 2008 si basa sul formato di archiviazione vardecimal e fornisce due tipi di compressione: ROW e PAGE. La compressione ROW estende il formato di archiviazione vardecimal memorizzando tutti i tipi di dati a lunghezza fissa in un formato di archiviazione a lunghezza variabile.

Alcuni esempi di tipi di dati a lunghezza fissa sono i dati integer, char e float. Anche se SQL Server memorizza questi tipi di dati in un formato a lunghezza variabile, la semantica rimane immutata (l'applicazione considera il tipo di dati a lunghezza fissa). Ciò significa che è possibile ottenere i vantaggi della compressione dati senza eseguire alcuna modifica nelle applicazioni.

La compressione PAGE riduce la ridondanza di dati nelle colonne di una o più righe di una data pagina. Utilizza un'implementazione proprietaria dell'algoritmo LZ78 (Lempel-Ziv), archiviando i dati ridondanti solo una volta nella pagina e facendovi riferimento da più colonne. Quando si utilizza la compressione PAGE, viene inclusa anche la compressione ROW.

Le compressioni ROW e PAGE possono essere attivate su una tabella o un indice oppure su una o più partizioni per le tabelle e gli indici partizionati. In questo modo viene garantita una flessibilità completa nella scelta di tabelle, indici e partizioni per la compressione, consentendo di raggiungere il giusto equilibrio tra ottimizzazione dello spazio e impatto sulla CPU. Nella Figura 7 viene illustrata una tabella vendite partizionata in diversi modi con indici allineati.

Figura 7** Tabella partizionata con diverse impostazioni di compressione **(Fare clic sull'immagine per ingrandirla)

Ogni partizione rappresenta un trimestre e l'ultimo corrisponde a ottobre-dicembre. Si supponga che le due partizioni siano utilizzate raramente, la terza partizione sia moderatamente attiva e l'ultima partizione sia la più attiva. In questo caso, una configurazione possibile prevede l'abilitazione della compressione PAGE sulle prime due partizioni per ottenere la massima ottimizzazione dello spazio con il minimo impatto sulle prestazioni del carico di lavoro, della compressione ROW sulla terza partizione e di nessuna compressione nell'ultima partizione.

Per abilitare la compressione, sia online che offline, è possibile utilizzare istruzioni Alter Table o Alter Index DDL (Data Definition Language). SQL Server fornisce inoltre una stored procedure per stimare l'ottimizzazione dello spazio. L'ottimizzazione dello spazio ottenuta dipende dalla distribuzione dei dati e dallo schema dell'oggetto compresso.

Sulla base dei risultati ottenuti dai test con molti database di clienti, traspare che la maggior parte dei clienti potrebbe ridurre le dimensioni dei propri database del 50%-65% e migliorare notevolmente le prestazioni delle query associate a operazioni I/O. Tuttavia, la valutazione dell'impatto sulle prestazioni delle query associate alla CPU non è semplice da eseguire e dipende dalla complessità della query. In SQL Server, la decompressione implica un costo solo durante l'accesso all'indice o alle tabelle. Se il costo CPU relativo degli operatori di ricerca è basso rispetto al costo CPU globale della query, come spesso accade nel data warehouse, l'impatto sull'utilizzo della CPU dovrebbe essere inferiore del 20%-30%.

Viste indicizzate con partizionamento allineato

In SQL Server 2008, le viste indicizzate con partizionamento allineato consentono di creare e gestire meglio aggregati di riepilogo nel data warehouse relazionale e di utilizzarli in scenari in cui non potevano essere finora impiegati. Le prestazioni delle query ne risultano migliorate. In uno scenario standard, la tabella dei fatti è partizionata per data. Per velocizzare le query, le viste indicizzate (o aggregati di riepilogo) vengono definite in questa tabella. Quando si passa a una nuova partizione della tabella, le partizioni corrispondenti delle viste indicizzate con partizionamento allineato definite nella tabella partizionata seguono automaticamente.

Si tratta di un notevole miglioramento rispetto a SQL Server 2005, in cui per passare da una partizione all'altra occorre eliminare le viste indicizzate definite in una tabella partizionata, prima di utilizzare ALTER TABLE SWITCH. La funzionalità delle viste indicizzate con partizionamento allineato di SQL Server 2008 offre i vantaggi delle viste indicizzate su grandi tabelle partizionate evitando i costi della ricostruzione degli aggregati su un'intera tabella partizionata. Tali vantaggi includono la manutenzione automatica degli aggregati e l'abbinamento delle viste indicizzate.

Escalation blocchi a livello di partizione

SQL Server supporta il partizionamento per intervalli che consente di partizionare i dati per una migliore gestibilità dei dati o raggrupparli in base agli schemi d'uso. Ad esempio, è possibile eseguire la partizione dei dati di vendita ogni mese o trimestre oppure associare una partizione al relativo filegroup e il filegroup a un gruppo di file. I vantaggi che ne derivano sono principalmente due: innanzitutto, è possibile eseguire il backup e il ripristino di una partizione come unità indipendente. In secondo luogo, è possibile associare un filegroup a un sottosistema I/O lento o veloce, in base al modello d'uso o al carico della query.

Un aspetto interessante è rappresentato dal modello di accesso dei dati. Le query e le operazioni DML potrebbero richiedere solo l'accesso o la manipolazione di un subset di partizioni. Se ad esempio si stanno analizzando i dati di vendita del 2004, sarà necessario accedere solo alle partizioni rilevanti, in teoria, senza essere in alcun modo coinvolti, ad eccezione delle risorse di sistema, dalle query che accedono contemporaneamente ai dati di altre partizioni. In SQL Server 2005, l'accesso contemporaneo ai dati di altre partizioni può causare un blocco della tabella impedendo l'accesso ad altre partizioni.

Per ridurre questa interferenza, SQL Server 2008 introduce un'opzione a livello di tabella per controllare l'escalation blocchi a livello di partizione o di tabella. Per impostazione predefinita, come in SQL Server 2005, l'escalation blocchi è attivata a livello di tabella, tuttavia, è possibile ignorare questo criterio. Ad esempio:

Alter table <mytable> set (LOCK_ESCALATION = AUTO)

Questo comando indica a SQL Server di scegliere la granularità dell'escalation blocchi adatta allo schema della tabella. Se la tabella non è partizionata, l'escalation blocchi avviene a livello TABLE. Se la tabella è partizionata, la granularità dell'escalation blocchi si limita al livello della partizione. Questa opzione viene inoltre utilizzata come suggerimento da SQL Server per impedire la granularità blocchi a livello di tabella.

Conclusioni

La presente è solo una panoramica sulle nuove e migliorate funzionalità di SQL Server 2008 che consentono di ottenere prestazioni superiori per le query di supporto decisionale nei data warehouse relazionali. Nonostante l'importanza dei tempi di risposta competitivi per le query di supporto decisionale, esistono altri requisiti chiave che superano gli obiettivi di questo articolo.

Alcune delle funzionalità aggiuntive relative ai data warehouse relazionali includono:

• il supporto per la sintassi MERGE in T-SQL per aggiornare, eliminare o inserire dati (dimensionali) con un'istruzione e round trip nel database.
• Prestazioni di registrazione ottimizzate del motore SQL Server per operazioni ETL più efficaci.
• Set di raggruppamento per semplificare la scrittura di query di supporto decisionale aggregate in T-SQL.
• Compressione backup per ridurre i requisiti di I/O per backup completi e incrementali.
• Controllo delle risorse per regolare l'assegnazione delle risorse di sistema a carichi di lavoro diversi.

Per informazioni dettagliate su queste interessanti funzionalità, consultare la pagina Web di SQL Server disponibile all'indirizzo microsoft.com/sql.

Si ringraziano Boris Baryshnikov, Prem Mehra, Peter Zabback e Shin Zhang per la consulenza tecnica.

Sunil Agarwal è Senior Program Manager del gruppo SQL Server Storage Engine in Microsoft. Si occupa di concorrenza, indici, tempdb, LOBS, supportabilità e importazione/esportazione bulk.

Torsten Grabs è Senior Program Manager Lead di Core Storage Engine nel team Microsoft SQL Server. Ha conseguito il titolo di PhD in sistemi di database e ha oltre 10 anni di esperienza in SQL Server.

Dr. Joachim Hammer è Program Manager del gruppo Query Processing in Microsoft. Si è specializzato in ottimizzazione delle query su applicazioni data warehouse su larga scala, esecuzione di query distribuita, ETL e integrazione delle informazioni.

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.