Piano di esecuzione e performance di una query

/ Performance dei database e temi più avanzati

Lo studio del piano di esecuzione di una query è uno step imprescindibile per migliorare le performance 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 colonna 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:

#Query 1
SELECT Nome
FROM   Clienti
GROUP BY Nome;
#Query 2
SELECT Cognome
FROM   Clienti
GROUP BY Cognome;
#Query 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.
piano d'esecuzione con group by sql server

T-SQL avanzato e tuning delle 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 AS a
CROSS JOIN sys.objects AS b;

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:

#Query 1
SELECT * 
FROM   TabellaTest 
WHERE  IdProdotto = 1;
#Query 2
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 (che puoi approfondire a questo link https://docs.microsoft.com/it-it/sql/relational-databases/statistics/statistics?view=sql-server-ver15) 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

Il tuo percorso completo di Analisi dei Dati

 

Continua a imparare

Scroll to Top
Torna su