Condividi tramite

  • Articolo

Ottimizzazione di query che accedono a colonne datetime correlate

L'opzione SET di database, denominata DATE_CORRELATION_OPTIMIZATION migliora le prestazioni delle query che uniscono in equi-join due tabelle le cui colonne date o datetime sono correlate e che specificano una limitazione di data nel predicato delle query.

Le tabelle in cui i valori delle colonne date o datetime sono correlati e che traggono vantaggio dall'abilitazione di DATE_CORRELATION_OPTIMIZATION fanno in genere parte di una relazione uno-a-molti e vengono utilizzate prevalentemente per il supporto decisionale, la segnalazione o il data warehousing.

Nel database di esempio AdventureWorks, ad esempio, la colonna OrderDate della tabella Purchasing.PurchaseOrderHeader e la colonna DueDate della tabella Purchasing.PurchaseOrderDetail sono correlate. I valori delle date di PurchaseOrderDetail.DueDate tendono a essere di poco successivi a quelli di PurchaseOrderHeader.OrderDate.

Quando l'opzione di database DATE_CORRELATION_OPTIMIZATION è impostata su ON, in SQL Server vengono gestite statistiche sulla correlazione tra due tabelle qualsiasi del database in cui siano incluse colonne date o datetime e che siano collegate tramite un vincolo di chiave esterna con una colonna.Per impostazione predefinita, l'opzione è impostata su OFF.

SQL Server utilizza tali statistiche sulla correlazione insieme alla restrizione sulla data specificata nel predicato della query per dedurre che è possibile aggiungere ulteriori restrizioni alla query senza modificare il set dei risultati. Query Optimizer utilizza le condizioni derivate durante la scelta di un piano della query. In questo modo, è possibile ottenere un piano della query più rapido, in quanto le restrizioni aggiunte consentono la lettura di una quantità minore di dati da parte di SQL Server durante l'elaborazione della query. Le prestazioni risultano migliori anche quando in entrambe le tabelle sono stati definiti indici cluster e le relative colonne date o datetime per cui vengono gestite statistiche sulla correlazione sono la prima o l'unica chiave dell'indice cluster.

Si supponga, ad esempio, di preparare il database AdventureWorks per gestire le informazioni relative alla correlazione per Purchasing.PurchaseOrderDetail e Purchasing.PurchaseOrderHeader eseguendo lo script Transact-SQL seguente:

USE AdventureWorks;
GO

-- Create a unique index to take the place of the existing 
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Si supponga ora di eseguire la query seguente:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '20020101' AND '20020201';

I valori di PurchaseOrderDetail.DueDate restituiti dalla query potrebbero in genere essere compresi all'interno di un certo periodo di giorni, ad esempio 14, corrispondenti ai valori di PurchaseOrderHeader.OrderDate. Per questo motivo, SQL Server potrebbe essere in grado di dedurre che la query precedente possa essere espressa in modo più adeguato, in base a quanto indicato di seguito:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/02' AND '2/1/02'
AND d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14;

Il formato esatto della condizione aggiunta, specificata nella seconda clausola AND, dipende dalla query originale e dai valori dei dati nel database. Dopo avere aggiunto una condizione implicita, questa viene utilizzata da Query Optimizer per costruire un piano di esecuzione. Nell'esempio seguente è incluso un indice cluster in PurchaseOrderDetail.DueDate, affinché possa essere utilizzato per recuperare righe che soddisfino d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14. Se vi sono dati relativi a vari anni in Purchasing.PurchaseOrderDetail, la query può provocare una diminuzione significativa dei tempi di esecuzione se paragonata ad altre query originali.

Prima di eseguire un piano della query con una condizione derivata a causa dell'attivazione di DATE_CORRELATION_OPTIMIZATION, in SQL Server viene verificato che la query produca la risposta corretta, in base al contenuto corrente del database.

Requisiti per l'utilizzo dell'opzione di database DATE_CORRELATION_OPTIMIZATION

Affinché due tabelle possano trarre vantaggio dall'attivazione dell'opzione di database DATE_CORRELATION_OPTIMIZATION, devono essere soddisfatte tutte le condizioni seguenti:

  • Le opzioni di database SET devono essere impostate nel modo indicato di seguito. ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL e QUOTED IDENTIFIER devono essere impostate su ON. NUMERIC_ROUNDABORT deve essere impostata su OFF.

  • Tra le tabelle deve essere definita una relazione di chiave esterna con singola colonna.

  • Le tabelle devono entrambe includere colonne datetime definite come NOT NULL.

  • Almeno una delle colonne datetime deve essere la colonna chiave di un indice cluster (se la chiave di indice è composta, deve essere la prima chiave) oppure la colonna di partizionamento, se si tratta di una tabella partizionata.

  • Entrambe le tabelle devono essere di proprietà dello stesso utente.

