Rivoluzionare il processo di identificazione degli indici ridondanti nei database di SQL Server

indice ridondante
indice che diventa inutile e non necessario a causa della presenza di un altro indice o insieme di indici.

Hai mai sentito il detto "troppo di una cosa buona può essere un male"? Bene, questo vale anche per gli indici di SQL Server. Voglio dire, certo, puoi creare tutti gli indici non raggruppati che vuoi, ma perché dovresti? È come cercare di impressionare la tua cotta indossando dieci strati di acqua di colonia: non funzionerà e peggiorerà solo le cose.

Doppio problema

I pericoli degli indici duplicati in SQL Server

In SQL Server è possibile creare indici duplicati sullo stesso oggetto. Mentre tutto, dalle chiavi di indice alle proprietà, può essere identico, questa pratica comporta diversi inconvenienti.

1. Gli indici duplicati occupano spazio di archiviazione aggiuntivo. E a chi piace sprecare spazio prezioso? Non noi! Eseguendo un'analisi di ridondanza dell'indice (parole fantasiose per l'analisi degli indici ridondanti), siamo stati in grado di identificare gli indici ridondanti e risparmiare quasi il 10% dello spazio di archiviazione. È come trovare uno scrigno del tesoro nascosto da 400 GB nel tuo database da 4 TB!
2. Gli indici ridondanti possono rallentare le istruzioni DML (inserimento, aggiornamento, eliminazione dei dati). Immagina di dover aggiornare più copie dello stesso indice ogni volta che apporti una modifica. Parla di una perdita di tempo! Ma non temere, l'eliminazione degli indici ridondanti può migliorare le prestazioni e far sembrare il tuo database come l'incredibile Hulk. Inoltre, risparmierai denaro sui core della CPU che puoi ridurre.
3. Ricostruire o riorganizzare gli indici può essere un vero problema con gli indici duplicati. Eliminando gli indici ridondanti, puoi accelerare questo processo e tornare a cose più importanti (come il binge-watching del tuo programma preferito).
4. Avere più indici sullo stesso oggetto può rendere più difficile per l'ottimizzatore di query svolgere il proprio lavoro. E nessuno vuole un ottimizzatore irritabile! Semplificando i tuoi indici, puoi semplificare la vita dell'ottimizzatore e migliorare le prestazioni delle query.

Il dilemma dell'indice in SQL Server

Gli indici possono essere considerati ridondanti anche se non sono identici. In un indice, le colonne sono divise in due categorie: colonne indice e colonne di inclusione.

Non giocare a Jenga con i tuoi indici: perché l'ordine è importante!

In SQL Server, l'ordine delle colonne dell'indice è fondamentale. SQL Server può utilizzare un indice per una scansione di intervallo solo se viene specificata la colonna più a sinistra, quindi solo se viene specificata la successiva colonna più a sinistra e così via. È come seguire una ricetta: non puoi saltare passaggi o aggiungere ingredienti nell'ordine sbagliato.

D'altra parte, l'ordine delle colonne non chiave (colonne di inclusione) nell'indice non ha alcuna importanza. È come preparare un panino: puoi mettere la lattuga prima del pomodoro o viceversa e non influirà sul gusto o sulla consistenza del panino. L'inclusione di colonne non chiave nell'indice può migliorare significativamente le prestazioni delle query perché Query Optimizer può individuare tutti i valori delle colonne all'interno dell'indice, con conseguente riduzione delle operazioni di I/O su disco.

Classificazione degli indici ridondanti

Esistono tre tipi di indici ridondanti:

• Indici duplicati: due indici hanno esattamente le stesse colonne chiave nello stesso ordine (cioè indici identici) con le stesse colonne di inclusione. Questo perché l'ordine delle colonne di inclusione non ha importanza.
  Ad esempio: entrambi gli indici hanno le stesse colonne chiave "ColumnA" e "ColumnB" nello stesso ordine e includono le stesse colonne non chiave "ColumnC" e "ColumnD", rendendole indici duplicati.
  CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnC, ColumnD);
CREATE INDEX idx2 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnD, ColumnC);


