Miglioramenti apportati all'elaborazione di query su tabelle e indici partizionati

SQL Server 2008 consente di migliorare le prestazioni di elaborazione delle query su tabelle partizionate per molti piani paralleli, di modificare le modalità di rappresentazione dei piani seriali e paralleli, nonché di ottimizzare le informazioni relative al partizionamento fornite nei piani di esecuzione sia della fase di compilazione che di esecuzione. In questo argomento vengono descritti i miglioramenti apportati, viene spiegato come interpretare i piani di esecuzione delle query relativi a tabelle e indici partizionati e vengono fornite le procedure consigliate per migliorare le prestazioni delle query su oggetti partizionati.

Nota

Il partizionamento di indici e tabelle è supportato solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server.

Nuova operazione di ricerca con riconoscimento delle partizioni

In SQL Server 2008 la rappresentazione interna di una tabella partizionata è stata modificata in modo che in Query Processor venga considerata come un indice a più colonne la cui colonna iniziale è PartitionID. PartitionID è una colonna calcolata nascosta utilizzata internamente per rappresentare l'ID della partizione che contiene una riga specifica. Ad esempio, si supponga che la tabella T, definita come T(a, b, c), venga partizionata in base alla colonna a e includa un indice cluster nella colonna b. In SQL Server 2008 questa tabella partizionata viene considerata internamente come una tabella non partizionata caratterizzata dallo schema T(PartitionID, a, b, c) e con un indice cluster sulla chiave composta (PartitionID, b). In tal modo Query Optimizer è in grado di eseguire su qualsiasi tabella o indice partizionato operazioni di ricerca basate su PartitionID.

L'eliminazione della partizione viene ora eseguita durante tale operazione di ricerca.

Query Optimizer è inoltre stato ampliato, pertanto è ora possibile eseguire un'operazione di ricerca o di analisi con un'unica condizione su PartitionID (come colonna iniziale logica) e su altre colonne chiave indice e quindi un'operazione di ricerca di secondo livello, con una condizione diversa, su una o più colonne aggiuntive, per ogni valore distinto che soddisfa la qualificazione per l'operazione di ricerca di primo livello. Questa operazione, denominata skip scan consente a Query Optimizer di eseguire un'operazione di ricerca o di analisi basata su un unica condizione per determinare le partizioni a cui effettuare l'accesso e un'operazione Index Scan di secondo livello all'interno di tale operatore per restituire le righe delle partizioni che soddisfano una condizione diversa. Si consideri ad esempio la query seguente.

SELECT * FROM T WHERE a < 10 and b = 2;

Per questo esempio si supponga che la tabella T, definita come T(a, b, c), venga partizionata in base alla colonna a e includa un indice cluster nella colonna b. I limiti delle partizione per la tabella T sono definiti dalla funzione di partizione seguente:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Per risolvere la query, Query Processor esegue un'operazione di ricerca di primo livello per individuare tutte le partizioni contenenti righe che soddisfano la condizione T.a < 10. In tal modo vengono identificate le partizioni a cui effettuare l'accesso. All'interno di ciascuna partizione identificata, viene quindi eseguita una ricerca di secondo livello nell'indice cluster della colonna b per individuare le righe che soddisfano le condizioni T.b = 2 e T.a < 10.

Nell'illustrazione seguente è riportata una rappresentazione logica dell'operazione di "skip scan". Include la tabella T con i dati nelle colonne a e b. Le partizioni sono numerate da 1 a 4 e i limiti delle partizioni sono contraddistinti da righe verticali tratteggiate. In seguito a un'operazione di ricerca di primo livello eseguita sulle partizioni (non riportata nell'illustrazione) è stato determinato che le partizioni 1, 2 e 3 soddisfano la condizione di ricerca prevista dal partizionamento definito per la tabella e il predicato sulla colonna a, ovvero T.a < 10. Il percorso attraversato dalla parte di ricerca di secondo livello dell'operazione di skip scan è illustrata dalla linea curva. In pratica, l'operazione di skip scan cerca in ciascuna di queste partizioni le righe che soddisfano la condizione b = 2. Il costo totale dell'operazione di skip scan equivale a quello di tre operazioni Index Seek distinte.

Visualizzazione di informazioni sul partizionamento nei piani di esecuzione delle query

