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 imprescindibile 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à tantissime 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.

 

Creazione delle tabelle per gli esperimenti sul QEP

Per il nostro primo esempio di analisi dei piani 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 un’indice non clustered sulla colonne Nome .

ALTER TABLE Clienti ADD PRIMARY KEY (NumeroCliente);

CREATE NONCLUSTERED INDEX IndiceNome ON Clienti(nome);

Consideriamo per la nostra analisi 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;

Analisi del piano di esecuzione in presenza di group by

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), a causa della sua dimensione ridotta rispetto al clustered index che contiene tutte le colonne.

In basso il piano di esecuzione delle tre query.

 

Analisi del piano di esecuzione di query con filtri e indici non clustered 

Prima di analizzare il secondo gruppo di query dobbiamo eseguire una serie di operazioni per creare le tabelle necessarie.

1. 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)

2. Inseriamo circa 10000 record nella tabella con un valore progressivo nell’IdCliente e la costante 1 nell’IdProdotto

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

3. Inseriamo ora un nuovo record con un IdProdotto differente

insert into TabellaTest(IdCliente,IdProdotto,IdFornitore)
      values (0,2,1)

4. Creiamo una chiave primaria su IdCliente

alter table TabellaTest
add primary key (IdCliente)

5. e una struttura d’appoggio sull’IdProdotto

create nonclustered index IndiceProdotto on TabellaTest(IdProdotto)

Andiamo ad analizzare il piano di esecuzione di queste due query

select * from TabellaTest where IdProdotto=1

select * from TabellaTest where IdProdotto=2

Le due query hanno piani di esecuzioni differenti nonostante siano quasi del tutto identiche, a meno del valore IdProdotto considerato nella where. 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 nell’indice.

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. In questo caso SqlServer deciderà dunque di scansionare direttamente il clustered index.

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 nell’indice clustered per i pochi casi d’interesse.

Migliorare-performance-sql-server

 

Corsi correlati

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.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Torna su