Troppi indici danneggiano le performance? Unused index

Performance dei database e temi più avanzati
impatto degli indici su sql server

Il moltiplicarsi di indici non-clustered può essere una delle cause del deterioramento delle performance di un database. In questo articolo capiamo perché e vediamo un buon punto di partenza per iniziare le nostre analisi su SQL Server.

 

Impatti di un indice non-clustered sull’aggiornamento di un DB

La creazione di un indice può migliorare notevolmente le performance di una query, fornendo al Database una copia parziale dei dati ordinata secondo la colonna di definizione dell’indice. Questa copia potrà essere utilizzata per ridurre drasticamente operazioni generalmente molto impattanti come la rimozione dei duplicati o l’ordinamento. Per approfondire questi temi puoi seguire il mio video corso gratuito https://www.yimp.it/courses/corso-gratuito-performance-sql-server-e-piani-esecuzione/.

Tuttavia essendo a tutti gli effetti una copia (seppur parziale) dei dati, ogni volta che andremo a inserire una riga nella tabella SQL Server dovrà preoccuparsi di aggiornare anche tutti gli indici relativi. Guardiamo ad esempio questo codice SQL:

CREATE TABLE Clienti(
   IdCliente INT PRIMARY KEY,
   Nome VARCHAR(50),
   Cognome VARCHAR(50));
CREATE NONCLUSTERED INDEX 
IX_Nome ON 
Clienti(Nome);
CREATE NONCLUSTERED INDEX 
IX_Cognome ON 
Clienti(Cognome);

Abbiamo creato una tabella e due indici nonclustered associati. Analizziamo ora il piano di esecuzione di una semplice INSERT 

INSERT INTO Clienti(
  IdCliente, Nome, Cognome)
VALUES
  (0,'Nicola','Rossi');

Apparentemente è presente un solo operatore Clustered Index Insert, tuttavia andando ad analizzare le sue proprietà vediamo che sono coinvolti anche i due indici non clustered!

piano d'esecuzione insert
La situazione diventa ancora più chiara con una INSERT “massiva” di diecimila righe

INSERT INTO Clienti(
IdCliente, Nome, Cognome)
SELECT top 10000
 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
 'Giovanni',
 'Rossi'
FROM Sys.objects AS a
CROSS JOIN Sys.objects AS B

In questo caso vediamo direttamente nel piano d’esecuzione tre operatori distinti.

inseriamo diecimila righe e vediamo tre operatori distinti nel qep

 

Altri impatti negativi degli indici

Come detto in precedenza, un indice nonclustered è sostanzialmente una copia parziale dei dati riordinata secondo un certo critero. Di conseguenza:

  • occuperà memoria sul disco
  • aumenterà il tempo necessario per le operazioni di backup ed eventualmente ripristino

Pinal Dave ci mostra che può addirittura diminuire le performance di query di selezione https://blog.sqlauthority.com/2019/05/28/sql-server-an-index-reduces-performance-of-select-queries/

Inoltre non è assolutamente scontato che SQL Server scelga di utilizzare gli indici che abbiamo creato per eseguire query che coinvolgono le relative colonne, abbastanza spesso è necessario che l’indice copra tutto ciò che è coinvolto nella query.  Ad esempio, con la tabella creata precedentemente una query come

SELECT *
FROM   Clienti
WHERE  Nome = 'Giovanni';

ignorerà l’indice sul nome per via della distribuzione dei dati.

 

Interroghiamo la sys.dm_db_index_usage_stats

La sys.dm_db_index_usage_stats è una vista di sistema che può essere un buon punto di inizio per le nostre analisi. In essa infatti sono presenti utili statistiche sull’utilizzo degli indici come il numero di ricerche, scansioni e aggiornamenti. La vista non contiene i dati storici dall’installazione di SQL Server. Infatti molti fattori possono provocare il reset del contenuto di questa vista, primo tra tutti un riavvio del server. Tuttavia in generale i dati presenti possono essere utili per avere dei primi dati di partenza. Guardiamo un esempio di query sulla vista:

Attenzione: non fare copia e incolla del codice senza i dovuti test

USE Gestionale; /*Database d'esempio*/
SELECT
  o.name as tabella,
  i.name as indice,
  i.type_desc,	   
  u.*
FROM sys.dm_db_index_usage_stats AS u 
     /*vista di sistema con statistiche 
       su utilizzo degli indici */
INNER JOIN sys.indexes AS i      
	ON u.index_id = i.index_id  
	AND u.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o  
	ON u.OBJECT_ID = o.OBJECT_ID
WHERE u.database_id = DB_ID(N'Gestionale')
AND o.type = 'U'  /*tabelle create dall'utente*/
AND i.index_id > 1 /*consideriamo solo indici nonclustered */
AND i.is_primary_key = 0 /*per togliere le chiavi primarie*/
AND i.is_unique = 0      /*per togliere gli indici univoci */
AND i.is_unique_constraint = 0 /*per togliere gli indici creati da vincoli univoci*/
ORDER BY user_seeks + user_scans + user_lookups ASC;

Alcune osservazioni sulla query:

  • la join con la sys.indexes e la sys.objects è fatta essenzialmente per ricavare il nome dell’indice e della tabella relativa. In questo articolo parlo di come estrarre l’elenco degli indici da SQL Server https://www.yimp.it/estrarre-lelenco-degli-indici-di-sql-server/
  • il primo filtro u.database_id = DB_ID(N’Gestionale’) è molto importante in quanto la sys.dm_db_index_usage_stats contiene i dati di tutti i Database e quindi bisogna assicurarsi di filtrare i dati sullo stesso database a cui si riferiscono la sys.indexes e la sys.objects (scelto tramite l’istruzione iniziale USE <nome database>)
  • i filtri seguenti restringono il perimetro di analisi alle tabelle create dagli utenti (o.type = ‘U’) e agli indici nonclustered che non sono coinvolti in vincoli di univocità
  • con l’Order By ordiniamo gli indici partendo da quelli meno usati

Attenzione: questa query può essere solo il punto di partenza di un’analisi sull’utilizzo degli indici. Ad esempio potrebbe estrarre tra i primi posti degli indici utilizzati solo in query lanciate a fine mese o fine trimestre, la cui presenza può essere fondamentale per l’esecuzione di query critiche per il business.

 

Continua a imparare

Torna su