Narcis Miclaus

5 min di lettura

SQL Server, dalle fondamenta Lezione 38 / 40

sp_BlitzIndex e sp_BlitzCache: la scansione completa

Audit approfondito degli indici con sp_BlitzIndex. Revisione dei piani di query con sp_BlitzCache. Leggere l'output, fare triage delle 50 cose che trovano.

sp_Blitz è la panoramica server-wide. sp_BlitzIndex e sp_BlitzCache sono gli strumenti specialistici: il primo fa l’audit degli indici, il secondo fa l’audit delle performance delle query. Insieme ti dicono circa l’80% di quello che non va nelle performance di un dato database.

Oggi scaviamo in entrambi.

sp_BlitzIndex: che aspetto hanno i tuoi indici

USE Runehold;
EXEC sp_BlitzIndex;

Quattro modalità, tramite il parametro @Mode:

  • @Mode = 0 (default) — Diagnose — trova i problemi.
  • @Mode = 1 — Summarize — statistiche per indice.
  • @Mode = 2 — Detail — ogni indice, ogni colonna, ogni stat.
  • @Mode = 3 — Missing indexes — il report “dovresti aggiungerli”.
  • @Mode = 4 — All — sputa fuori tutto.

Nel quotidiano usi @Mode = 0. I deep-dive occasionali usano @Mode = 4.

L’output di diagnose

Una riga per finding. Colonne:

  • Priority — 1 (urgente) a 250 (informativo).
  • Finding — il problema.
  • URL — spiegazione (come sp_Blitz).
  • Database Name / Schema Name / Table Name.
  • Details: schema.table.index(keys) / includes.
  • Definition: [Property1], [Property2] — qualsiasi contesto rilevante.
  • Secret columns — colonne nascoste che l’optimizer conosce ma tu forse no (la chiave clustered appiccicata sugli indici non clustered, ad esempio).
  • Usage: Reads vs Writes; Size.
  • More Info — come scavare più a fondo (di solito “esegui sp_BlitzIndex @Mode=2 @TableName='...'” per quella tabella specifica).

I finding sono prioritizzati per un motivo. Sistema prima i P1.

Finding comuni di sp_BlitzIndex

P1 — Indici multipli con chiavi duplicate. Hai due indici su (CustomerId, OrderDate). Droppane uno. Vittoria facile, risparmio immediato di spazio + costo di scrittura.

P10 — Indici inutilizzati. Un indice con migliaia di scritture e zero letture. Candidato al drop. Verifica prima: user_seeks + user_scans + user_lookups = 0 per un periodo significativo (non solo da quando il server si è riavviato l’ultima volta).

P20 — Heap (nessun indice clustered). Quasi sempre sbagliato. Aggiungi un indice clustered. Lezione 21 per la teoria.

P50 — Indici mancanti suggeriti. Dalla DMV degli indici mancanti. Valuta, non creare alla cieca.

P100 — Indici non aggiornati di recente. Non sono stati riorganizzati/ricostruiti. Probabilmente va bene; gli script di Ola gestiscono la cosa (lezione 35).

P150 — Statistiche mancanti o stantie. UPDATE STATISTICS ... WITH FULLSCAN e riesegui sp_BlitzIndex per vederlo sparire.

Modalità detail per una tabella specifica

EXEC sp_BlitzIndex @Mode = 2, @DatabaseName = 'Runehold', @SchemaName = 'Sales', @TableName = 'Orders';

Mostra ogni indice, ogni colonna, ogni stat. Più gli indici non clustered suggeriti basati sulle DMV degli indici mancanti. Più quali query nella plan cache beneficerebbero di ciascun indice suggerito.

È quello che guardi quando qualcuno dice “perché questa tabella è lenta?” — ottieni un quadro completo in un singolo result set.

sp_BlitzCache: che aspetto hanno le tue query

USE DBA;
EXEC sp_BlitzCache;

Fa il parsing della plan cache (come le query DMV della lezione 36, ma molto più intelligente). Mostra le top query secondo una metrica specificata, annotate con ogni anti-pattern noto rilevato in ciascun piano.

