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!
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.
Altri impatti negativi degli indici
Come detto in precedenza, un indice nonclustered è sostanzialmente una copia parziale dei dati riordinata secondo un certo criterio. 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 DESC;
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
- Leggi l’articolo su come creare un filtered index per migliorare le performance di una query e il consumo di memoria.
- Alcuni semplici mosse per migliorare le performance di una query
- Il mio video corso su T-SQL e le performance https://www.yimp.it/video-corso-t-sql-avanzato-tuning-query/
- Il mio corso avanzato in diretta streaming sulle performance di SQL Server