Piano di esecuzione di una query

Piano di esecuzione e performance di una query

Lo studio del piano di esecuzione di una query è uno step imprescendibile per migliorare le perfomance di un database relazionale e velocizzarne i tempi di esecuzione. La lettura del QEP (query execution plan) noto anche come piano di accesso è il primo passo da fare quando occorre migliorare i tempi di una query troppo lenta, la sua comprensione ci fornirò tantassime informazioni sulle operazioni svolte dal database e sulle strategie da attuare per ottimizzarne le performance. Vedremo in questo articolo una serie d’esempi a riguardo.

 

Analisi degli indici clustered e degli algoritmi di ordinamento

Per il nostro primo esempio di lettura del piano di esecuzione, creiamo una tabella Clienti con tre colonne

CREATE TABLE Clienti (NumeroCliente int not null,
Nome varchar(100) not null,
Cognome varchar(100) not null);

Popoliamola con un po’ di record

INSERT INTO Clienti (NumeroCliente, Nome, Cognome)
VALUES
(1, ‘Francesco’, ‘Marino’),
(2, ‘Francesco’, ‘Gialli’),
(3, ‘Francesco’, ‘Verdi’),
(4, ‘Carlo’, ‘Rossi’);

Aggiungiamo una chiave primaria sulla colonna NumeroCliente e una struttura d’appoggio sulla colonne Nome .

ALTER TABLE Clienti ADD PRIMARY KEY (NumeroCliente);

CREATE NONCLUSTERED INDEX IndiceNome ON Clienti(nome);

Consideriamo queste tre query con una struttura molto simile:

  1. SELECT Nome
    FROM Clienti
    GROUP BY Nome;
  2. SELECT Cognome
    FROM Clienti
    GROUP BY Cognome;
  3. SELECT NumeroCliente
    FROM Clienti
    GROUP BY NumeroCliente;

Ci poniamo tre domande: quante tra queste query sono risolte esclusivamente con una scansione del clustered index? In quali è presente un algoritmo di ordinamento? Esaminiamole una alla volta, in basso sono riportati i piani di esecuzione.

Scansione con clustered Index e algoritmi di Ordinamento

Prima query: SELECT Nome FROM Clienti GROUP BY Nome;

Le informazioni necessarie per risolvere la query sono contenute tutte nell’indice non clustered costruito sulla colonna nome. In questa struttura d’appoggio i dati sono già ordinati, di conseguenza l’operazione di rimozione dei duplicati sarà immediata. Concludendo, il piano di esecuzione prevederà una semplice scansione ordinata dell’indice (Index Scan) e l’aggregazione dei record uguali (Stream Aggregate) dal costo trascurabile.

 

Seconda query: SELECT Cognome FROM Clienti GROUP BY Cognome;

Sul campo Cognome non è presente nessuna struttura d’appoggio. Il piano d’esecuzione sarà dunque obbligato a scansionare l’intero clustered index. Per rendere più efficiente l’operazione di rimozione dei duplicati sarà inoltre necessario un oneroso ordinamento (78% del costo totale).

 

Terza query: SELECT NumeroCliente FROM Clienti GROUP BY NumeroCliente;

Occorre osservare che non sono necessarie operazioni di rimozione dei duplicati. Sql Server è consapevole che la colonna NumeroCliente è chiave primaria, di conseguenza si limiterà a scansionare i dati. Tale scansione sarà fatta sull’indice non clustered (invece che sull’indice clustered), probabilmente a causa della sua dimensione ridotta rispetto alla struttura principale che contiene tutte le colonne.

 

Ricapitolando, nessuna delle tre query proposte effettua esclusivamente una scansione del clustered index, mentre una query prevede un operatore di ordinamento (la seconda).

In basso il piano di esecuzione delle tre query.

 

Impatto degli indici non clustered sulle performance

Per il secondo esempio creiamo una tabella con tre colonne: IdCliente, IdProdotto e IdFornitore

CREATE TABLE TabellaTest (

IdCliente int not null,

IdProdotto int not null,

IdFornitore int not null)

 

Inseriamo nella tabella circa 10000 record con l’IdCliente progressivo e l’IdProdotto costantemente uguale a 1

insert into TabellaTest(IdCliente,IdProdotto,IdFornitore)

select ROW_NUMBER() over(order by (select null)),1,1

from sys.objects aa

cross join  sys.objects bb

 

Inseriamo ora un nuovo record con un IdProdotto differente

insert into TabellaTest

      values (0,2,1)

 

Creiamo una chiave primaria su IdCliente

alter table TabellaTest

add primary key (IdCliente)

 

e una struttura d’appoggio sull’IdProdotto

create nonclustered index IndiceProdotto on TabellaTest(IdProdotto)

 

Osserviamo che tale struttura non contiente l’IdFornitore.

Le due query seguenti hanno lo stesso piano di esecuzione?

select * from TabellaTest where IdProdotto=1

select * from TabellaTest where IdProdotto=2

 

Le due query hanno due piani di esecuzioni differenti nonostante siano quasi del tutto identiche a meno del valore di IdProdotto da filtrare. Cerchiamo di capire il motivo identificando le due peculiarità principali della query:

– sulla colonna oggetto di filtro (IdProdotto) è costruito un indice non clustered;

– sono richieste in output tutte le colonne, compreso il campo IdFornitore che non è presente nella struttura d’appoggio.

Quando filtriamo per IdProdotto=1, le statistiche del database intercettano la presenza di molte righe che rispettano il filtro. Di conseguenza non avrebbe senso fare un’iniziale ricerca nella struttura d’appoggio per poi riscorrere quasi interamente il clustered index al fine di abbinare i valori dell’IdFornitore. Nel primo caso SqlServer deciderà dunque di scorrere direttamente la struttura principale.

Quando filtriamo per IdProdotto=2, le statistiche del database intercettano la presenza di un filtro molto selettivo. In questo caso il database reputa conveniente effettuare una prima ricerca nell’indice non clustered, per poi recuperare le altre colonne nella struttura principale per i pochi casi d’interesse.

Migliorare-performance-sql-server

 

Piano di esecuzione di una query: e adesso?

Se cerchi un corso per imparare a leggere gli execution plan e padroneggiare tutte le tecniche per analizzare e migliorare le performance di Sql Server , visita la pagina del nostro corso avanzato sul performance tuning.

Torna su