È possibile esaminare i piani di esecuzione delle query su tabelle e indici partizionati utilizzando le istruzioni SET SHOWPLAN_XML o SET STATISTICS XML di Transact-SQL oppure l'output del piano di esecuzione grafico restituito in SQL Server Management Studio. È ad esempio possibile visualizzare il piano di esecuzione della fase di compilazione facendo clic su Visualizza piano di esecuzione stimato sulla barra degli strumenti dell'editor di query e il piano della fase di esecuzione facendo clic su Includi piano di esecuzione effettivo.

Questi strumenti consentono di verificare le informazioni seguenti:

• Operazioni, ad esempio analisi, ricerche, inserimenti, aggiornamenti, unioni ed eliminazioni, che accedono a tabelle e indici partizionati.

• Partizioni a cui viene effettuato l'accesso tramite la query. Ad esempio, il totale delle partizioni e gli intervalli relativi alle partizioni contigue a cui viene effettuato l'accesso sono disponibili nei piani di esecuzione della fase di esecuzione.

• Utilizzo dell'operazione di skip scan in un'operazione di ricerca o analisi per recuperare dati da una o più partizioni.

Per ulteriori informazioni sulla visualizzazione dei piani di esecuzione, vedere Procedure per i piani di esecuzione.

Miglioramenti apportati alle informazioni sulle partizioni

SQL Server 2008 fornisce informazioni migliorate sul partizionamento per i piani di esecuzione sia della fase di compilazione che della fase di esecuzione. I piani di esecuzione includono ora le informazioni seguenti:

• Un attributo Partitioned facoltativo per indicare che su una tabella partizionata viene eseguito un operatore, ad esempio una ricerca, un'analisi, un inserimento, un aggiornamento, un'unione o un'eliminazione.

• Un elemento SeekPredicateNew nuovo con un sottoelemento SeekKeys che include PartitionID come colonna iniziale della chiave di indice e condizioni di filtro che specificano ricerche di intervallo su PartitionID. La presenza di due sottoelementi SeekKeys indica che su PartitionID viene utilizzata un'operazione di skip scan.

• Informazioni di riepilogo che includono il totale delle partizioni a cui viene effettuato l'accesso. Queste informazioni sono disponibili solo nei piani della fase di esecuzione.

Per illustrare la modalità di visualizzazione di queste informazioni nell'output del piano di esecuzione grafico e nell'output di Showplan XML, considerare la query seguente sulla tabella partizionata fact_sales. Questa query implica l'aggiornamento dei dati in due partizioni.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

Nella figura seguente sono illustrate le proprietà dell'operatore Clustered Index Seek nel piano di esecuzione della fase di compilazione per questa query. Per visualizzare la definizione della tabella fact_sales e la definizione della partizione, vedere la sezione "Esempio" in questo argomento.

Attributo Partitioned

Quando su una tabella o un indice partizionato si esegue un operatore quale Index Seek, l'attributo Partitioned viene incluso sia nel piano della fase di compilazione che in quello della fase di esecuzione ed è impostato su True (1). L'attributo non viene visualizzato quando è impostato su False (0).

L'attributo Partitioned può essere visualizzato negli operatori fisici e logici seguenti:

• Table Scan

• Index Scan

• Index Seek

• Insert

• Update

• Delete

• Merge

Come illustrato nella figura precedente, questo attributo viene visualizzato nelle proprietà dell'operatore in cui è definito. Nell'output di Showplan XML, questo attributo è indicato come Partitioned="1" nel nodo RelOp dell'operatore nel quale è definito.

Nuovo predicato Seek

Nell'output di Showplan XML l'elemento SeekPredicateNew è visualizzato nell'operatore nel quale è definito. Può contenere fino a due occorrenze del sottoelemento SeekKeys. Il primo elemento SeekKeys specifica l'operazione di ricerca di primo livello a livello di ID della partizione dell'indice logico. Tale ricerca consente di determinare le partizioni a cui è necessario accedere per soddisfare le condizioni della query. Il secondo elemento SeekKeys specifica la parte della ricerca di secondo livello dell'operazione di skip scan che viene eseguita all'interno di ciascuna partizione identificata nella ricerca di primo livello.

Informazioni di riepilogo sulle partizioni

Nei piani di esecuzione della fase di esecuzione le informazioni di riepilogo sulle partizioni includono il totale delle partizioni e l'identità delle partizioni effettive a cui viene effettuato l'accesso. È possibile utilizzare queste informazioni per verificare che le partizioni a cui viene effettuato l'accesso tramite la query sono corrette e che tutte le altre partizioni non vengono considerate.