Quando si imposta l'opzione di database DATE_CORRELATION_OPTIMIZATION su ON, considerare gli aspetti seguenti:

  • In SQL Server le informazioni sulla correlazione vengono gestite in forma di statistiche. Tali statistiche vengono aggiornate tramite SQL Server durante le operazioni INSERT, UPDATE e DELETE sulle tabelle applicabili, influendo talvolta sulle prestazioni di tali operazioni. Non è consigliabile attivare DATE_CORRELATION_OPTIMIZATION in ambienti con database in cui vengono eseguiti numerosi aggiornamenti.

  • Se nessuna delle colonne datetime per cui vengono gestite statistiche sulla correlazione è la prima o l'unica chiave di un indice cluster, valutare l'opportunità di crearvi un indice cluster. Questa operazione assicura prestazioni migliori nei tipi di query inclusi nelle statistiche sulla correlazione. Se nelle colonne chiave primaria è già presente un indice cluster, è possibile modificare una tabella affinché l'indice cluster e la chiave primaria utilizzino set di colonne diversi.

  • L'attivazione di DATE_CORRELATION_OPTIMIZATION non offre alcun vantaggio nei casi seguenti:

    • Non vi sono coppie di tabelle che soddisfano i criteri definiti in precedenza per la gestione delle statistiche sulla correlazione.

    • Vi sono coppie di tabelle che soddisfano i criteri relativi alla gestione delle statistiche sulla correlazione, ma le query che uniscono in join tali tabelle non specificano una restrizione sulla data nei relativi predicati.

Per impostare l'opzione di database DATE_CORRELATION_OPTIMIZATION

Utilizzo di statistiche sulla correlazione

Quando si imposta l'opzione di database DATE_CORRELATION_OPTIMIZATION su ON, per tutte le coppie idonee di tabelle corrispondenti vengono create automaticamente statistiche sulla correlazione in forma di viste indicizzate. Quando SQL Server Query Optimizer può sfruttare la correlazione tra coppie di colonne datetime, le statistiche sulla correlazione vengono utilizzate nel piano della query. Le statistiche sulla correlazione sono inoltre incluse nella logica delle istruzioni INSERT, UPDATE e DELETE quando queste sono interessate. I nomi delle statistiche sulla correlazione hanno il formato seguente:

_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>

<FK_constraint_name>rappresenta il nome del vincolo di chiave esterna nella vista del catalogo sys.objects su cui si basa la corrispondenza di datetime. <constraint_object_id> è una rappresentazione esadecimale a 8 cifre del valore objectid del vincolo di chiave esterna.

[!NOTA]

In SQL Server la parte FK_constraint_ del nome delle statistiche sulla correlazione viene abbreviata se il nome supera il limite consentito per la lunghezza dell'identificatore.

Quando si esegue una query utilizzando SET SHOWPLAN XML, qualsiasi nodo filtro derivato dalle statistiche sulla correlazione include l'attributo seguente:

DateCorrelationOptimization="true"

Un nodo <Predicate>, ad esempio, che dipende dalle statistiche sulla correlazione sarà simile al seguente:

<Predicate DateCorrelationOptimization="true">

Questo attributo viene incluso con qualsiasi nodo filtro generato interamente dalle statistiche sulla correlazione oppure combinando un predicato che dipende dalle statistiche con un altro predicato.

In genere, quando l'opzione di database DATE_CORRELATION_OPTIMIZATION è impostata su ON, in SQL Server vengono create statistiche sulla correlazione per tutte le coppie valide di colonne datetime. In SQL Server vengono create ulteriori statistiche sulla correlazione quando si eseguono le operazioni seguenti:

  • Creazione di vincoli di chiave esterna tramite CREATE TABLE o ALTER TABLE che soddisfano i requisiti per l'ottimizzazione della correlazione delle colonne datetime.

  • Creazione di un indice cluster in una colonna datetime, quando la colonna è idonea per la correlazione con la colonna datetime di un'altra tabella.

    [!NOTA]

    Quando gli indici cluster vengono creati utilizzando l'opzione ONLINE = ON, non vengono create statistiche sulla correlazione.Dopo l'esecuzione del commit della creazione dell'indice, è tuttavia possibile creare le statistiche sulla correlazione che dipendono dall'indice come risultato di un evento di un'altra transazione, ad esempio la creazione di un vincolo di chiave esterna.

  • Modifica del supporto di valori NULL o del tipo di dati di una colonna per renderla idonea per la correlazione con la colonna datetime di un'altra tabella.