• Indici sovrapposti: un indice ha colonne chiave che formano un sottoinsieme ordinato a sinistra delle colonne chiave di un altro indice e ha un sottoinsieme di colonne non chiave di colonne non chiave di un altro indice che sono indici sovrapposti. Le colonne chiave nell'indice sovrapposto devono essere ordinate a sinistra, nel senso che sono elencate in ordine di importanza decrescente, con la colonna più importante che appare per prima. Questo perché SQL Server può utilizzare un indice per una scansione di intervallo solo se viene specificata la colonna più a sinistra, quindi solo se viene specificata la successiva colonna più a sinistra e così via.
  Ad esempio: in questo esempio, l'indice "idx1" ha colonne chiave "ColumnA" e "ColumnB", che è un sottoinsieme sinistro delle colonne chiave "ColumnA", "ColumnB" nell'indice "idx2". Pertanto, "idx2" si sovrappone a "idx1" e questi due indici sono indici sovrapposti.
  CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnX, ColumnY);
CREATE INDEX idx2 ON MyTable (ColumnA, ColumnB, ColumnD) INCLUDE (ColumnX, ColumnY, ColumnZ);


• Indici simili: indici che hanno colonne chiave identiche nello stesso ordine, ma colonne incluse diverse. Per risolvere indici simili, un indice deve essere aggiornato per contenere colonne di inclusione di entrambi gli indici ridondanti.
  Ad esempio: Indice della soluzione: in questo esempio, l'indice "idx2" ha colonne chiave "ColumnA" e "ColumnB", che è identico a "idx1". Le colonne di inclusione sono tuttavia diverse e l'indice risultante deve contenere colonne di inclusione come unione distinta di entrambe le colonne di inclusione dell'indice.
  CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnX, ColumnY);
CREATE INDEX idx2 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnP, ColumnQ);

CREATE INDEX idx1 ON MyTable (ColumnA, ColumnB) INCLUDE (ColumnX, ColumnY, ColumnP, ColumnQ);


La caccia all'efficienza in SQL Server

Mentre ci addentravamo nel misterioso mondo dei database di SQL Server, abbiamo scoperto uno strano fenomeno: gli indici inutilizzati! Questi indici erano come i fantasmi dei database del passato, che infestavano il nostro spazio di archiviazione e assorbivano la nostra preziosa potenza di calcolo con aggiornamenti degli indici non necessari.

Abbiamo notato alcuni seri problemi con i nostri database di grandi dimensioni: le nostre CPU e l'IO dei dati presentavano spesso picchi, il che non è mai un buon segno per un database integro. I colpevoli erano principalmente domande fastidiose, ma in alcuni casi le domande sembravano del tutto innocenti e gli indici sembravano a posto. Dopo un'immersione più approfondita, abbiamo scoperto che il vero problema erano più indici inutilizzati sulle colonne primarie. Questi indici ridondanti stavano causando una quantità eccessiva di aggiornamenti e creando più piani di query, provocando così il caos sulle prestazioni del nostro database.

Quindi, abbiamo iniziato la nostra ricerca per eliminare questi indici ridondanti, uno per uno. Ma ahimè, il processo di eliminazione degli indici ridondanti si è rivelato un vero e proprio snooze-fest. In qualità di amministratore di database, dare la caccia agli indici ridondanti è come giocare a un vero gioco di Where's Waldo. Devi setacciare un mare di script SQL, eseguire uno script che elenca tutti gli indici e le loro colonne indicizzate e incluse, quindi scorrere ogni singolo indice per identificare gli indici ridondanti. L'ironia non ci sfugge: stiamo cercando di aumentare l'efficienza del database facendo qualcosa che è incredibilmente inefficiente. E proprio quando pensi di aver identificato gli indici ridondanti, devi comunque eliminarli uno per uno, il che può farti indovinare la tua scelta professionale e sognare di scambiare il tuo laptop con una sedia a sdraio e una piña colada.

E oh ragazzo, ci aspettavamo una sorpresa: si è scoperto che i nostri problemi con il database erano appena iniziati! Abbiamo capito che sbarazzarsi di indici inutili era piuttosto in basso nell'elenco delle priorità per i nostri cari DBA. Era come cercare di convincerli a rinunciare alla loro amata dose di caffeina - quasi impossibile! Ma sapevamo che dovevamo rendere il processo più efficiente se volevamo avere qualche speranza di risolvere i problemi del nostro database.