Vengono fornite le informazioni seguenti: Conteggio partizioni effettive e Partizioni effettive a cui è stato eseguito l'accesso.

Conteggio partizioni effettive corrisponde al numero totale di partizioni a cui viene effettuato l'accesso tramite la query.

Nell'output di Showplan XML Partizioni effettive a cui è stato effettuato l'accesso corrisponde alle informazioni di riepilogo sulle partizioni che vengono visualizzate nel nuovo elemento RuntimePartitionSummary del nodo RelOp dell'operatore nel quale è definito. Nell'esempio seguente è illustrato il contenuto dell'elemento RuntimePartitionSummary, in cui è indicato che viene effettuato l'accesso a due partizioni totali, ovvero la 2 e la 3.

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Visualizzazione delle informazioni sulle partizioni utilizzando altri metodi di Showplan

I metodi SHOWPLAN_ALL, SHOWPLAN_TEXT e STATISTICS PROFILE di Showplan non restituiscono le informazioni sulle partizioni descritte in questo argomento, con un'unica eccezione illustrata di seguito. In quanto incluse nel predicato SEEK, le partizioni a cui effettuare l'accesso sono identificate da un predicato di intervallo nella colonna calcolata che rappresenta l'ID di partizione. Nell'esempio seguente è illustrato il predicato SEEK per un operatore Clustered Index Seek. Viene effettuato l'accesso alle partizioni 2 e 3 e l'operatore di ricerca applica il filtro sulle righe che soddisfano la condizione date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

                ORDERED FORWARD)

Interpretazione dei piani di esecuzione per heap partizionati

In SQL Server 2008 un heap partizionato viene considerato come un indice logico sull'ID di partizione. In un piano di esecuzione l'eliminazione di partizioni in un heap partizionato viene rappresentata come un operatore Table Scan con un predicato SEEK sull'ID di partizione. Nell'esempio seguente sono illustrate le informazioni di Showplan fornite:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretazione dei piani di esecuzione per join collocati

La collocazione dei join può verificarsi quando due tabelle vengono partizionate utilizzando una funzione di partizionamento identica o equivalente e le colonne di partizionamento di entrambi lati del join sono specificate nella condizione di join della query. Query Optimizer può generare un piano in cui le partizioni di ogni tabella con ID di partizione uguali sono unite in join separatamente. I join collocati possono risultare più rapidi di quelli non collocati perché possono richiedere una minor quantità di memoria e tempi di elaborazione inferiori. Query Optimizer sceglie un piano non collocato o un piano collocato sulla base delle stime dei costi.

In un piano collocato il join Nested Loops legge una o più partizioni di tabelle o indici unite in join dal lato interno. I numeri all'interno degli operatori Constant Scan rappresentano i numeri della partizione.

Quando per le tabelle o gli indici partizionati si generano piani paralleli per join collocati, viene visualizzato un operatore Parallelism tra gli operatori di join Constant Scan e Nested Loops. In questo caso, ognuno dei thread nel lato esterno del join legge ed elabora una partizione diversa.

Nella figura seguente viene illustrato un piano di query parallele per un join collocato.

Strategia di esecuzione delle query parallele per oggetti partizionati

Query Processor utilizza una strategia di esecuzione parallela per query che eseguono la selezione da oggetti partizionati. Come parte della strategia di esecuzione, Query Processor determina le partizioni della tabella necessarie per eseguire la query e la proporzione di thread da allocare a ogni partizione. Nella maggior parte dei casi, Query Processor alloca un numero uguale o quasi uguale di thread a ogni partizione, quindi esegue la query in parallelo tra le partizioni. Nei paragrafi seguenti viene descritta più dettagliatamente l'allocazione dei thread.

Se il numero di thread è minore di quello delle partizioni, Query Processor assegna ogni thread a una partizione diversa, lasciando inizialmente uno o più partizioni senza thread. Quando termina l'esecuzione di un thread in una partizione, Query Processor assegna tale thread alla partizione successiva finché non viene assegnato un singolo thread a ogni partizione. Questo è l'unico caso in cui Query Processor rialloca i thread ad altre partizioni.

Se il numero di thread è uguale a quello delle partizioni, Query Processor assegna un thread a ogni partizione. Quando un thread termina, non viene riallocato ad altre partizioni.