Non è consigliabile fare riferimento alle statistiche sulla correlazione direttamente nella applicazioni, in quanto in SQL Server potrebbero essere eliminate in qualsiasi momento. È possibile decidere di eliminare singole statistiche sulla correlazione se si determina che i costi di gestione correlati influiscono sulle prestazioni. Per impostazione predefinita, le autorizzazioni DROP per le statistiche sulla correlazione vengono impostate per i membri del ruolo predefinito del server sysadmin, i ruoli predefiniti del database db_owner e db_ddladmin e il proprietario della coppia di tabelle su cui sono definite le statistiche sulla correlazione. Queste autorizzazioni non sono trasferibili.

Le statistiche sulla correlazione vengono eliminate nei casi seguenti:

  • Quando si imposta l'opzione di database DATE_CORRELATION_OPTIMIZATION su OFF, vengono eliminate tutte le statistiche sulla correlazione create da SQL Server.

  • Le statistiche sulla correlazione la cui gestione richiede uno spazio di archiviazione eccessivo o che sono ritenute poco vantaggiose vengono eliminate.

  • Quando si elimina un vincolo di chiave esterna tramite DROP TABLE o ALTER TABLE, vengono eliminate anche le statistiche sulla correlazione associate al vincolo.

  • Quando un'operazione fa sì che le tabelle che partecipano alla correlazione non appartengano più allo stesso utente, vengono eliminate le statistiche sulla correlazione corrispondenti.

  • Quando si esegue un'istruzione ALTER TABLE…SWITCH e per la tabella di origine o per quella di destinazione sono state definite statistiche sulla correlazione, tali statistiche vengono eliminate.

  • Quando si crea un indice cluster in una colonna datetime e vengono create statistiche sulla correlazione in una colonna datetime diversa della stessa tabella, le statistiche vengono eliminate. È possibile che in SQL Server vengano create nuove statistiche sulla correlazione in base al nuovo indice cluster creato, se idoneo.

  • Quando si elimina un indice cluster la cui chiave di indice iniziale è una colonna datetime, tutte le statistiche sulla correlazione associate vengono eliminate se nella stessa tabella è presente un'altra colonna datetime su cui sia possibile creare nuove statistiche sulla correlazione.

  • Quando si esegue ALTER TABLE per modificare il tipo di dati o il supporto di valori NULL di una colonna che partecipa a statistiche sulla correlazione, tali statistiche vengono eliminate.

Le statistiche sulla correlazione vengono create o eliminate come parte della stessa transazione che ne ha provocato la creazione o l'eliminazione. La transazione non è né in linea, né asincrona.

Quando si utilizza Ottimizzazione guidata motore di database in un semplice scenario di ottimizzazione basato su un server per ottimizzare direttamente il server di produzione, vengono considerati i costi e i vantaggi delle statistiche sulla correlazione. Quando invece si utilizza Ottimizzazione guidata motore di database in uno scenario con un server di produzione di prova, le statistiche sulla correlazione non vengono considerate come oggetti di sistema interni. Le statistiche sulla correlazione, pertanto, non vengono utilizzate nell'ottimizzazione delle query tramite Ottimizzazione guidata motore di database durante l'analisi per l'ottimizzazione degli indici. In uno scenario di produzione di prova, potrebbe essere utile ignorare i consigli visualizzati in Ottimizzazione guidata motore di database relativi alle viste indicizzate per cui sono disponibili statistiche sulla correlazione, in quanto vengono considerati solo i costi, ma non i vantaggi. In entrambi gli scenari, è possibile che in Ottimizzazione guidata motore di database non venga consigliata la selezione di indici specifici, ad esempio indici cluster in colonne datetime, che potrebbero invece risultare utili quando l'opzione DATE_CORRELATION_OPTIMIZATION è attivata.

Esecuzione di query sui metadati per ottenere statistiche sulla correlazione

Per visualizzare l'impostazione dell'opzione di database DATE_CORRELATION_OPTIMIZATION, selezionare la colonna is_date_correlation_on della vista del catalogo sys.databases.

Per determinare se una vista si basi su statistiche sulla correlazione, selezionare la colonna is_date_correlation_view della vista del catalogo sys.views (Transact-SQL).