Ma non temere, perché ci siamo ricordati del nostro fidato aiutante, snorql! Con il suo aiuto, abbiamo creato una metrica di ottimizzazione potenziata: la metrica di ridondanza dell'indice. Grazie a questo utile strumento, siamo stati in grado di semplificare il processo e sbarazzarci di quegli indici inutili in pochissimo tempo. Questo ragazzaccio ha creato i DBA così in fretta che potevano far sembrare un ghepardo un bradipo.

Cos'è lo snorql?

diagnosticare database come un capo!

Sviluppato presso udaan, snorql è un framework open source e completamente gratuito volto a diagnosticare, risolvere e ottimizzare le metriche SQL. snorql è collegabile e può essere facilmente implementato con qualsiasi database ed è qui per semplificarti la vita. Inizia con la nostra guida facile da seguire su " Iniziare con snorql ".

Ma non è tutto: Snorql identifica anche le tabelle e gli indici inutilizzati, offrendoti ancora più informazioni sui potenziali risparmi di spazio. Con le metriche di ottimizzazione di Snorql, puoi prendere con sicurezza decisioni informate su quali indici e tabelle rimuovere, riducendo il disordine e migliorando l'organizzazione nel tuo database.

Repository Snorql:
1. snorql (framework): https://github.com/udaan-com/snorql
2. interfaccia snorql: https://github.com/udaan-com/snorql-frontend

Se hai bisogno di ulteriori convincimenti, assicurati di dare un'occhiata al fantastico articolo snorql: diagnosticare database come un boss

Follie di indicizzazione

Lezione appresa nella ricerca dell'efficienza del database

Per affrontare gli indici ridondanti, abbiamo ideato un algoritmo per classificare i nostri indici. Sembra abbastanza semplice, vero? Basta confrontare e contrastare, e voilà: gli indici inutilizzati, duplicati, sovrapposti e simili verranno ordinati ordinatamente. Ma tieni i cavalli, amico mio, non è stata una passeggiata nel parco! Ci sono volute più iterazioni per perfezionare la nostra metrica e lungo il percorso abbiamo acquisito preziose informazioni.

1. Invito alla festa dei database di replica di lettura e replica geografica:
   ci siamo resi conto che solo perché esiste un indice nel database primario, non significa che venga trattato allo stesso modo nelle istanze di replica di lettura e replica geografica. E per rendere le cose ancora più complicate, un indice che viene usato pesantemente nel database Read-Replica o Geo-Replica potrebbe essere contrassegnato come indice inutilizzato nel database primario. Pertanto, per ottenere statistiche accurate per il nostro algoritmo di classificazione dell'indice, abbiamo dovuto raccogliere e riepilogare sia l'utilizzo dell'indice che gli aggiornamenti dell'indice in tutte le istanze.
2. Assicurarsi che gli indici univoci rimangano alla festa:
   quindi, ecco l'accordo: gli indici univoci e i vincoli UNIQUE sono come fratelli che impongono l'unicità allo stesso modo. Quando crei un vincolo UNIQUE, SQL Server è come un mago che crea un indice univoco dal nulla. E a causa di questa stregoneria, non puoi semplicemente eliminare indici univoci direttamente dal database. Quindi, per evitare incidenti, abbiamo dovuto escludere gli indici univoci dal nostro algoritmo di classificazione.
3. Come non eseguire UX:
   nella prima versione della nostra analisi di ridondanza dell'indice, abbiamo provato a raggruppare gli indici ridondanti a livello di indice. Ha reso difficile comprendere le relazioni tra gli indici padre e figlio senza guardare l'intera tabella. Inoltre, l'algoritmo che abbiamo sviluppato era complicato come una partita a scacchi in 3D e ci siamo subito resi conto che non era quella la strada da percorrere.