Parametro @SortOrder — scegli la metrica:

  • 'CPU' (default) — più CPU bruciata.
  • 'Reads' — più letture logiche.
  • 'Writes' — più scritture.
  • 'Duration' — tempo di parete.
  • 'Executions' — volte eseguita.
  • 'Recent Compilations' — query compilate di recente (utile per individuare il parameter sniffing).
  • 'Memory Grant' — query che si mangiano più memoria.

L’output

Una riga per top query. Colonne:

  • Query — il testo SQL (troncato).
  • Query Plan — clicca per vederlo in SSMS.
  • Executions / Total CPU / Avg CPU / Total Reads / Avg Reads eccetera.
  • Warnings — lista separata da virgole dei problemi rilevati nel piano.

La colonna warnings è oro. Ogni warning ti dice qualcosa di specifico:

  • Implicit Conversion Affecting Cardinality — la tua WHERE ha una conversione di tipo implicita che disabilita gli index seek.
  • Key Lookups: N% — la query fa l’N% del suo costo in key lookup. Tempo di un indice covering.
  • Missing Index: high value — il piano suggerisce un indice mancante che aiuterebbe parecchio.
  • Parameter Sniffing — questa query ha runtime drasticamente diversi tra le esecuzioni.
  • Spills to tempdb — sort/hash non sono entrati in memoria.
  • Unparameterized Query — un valore literal viene compilato nel testo del piano; ogni valore distinto ha il suo piano. Di solito spreca plan cache.

Sistemare un paio dei top offender spesso ti dà il 30-50% di riduzione di CPU sul server.

Una sessione d’esempio

Su un server nuovo che ti hanno chiesto di tunare:

USE DBA;

-- Top 10 consumatori di CPU
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'CPU';

-- Top 10 per letture (mangia-IO)
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Reads';

-- Top 10 con memory grant
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Memory Grant';

Quattro minuti di lavoro, le top 30 query costose della cache, con ogni warning annotato. Leggi quella in cima. Sistemala (di solito: aggiungi un indice covering, oppure riscrivi per rimuovere una conversione implicita). Passa alla prossima.

Runehold ha tunato il proprio database di produzione dal 65% di CPU media al 22% in un mese sistemando i top 15 finding di sp_BlitzCache, uno alla settimana. Tutto qua. Niente magia, solo sistematicità.

Eseguirli regolarmente

Schedula sp_Blitz, sp_BlitzIndex e sp_BlitzCache settimanalmente via Agent, scarica i risultati su tabelle di log e rivedi i cambiamenti. I nuovi finding si guadagnano l’attenzione; i vecchi restano risolti.

EXEC sp_Blitz
    @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';

EXEC sp_BlitzCache @Top = 100,
    @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzCacheResults';

EXEC sp_BlitzIndex @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzIndexResults';

Adesso hai uno storico. Graficalo. Puoi rispondere “stiamo migliorando o peggiorando?” con dati invece che a tentoni.

Personalizzare sp_BlitzCache

Parametri extra utili:

  • @MinimumExecutionCount — ignora le query con meno di N esecuzioni. Aiuta a filtrare il rumore di query ad-hoc usa-e-getta.
  • @StoredProcName — analizza solo una procedura.
  • @ExportToExcel = 1 — formatta l’output per incollarlo in Excel.
  • @IgnoreSqlHandles — lista separata da virgole di sql_handle da escludere (query rumorose che hai già sistemato o lente di proposito).

Prova questo sulla tua macchina

USE Runehold;

-- 1. Audit degli indici
EXEC sp_BlitzIndex;

-- 2. Dettaglio su una tabella specifica
EXEC sp_BlitzIndex @Mode = 2, @SchemaName = 'Sales', @TableName = 'Orders';

-- 3. Suggerimenti di indici mancanti
EXEC sp_BlitzIndex @Mode = 3;

USE DBA;

-- 4. Top 10 query CPU
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'CPU';

-- 5. Top query con problemi di parameter sniffing
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Recent Compilations';

-- 6. Query che vanno in spool su tempdb
EXEC sp_BlitzCache @Top = 20, @SortOrder = 'Memory Grant';

Scegli un finding. Clicca l’URL. Leggi la spiegazione. Sistema o documenta. Vai oltre.

Prossimo: il fratello di sp_BlitzCache per “cosa sta succedendo PROPRIO ADESSO” — sp_WhoIsActive e sp_BlitzFirst. Gli strumenti che esegui quando squilla il telefono.

Cerca