SQL Server: covered index

In questo articolo discuteremo il concetto di Covered Index su SQL Server e di come esso possa essere utilizzato per migliorare le performance di alcune query. A volte, infatti, creare un indice semplicemente sulla foreign key o sulle colonne usate nella Where o nella Group By potrebbe non cambiare il piano d’esecuzione della query coinvolte. Capiamo meglio perché.

Funzionamento indici non clustered e INCLUDE

Quando creiamo un indice non clustered su una colonna, Sql Server salverà nelle foglie di tale indice soltanto i dati dell’indice clustered (generalmente la chiave primaria) o un puntamento alla riga nel caso l’indice clustered non fosse presente. Se nella query che vogliamo ottimizzare sono coinvolte altre colonne (anche semplicemente nella Select) occorrerà usare questo puntamento per recuperarle. Ma l’operazione di recupero non è gratis dal punto di vista delle performance. In alcuni casi è talmente costosa da spingere alla scelta di un piano di esecuzione che non utilizza gli indici non-clustered e che effettua dunque delle costose scansioni.

Quando creiamo un indice, tramite l’INCLUDE possiamo aggiungere altre colonne direttamente nelle foglie dell’indice, senza che i dati siano ordinati per queste colonne. Nel prossimo paragrafo vediamo con un esempio come può migliorare il piano d’esecuzione di una query con una Gruop By.

Attenzione comunque a non esagerare e a eseguire scelte con criterio, in quanto aggiungendo colonne nell’INCLUDE aumentiamo la dimensione dell’indice e di conseguenza anche il tempo necessario per il suo aggiornamento e manutenzione.

Miglioriamo le performance di una Group By

Partiamo creando una tabella temporanea di al più 50000 righe contenente due colonne con un progressivo e una colonna con il valore ‘a’. Aggiungiamo la chiave primaria sulla prima colonna e un indice non clustered sulla seconda

CREATE TABLE #TEST(
  Colonna1 INT NOT NULL PRIMARY KEY,
  Colonna2 INT,
  Colonna3 VARCHAR(50));
INSERT INTO #TEST(
   Colonna1, Colonna2, Colonna3)
SELECT TOP 50000 
 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
                              AS Colonna1, 
 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
                              AS Colonna2,
 'a' AS Colonna3 
FROM sys.objects AS A
CROSS JOIN sys.objects AS B;
CREATE NONCLUSTERED INDEX idx_test  
ON #Test(Colonna2);

Eseguiamo una query con una Group by sulla colonna con il nonclustered index. 

SELECT Colonna2,
   MAX(Colonna3) AS ValorePiuAlto
FROM   #Test 
GROUP BY Colonna2

Analizzando il relativo piano di esecuzione, vediamo che l’indice non viene utilizzato e l’operazione di GROUP BY è eseguita tramite il più costoso operatore Hash-Aggregate. La query infatti coinvolge anche la Colonna3 che non è presente nell’indice.

piano d'esecuzione con hash aggregate

Cancelliamo e ricreiamo l’indice aggiungendo la Colonna3 dopo l’INCLUDE e rieseguiamo la query

CREATE NONCLUSTERED INDEX idx_test 
ON #Test(Colonna2) 
INCLUDE (Colonna3) 
WITH (DROP_EXISTING = ON); 
SELECT Colonna2,
  MAX(Colonna3) AS ValorePiuAlto
FROM #Test 
GROUP BY Colonna2;

Nel piano d’esecuzione viene utilizzato l’indice e l’operazione di aggregazione è tradotta con il più efficiente Stream Aggregate

piano d'esecuzione con stream aggregate

Vi ricordo in generale di fare attenzione all’operazione di rimozione e creazione di un indice: su tabelle di grandi dimensioni potrebbe necessitare di una quantità di tempo non trascurabile. Inoltre alcuni piani d’esecuzione potrebbero dover essere ricalcolati per valutare la presenza di un nuovo indice.

Video Corso Data Analysis completo

Continua a imparare

Torna in alto
Torna su