5. Come gestire indici simili
Quindi, immagina di avere due amici che si assomigliano, ma uno di loro ha un bel cappello e l'altro ha un paio di occhiali stravaganti. Sono come indici simili: duplicati o sovrapposti per colonne indicizzate, ma con colonne di inclusione diverse. Ma ecco il trucco: non puoi semplicemente sostituirli come un paio di scarpe senza causare seri problemi di prestazioni. Anche eliminarli non è la risposta: è come rompere con uno dei tuoi amici solo perché entrambi hanno lo stesso nome! L'approccio migliore consiste nell'aggiornare uno degli indici per avere tutte le colonne di inclusione di entrambi, quindi salutare l'altro. In questo modo, possiamo risparmiare su indici simili e mantenere la pace tra i nostri amici del database.

Va bene gente, è ora di rimboccarsi le maniche e scendere e sporcarsi! Abbiamo identificato quei fastidiosi casi limite e ora è il momento di tuffarci a capofitto nella fase di implementazione.

Allora, chi è pronto a sporcarsi le mani? Facciamo questa cosa!

Immergersi nella metrica di ridondanza dell'indice

È tempo di concentrarsi sul mago dietro questo, l'algoritmo!

Nota: Index Redundancy Analysis è attualmente disponibile per i database SQL Server e l'implementazione è specifica per questo sistema di database.

Problema Github ✅ #79 Nuova metrica — Metrica di ridondanza dell'indice Github PR ⛓ #84 [Nuova metrica] Metrica di ridondanza dell'indice

Ho suddiviso l'algoritmo in passaggi:

1. Ottieni dati dettagliati di tutti gli indici in un database utilizzando la seguente query sql

3. Raggruppare gli indici per tabella e iterare su ciascuna tabella, filtrare gli indici di cui name == NULLfiltrare gli indici heap e ordinare in ordine decrescente in base al numero di colonne indicizzate.
Manteniamo anche un elenco di indici che devono essere saltati. Questi includono gli indici che sono già classificati o gli indici univoci.

4. Identificare gli indici inutilizzati. Qui, se l'utilizzo è inferiore a 10, lo consideriamo un indice inutilizzato. Abbiamo mantenuto questa piccola soglia perché potrebbe essere possibile che un indice venga utilizzato durante l'esecuzione di query ad hoc.

5. Identificazione degli indici univoci. Gli indici univoci vengono creati intenzionalmente sulle colonne per mantenere l'univocità, quindi saltiamo la loro classificazione nell'analisi. Mostriamo indici univoci a livello di tabella per una migliore analisi e visibilità.

6. Pubblica questo, iteriamo su ogni indice e lo analizziamo per trovare indici ridondanti

UN. Classifica indici duplicati:
la classificazione degli indici duplicati è semplice. Le colonne indicizzate e le colonne di inclusione devono essere le stesse e le colonne degli indici devono essere nello stesso ordine

B. Classifica indici sovrapposti:
le colonne indicizzate dell'indice secondario devono essere un sottoinsieme basato a sinistra delle colonne indicizzate principali nello stesso ordine e le colonne di inclusione devono essere le stesse.

C. Classifica indici simili:
le colonne indicizzate devono essere identiche, mentre le colonne di inclusione possono essere diverse

Cosa ne è uscito udaan?

Più risparmi, meno stress e amministratori di database felici!

È venuto fuori che l'analisi della ridondanza dell'indice non era solo un piacere per i DBA, ma per l'intera organizzazione. Abbiamo alcuni succosi vantaggi di cui vantarci:

1. Cha Ching! Abbiamo risparmiato qualche soldo ridimensionando i nostri database senza problemi di prestazioni. Il nostro picco di calcolo del database è diminuito in media dell'8% e lo spazio di archiviazione si è ridotto del 10%. Ad esempio, abbiamo ridotto un database da 32 vCore a uno da 24 vCore e bam, abbiamo risparmiato il 22% sui costi! Chi ha bisogno comunque di tutti quei core extra?

3. I DBA sono al settimo cielo. Abbiamo persino sentito voci su di loro che sbaciucchiavano i loro monitor con la metrica di ridondanza dell'indice aperta sullo schermo. Ehi, non stiamo giudicando - se è quello che serve per renderli felici, così sia!