Se il numero di thread è maggiore di quello delle partizioni, Query Processor alloca un numero uguale di thread a ogni partizione. Se il numero di thread non è un multiplo esatto del numero di partizioni, Query Processor alloca un thread aggiuntivo ad alcune partizioni per utilizzare tutti i thread disponibili. Si noti che se la partizione è unica, tutti i thread verranno assegnati a tale partizione. Nel diagramma seguente sono presenti quattro partizioni e 14 thread. Ogni partizione dispone di 3 thread assegnati e due partizioni dispongono di un thread aggiuntivo, per un totale di 14 thread assegnati. Quando un thread termina, non viene riassegnato ad altre partizioni.

Sebbene negli esempi precedenti venga suggerito un modo semplice per allocare thread, la strategia effettiva è più complessa e tiene conto di altre variabili che possono presentarsi durante l'esecuzione di query. Ad esempio, se la tabella è partizionata e dispone di un indice cluster nella colonna A e se in una query è presente la clausola del predicato WHERE A IN (13, 17, 25),, Query Processor allocherà uno o più thread a ciascuno dei tre valori di ricerca (A=13, A=17 e A=25) anziché eseguire l'allocazione a ogni partizione della tabella. È necessario solo eseguire la query nelle partizioni che contengono questi valori e, se tutti i predicati SEEK si trovano nella stessa partizione della tabella, tutti i thread verranno assegnati alla partizione specifica.

Per prendere un altro esempio, si supponga che la tabella disponga di quattro partizioni nella colonna A con punti limite (10, 20, 30) e di un indice nella colonna B e che nella query sia presente una clausola del predicato WHERE B IN (50, 100, 150). . Poiché le partizioni della tabella si basano sui valori di A, i valori di B possono trovarsi in qualsiasi partizione. Di conseguenza Query Processor ricercherà ciascuno dei tre valori di B (50, 100, 150) in ognuna delle quattro partizioni della tabella e assegnerà proporzionatamente i thread in modo da eseguire ciascuna delle 12 analisi della query in parallelo.

Partizioni della tabella basate sulla colonna A	Ricerca della colonna B in ogni partizione della tabella
Partizione della tabella 1: A < 10	B=50, B=100, B=150
Partizione della tabella 2: A >= 10 AND A < 20	B=50, B=100, B=150
Partizione della tabella 3: A >= 20 AND A < 30	B=50, B=100, B=150
Partizione della tabella 4: A >= 30	B=50, B=100, B=150

Procedure consigliate

Per migliorare le prestazioni di query che accedono a una grande quantità di dati da tabelle e indici partizionati estesi, è opportuno adottare le procedure consigliate seguenti:

• Eseguire lo striping di ogni partizione tra molti dischi.

• Quando possibile, utilizzare un server con memoria principale sufficiente per contenere partizioni a cui viene effettuato l'accesso frequentemente o a tutte le partizioni in memoria per ridurre il costo delle operazioni di I/O.

• Se i dati oggetto della query non sono tutti contenuti in memoria, comprimere le tabelle e gli indici al fine di ridurre il costo delle operazioni di I/O.

• Utilizzare un server con processori veloci e il maggior numero possibile di core del processore per sfruttare a pieno la funzionalità di elaborazione di query parallele.

• Assicurarsi che per il server sia disponibile larghezza di banda sufficiente del controller I/O.

• Creare un indice cluster in ogni tabella partizionata grande per sfruttare le ottimizzazioni dell'analisi dell'albero B.

• Quando si esegue il caricamento bulk di dati in tabelle partizionate, attenersi ai requisiti della procedura consigliata nel white paper "Loading Bulk Data into a Partitioned Table" (informazioni in lingua inglese).

Esempio

Nell'esempio seguente viene creato un database di test che contiene un'unica tabella con sette partizioni. Per l'esecuzione delle query in questo esempio utilizzare gli strumenti descritti in precedenza per visualizzare le informazioni sul partizionamento relative ai piani della fase di compilazione e della fase di esecuzione.

Nota

In questo esempio nella tabella viene inserito più di un milione di righe. A seconda dell'hardware disponibile l'esecuzione di questo esempio può richiedere diversi minuti. Prima di eseguire questo esempio, verificare di disporre di almeno 1,5 GB di spazio libero su disco.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Vedere anche

Riferimento

Visualizzazione dei piani di esecuzione grafici (SQL Server Management Studio)

DBCC TRACEON (Transact-SQL)

Concetti

Tabelle e indici partizionati

Visualizzazione di piani di esecuzione tramite le opzioni SET SHOWPLAN (Transact-SQL)