Gli indici di SQL Server sono essenzialmente copie dei dati già esistenti nella tabella, ordinati e filtrati in diversi modi per migliorare le prestazioni delle query eseguite.
Sebbene un uso corretto degli indici di SQL Server possa garantire prestazioni migliori per le query eseguite e quindi per SQL Server in generale, impostarli in modo improprio o non impostarli ove necessario, può peggiorare significativamente le prestazioni delle query. Inoltre, anche avere indici non necessari che non vengono utilizzati dalle query (definiti bad index) può essere problematico.
Gli indici di SQL Server sono infatti uno strumento eccellente per migliorare le prestazioni delle query SELECT, ma allo stesso tempo hanno effetti negativi sugli aggiornamenti dei dati. Gli indici inutilizzati nel tempo si accumulano nel database, spesso proprio perché sono stati creati per “una query specifica” e sono inutili in generale, o perché i cambiamenti nella distribuzione dei dati hanno portato l’Optmizer a cambiare la sua strategia. Questi indici comportano un sovraccarico su qualsiasi operazione di scrittura.
Le operazioni INSERT, UPDATE e DELETE provocano l'aggiornamento dell'indice e quindi la duplicazione dei dati già esistenti nella tabella. Di conseguenza, aumenta la durata delle transazioni e dell'esecuzione delle query e spesso possono verificarsi lock, block, deadlock e timeout di esecuzione abbastanza frequenti. Ciò si può anche tradurre in una frammentazione dell'indice e spesso si finisce per utilizzare ancora più risorse per eseguire ricostruzioni o riorganizzazioni di indici che vengono utilizzati raramente.
Per database o tabelle di grandi dimensioni, lo spazio di archiviazione è influenzato anche da indici ridondanti. Un obiettivo fondamentale, di qualsiasi DBA SQL Server, è dunque quello di mantenere gli indici inclusa la creazione di nuovi indici richiesti ma allo stesso tempo rimuovere quelli che non vengono utilizzati.
Identificare un bad index
Sono disponibili molte risorse online su come individuare ed eliminare gli indici bad, ma esistono numerosi problemi relativi alla rimozione di questi indici. Come possiamo assicurarci di eliminare in modo sicuro gli indici SQL Server non utilizzati? Quali sono i potenziali problemi?
Alcuni DBA probabilmente eseguono una revisione degli indici inutilizzati come parte della loro routine. I DBA esperti comprendono le conseguenze della rimozione di un indice ed i potenziali problemi che questa può provocare. Nella sezione successiva esamineremo alcuni di questi potenziali problemi e forniremo alcuni esempi in cui si dovrebbero semplicemente lasciare gli indici così come sono (anche se non vengono utilizzati).
SQL Server fornisce una quantità significativa di informazioni sull'indice tramite le Dynamic Management Views (DMVs). La DMV dm_db_index_usage_stats estrae informazioni essenziali sull'utilizzo dell'indice e può essere uno strumento utile per identificare gli indici SQL Server inutilizzati.
Quando un indice viene utilizzato per la prima volta, viene creata una nuova riga nella DMV dm_db_index_usage_stats e successivamente aggiornata ogni volta che viene utilizzato l’indice. Tuttavia, come per ogni DMV, le informazioni presenti in dm_db_index_usage_stats contengono solo i dati dall'ultimo riavvio del servizio SQL Server, dato che il riavvio del servizio SQL Server resetta i dati nella DMV. Inoltre, è importante notare che, oltre al riavvio del servizio, ci sono altri eventi che resettano le informazioni presenti nella DMV, ad esempio mettere offline il database oppure eliminare con drop e rigenerazione un indice.
Pertanto, è fondamentale che vi sia un tempo sufficiente dall'ultimo riavvio di SQL Server per consentire di determinare correttamente quali indici sono buoni candidati per essere eliminati.
Considerazioni in fase di identificazione ed eliminazione di un indice
Solitamente, in fase di identificazione di un bad index, si possono riscontrare una serie di problemi, vediamoli in ordine:
| # | Problema |
|---|---|
| 1 | Trovare una query adeguata e completa per l’identificazione degli indici inutilizzati |
| 2 | La query utilizzata può avere raccomandazioni troppo specifiche per un’indagine adeguata |
| 3 | Indici inutilizzati ricreati in continuazione |
| 4 | Indici inutilizzati che sono hard-coded tramite hint all’interno del codice applicativo |
| 5 | Indici inutilizzati che non dovrebbero essere rimossi |
| 6 | Nessuna evidenza che l’eliminazione di questi indici migliorerà le prestazioni |
1) Spesso le query che si possono trovare online per ricercare gli indici inutilizzati sono simili a questa:
SELECT OBJECT_SCHEMA_NAME(i.[object_id]) AS [Schema_name] ,OBJECT_NAME(i.[object_id]) AS [Object_name] ,i.name AS [Index Name] ,iu.[object_id] ,i.index_id ,i.[type_desc] ,i.is_disabled ,i.is_hypothetical ,i.has_filter ,iu.user_seeks ,iu.user_scans ,iu.user_lookups ,iu.user_updates FROM sys.dm_db_index_usage_stats iu RIGHT JOIN sys.indexes i ON iu.index_id = i.index_id AND iu.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(iu.[object_id], 'IsUserTable') = 1 AND iu.database_id = DB_ID()
Qualcuna ha dei filtri aggiuntivi altre invece no. Verifichiamo alcuni di questi filtri per capire quali sono i vantaggi e gli svantaggi.
Nella query sopra manca un importante filtro per evitare di prendere decisioni sbagliate.
... AND i.type = 2 AND i.is_primary_key = 0 -- type = 2 è riferito ai nonclustered index AND i.is_unique = 0 AND i.is_unique_constraint = 0...
Nella query precedente mancano le esclusioni dei vincoli univoci e di chiave primaria e si potrebbe interrompere l'applicazione se non si filtrano gli indici "non utilizzati" che sono anche vincoli univoci e di chiave primaria, quindi è consigliabile assicurarsi di utilizzare sempre il filtro sopra quando si contollano gli indici non utilizzati.
Molti script che si trovano online sono condivisi come linee guida e necessitano di analisi e svariati test prima di poter essere utilizzati in un ambiente di produzione.
2) La query sopra è uno script di base che si può utilizzare (una volta compreso come vengono utilizzati gli indici) per il proprio caso specifico.
Non contiene raccomandazioni, ad esempio, per visualizzare gli indici che vengono aggiornati più frequentemente di quanto vengano interrogati. Vediamo un esempio di tale filtro:
... WHERE...... AND iu.user_updates > (iu.user_scans + iu.user_seeks + iu.user_lookups)
Il risultato non riporterà esclusivamente gli indici inutilizzati. Quelli trovati sono indici con un overhead di aggiornamento.
Ecco riportati alcuni esempi per cui l’ultimo filtro non risulta essere utile al nostro scopo:
- Se abbiamo user_updates = 2 e user_scans + user_seeks + user_lookups = 1 per un indice, questo indice verrà restituito con l’utilizzo del filtro sopra. Ma ciò non significa in realtà che abbiamo un enorme overhead per l'aggiornamento dell'indice. In questo caso la differenza tra gli aggiornamenti e l'utilizzo dell'indice è troppo piccola.
- Nel secondo esempio, prendiamo il caso dell’apertura di alcuni report molto importanti per l'azienda. Supponiamo che questi report, vengano eseguiti una volta al mese ed utilizzino i nostri indici (con overhead). La somma delle esecuzioni dei report senza indice è di alcune ore, con gli indici pochi minuti. Moltiplicando per il numero di mesi in un anno si potrebbe risparmiare qualche giorno lavorativo ogni anno. Potrebbero esserci molti aggiornamenti a questi indici nel corso di un mese, quindi la condizione di cui sopra inserirà anche questi indici come candidati per l'eliminazione, ma il vantaggio di avere questi indici potrebbe essere molto più elevato rispetto all’overhead degli aggiornamenti. Una possibile soluzione alternativa, in questo caso, potrebbe essere la creazione degli indici prima di questi processi e l’eliminazione alla fine.
È necessario comprendere come viene utilizzato ogni indice bad e prendere la decisione giusta sulla base di varie prove e test rispetto alla semplice interrogazione della DMV.
Il filtro sopra non è adatto a tutti gli scenari, ma si può modificare in questo modo:
... AND ((iu.user_seeks + iu.user_scans + iu.user_lookups) > 0
AND iu.user_updates/( iu.user_seeks + iu.user_scans + iu.user_lookups ) > 5)
OR (iu.user_seeks + iu.user_scans + iu.user_lookups) = 0 )...
Questo permetterà di visualizzare gli indici "veramente" inutilizzati (da ricordare che sono inutilizzati dall'ultimo ripristino DMV, quindi non significa che l'indice non sia stato utilizzato prima). Verranno inoltre visualizzati gli indici in cui il rapporto aggiornamenti/utilizzo è superiore a 5.
Tuttavia, è consigliabile ignorare completamente questi filtri poiché l'utilizzo di ogni indice è un caso a sé e non è possibile avere una query adatta a tutti gli scenari di utilizzo degli indici.
Una buona base per iniziare può essere questa:
SELECT OBJECT_SCHEMA_NAME(i.[object_id]) AS [Schema_name] ,OBJECT_NAME(i.[object_id]) AS [Object_name] ,i.name AS [Index Name] ,iu.[object_id] ,i.index_id ,i.[type_desc] ,i.is_primary_key ,i.is_unique ,i.is_unique_constraint ,iu.user_seeks ,iu.user_scans ,iu.user_lookups ,iu.user_updates ,iu.user_seeks + iu.user_scans + iu.user_lookups AS total_uses ,CASE WHEN (iu.user_seeks + iu.user_scans + iu.user_lookups) > 0 THEN iu.user_updates / (iu.user_seeks + iu.user_scans + iu.user_lookups) ELSE iu.user_updates END AS update_to_use_ratio FROM sys.dm_db_index_usage_stats iu RIGHT JOIN sys.indexes i ON iu.index_id = i.index_id AND iu.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(iu.[object_id], 'IsUserTable') = 1 AND iu.database_id = DB_ID() ORDER BY CASE WHEN (iu.user_seeks + iu.user_scans + iu.user_lookups) > 0 THEN iu.user_updates / (iu.user_seeks + iu.user_scans + iu.user_lookups) ELSE iu.user_updates END DESC
3) Indici inutilizzati ricreati più e più volte.
Si può verificare la situazione in cui un indice considerato bad ed eliminato venga ricreato più e più volte. Questo perché c’è la possibilità che l’applicazione stessa vada a ricreare determinati indici nel database in particolari circostanze come ad esempio il riavvio dell’applicazione stessa. Anche la modifica degli indici dell'applicazione (aggiunta di colonne incluse, ecc.) potrebbe causare problemi all'applicazione.
Questi sono solo degli esempi per cui si dovrebbero mantenere gli indici del fornitore così come sono.
4) Gli indici "inutilizzati" sono hard-coded con hint dell’indice.
Se le query dell'applicazione di un fornitore hanno hint di tabella/query per forzare l'utilizzo di un indice specifico, l'applicazione andrà in errore nel caso in cui l’indice venisse eliminato.
Questo succederà anche in caso di disabilitazione dell’indice.
Ecco un esempio di codice con hint dell’indice:
SELECT ProductID ,ReviewerName ,[Comments] FROM [Production].[ProductReview] WITH (INDEX = IX_ProductReview_ProductID_Name)
Microsoft pone attenzione in fase di utilizzo di eventuali hint:
Per riassumere: gli hint sull'indice sono accettabili in alcune(rare) occasioni, ma devono essere monitorati e rivisti: è fondamentale assicurarsi di non avere hint sull'indice prima di apportare modifiche all’indice stesso.
Si possono trovare eventuali hint iniziando con questo script:
SELECT OBJECT_NAME([object_id]) objectName ,SUBSTRING([definition], CHARINDEX('WITH', [definition], 1), 50) IndexHint FROM sys.sql_modules WHERE REPLACE(REPLACE(SUBSTRING([definition], CHARINDEX('WITH', [definition], 1), 50), ' ', ''), ' ', '') LIKE '%WITH (INDEX%'
5) Indici inutilizzati che non dovrebbero essere rimossi.
Unique constraints
Un esempio di ulteriori motivi di cautela è che l'indice potrebbe essere elencato come inutilizzato, ma potrebbe applicare un vincolo univoco ed è probabile che Query Optimizer possa aver bisogno di questo indice. L'ottimizzatore di query potrebbe utilizzare una garanzia di unicità nel determinare quali trasformazioni logiche e operazioni fisiche dovrebbero essere utilizzate per ottenere risultati accurati.
L'ottimizzatore delle query tiene conto di una garanzia di unicità per eseguire determinate operazioni, ma ciò non viene ripreso nelle statistiche sull'utilizzo dell'indice senza accedere fisicamente all'indice nel piano di esecuzione finale. Tenendo presente questo, qualsiasi rimozione di indice o vincolo univoco deve essere presa con la massima precauzione
Use statistics
Un'altra cosa a cui prestare attenzione è la possibilità che il Query Optimizer utilizzi la statistica associata a quell'indice anche in situazioni in cui il piano di esecuzione finale non utilizzi alcun accesso a quell'indice.
Le stime della cardinalità, il caricamento dei candidati per le statistiche ed infine la creazione di un piano di esecuzione delle query completo sono azioni del tutto indipendenti.
Infine, la rimozione dell'indice potrebbe rimuovere anche le relative statistiche. Ciò può influire sulla qualità del piano di esecuzione delle query quando l’istruzione viene ricompilata. Questo perché il piano di esecuzione della query potrebbe utilizzare le statistiche dell'indice, anche quando l'indice non è fisicamente presente nel piano di esecuzione finale, per calcolare la stima della cardinalità, che è qualcosa su cui il piano di esecuzione finale si basa in modo significativo.
6) Non ci sono prove sufficienti che l'eliminazione degli indici inutilizzati migliorerà le prestazioni.
Il suggerimento è quello di effettuare numerosi test. Inoltre è importante:
- Verificare le performance di una query che usa un bad index quando l’indice è presente e quando l’indice è stato eliminato.
- Analizzare l’overhead dovuto agli aggiornamenti dell’indice ed ottenere risposte concrete che giustifichino il degrado delle prestazioni del sistema.
- Valutare i vantaggi e gli svantaggi in base al fatto di avere o non avere un determinato indice.
Uno dei motivi forniti per eliminare gli indici "inutilizzati" è quello di risparmiare spazio su disco. Nel tempo però il costo dello storage è diminuito, quindi non è più un problema centrale come lo era prima. Inoltre, con la compressione dei dati, in alcuni casi è possibile risparmiare spazio su disco.
Pertanto, lo spazio dello storage non dovrebbe essere il motivo principale della rimozione dell'indice (a meno che non si abbiano vincoli di spazio su disco). Ci si dovrebbe concentrare sull'impatto sulla CPU e sulla memoria durante gli aggiornamenti dell'indice.
Conclusioni
Abbiamo riportato solo alcuni esempi di un utilizzo fuori dallo standard degli indici da parte dei fornitori terzi ma potrebbero esistere altri scenari di questo tipo.
Bisogna fare molta attenzione prima di decidere di eliminare gli indici, specialmente sui database di fornitori terzi e assicurarsi che l'applicativo sia ancora supportato in caso di eliminazione di questi indici. Alcuni fornitori non forniscono supporto per un'applicazione dopo che è stata effettuata una modifica al database, inclusa la modifica degli indici.
Sulla base di quanto visto fino a qui, riportiamo le raccomandazioni per la rimozione degli indici non utilizzati:
- Eliminare solo gli indici che sono stati creati da voi stessi o dal vostro team.
- Per gli indici già esistenti fare riferimento al fornitore applicativo prima di prendere una qualsiasi decisione.
- Documentare sempre i nuovi indici creati, in particolare quelli ad alto impatto.
- Assicurarsi che gli indici non vengano ricreati da un'applicazione o da un processo automatizzato.
- Verificare che non siano presenti hint sull’indice.
- Assicurarsi di salvare uno script dell’indice, completo di tutte le proprietà, prima di eliminarlo ed avere un piano di recupero nel caso in cui qualcosa dovesse andare storto.
- Come alternativa all’eliminazione diretta si potrebbe provare prima a disabilitare gli indici.
- Tenere traccia dell'utilizzo dell'indice per almeno 2/3 mesi (più tempo è meglio è). Eventualmente creare una tabella\log di cronologia di utilizzo dell’indice all’interno del database DBA.
- Non devono essere eliminati gli indici solamente perché non vengono utilizzati oppure perché la loro manutenzione risulta essere molto impattante rispetto al vantaggio ricavato dal loro stesso utilizzo.
- Solitamente si deve partire da un caso evidente di degrado delle performance, come ad esempio un processo con prestazioni scadenti oppure una query lenta con un notevole overhead in fase di aggiornamento dell’indice.
di Alice Sella, pubblicato il 22 agosto 2024
