Il Query Store è il punto di partenza

Quando arriva la segnalazione “il database è lento”, la domanda d’esame non è come si ottimizza, ma dove si guarda. Il Query Store conserva per singolo database il testo delle query, i piani compilati, le statistiche di esecuzione e le attese: è ciò che permette di dire “questa query ieri costava X e oggi dieci volte tanto”, cioè di riconoscere una plan regression e di forzare il piano precedente. Le DMV mostrano lo stato corrente e si azzerano al riavvio o al failover; il Query Store è persistente perché scrive dentro il database e lo segue in restore, geo-replication e failover. È anche il presupposto dell’automatic tuning: senza quella storia, FORCE_LAST_GOOD_PLAN non ha nulla su cui decidere.

Blocking, deadlock e DMV

Una catena di blocco e un deadlock non sono la stessa cosa, ed è una confusione che l’esame sfrutta. Nel blocking le sessioni attendono finché la transazione capofila non chiude: il motore non interviene, la query resta appesa e compaiono attese LCK_M_*. Si risale al capofila seguendo blocking_session_id in sys.dm_exec_requests fino alla sessione che blocca senza essere bloccata; di norma una transazione lunga o lasciata aperta dall’applicazione. Nel deadlock il motore rileva il ciclo, sceglie una vittima in base al costo di rollback e restituisce l’errore 1205: la risposta corretta è retry logic applicativa, transazioni più corte e accesso agli oggetti nello stesso ordine.

Il livello di isolamento cambia la fisionomia del problema: con read committed snapshot isolation (RCSI) i lettori non bloccano gli scrittori, al prezzo del version store; SERIALIZABLE moltiplica i lock. Le DMV completano il quadro: statistiche aggregate per isolare le query più costose per CPU, I/O logico o durata; viste sulle attese per capire cosa aspetta il workload (I/O, memory grant, log rate governance, parallelismo); viste su lock e transazioni attive per il caso singolo.

Indici: aggiungerne uno costa

Gli indici mancanti suggeriti dal motore vengono da una struttura di supporto dell’ottimizzatore, non da un’analisi del workload: ignorano gli indici esistenti, non ordinano bene le colonne chiave, non pesano il costo in scrittura e non sono deduplicati fra loro. Il criterio è consolidarli, confrontarli con l’esistente e valutare l’impatto sulle scritture: ogni indice va mantenuto a ogni INSERT, UPDATE e DELETE, occupa spazio e finisce nei backup. Nella potatura vanno distinti due casi: un indice duplicato ha le stesse colonne chiave nello stesso ordine di un altro e si elimina con poco rischio; un indice non utilizzato si giudica dalle statistiche d’uso, che si azzerano a ogni riavvio o failover — leggerle dopo poche ore di uptime porta a cancellare indici che servono a fine mese.

Piani di esecuzione e diagnosi automatica

Il piano stimato riporta solo le previsioni dell’ottimizzatore; quello effettivo aggiunge righe reali, spill in tempdb e memory grant. Il segnale più utile è la differenza fra righe stimate ed effettive: una cardinalità sbagliata spiega join scelti male, sort che finiscono su disco e grant sproporzionati, e nasce da statistiche vecchie, predicati non SARGable o parameter sniffing. Scan contro seek non è una regola morale: un seek seguito da migliaia di key lookup può costare più di una scansione, mentre una conversione implicita di tipo trasforma in scan un seek possibile. Le funzionalità IQP correggono a runtime parte di questi difetti senza riscrivere le query. Intelligent Insights, all’estremo opposto della scala di automazione, non espone metriche ma diagnosi già interpretate del degrado, con causa probabile e impatto, verso il logging diagnostico.

Trappole tipiche d’esame

  • Capire se una query è peggiorata rispetto alla settimana scorsa → Query Store: le DMV danno solo lo stato corrente e si azzerano al failover; confronto storico e forcing del piano precedente stanno lì.
  • Query appesa senza errori e sessioni che si accodano → blocking, non deadlock: il deadlock si risolve da sé con una vittima e l’errore 1205, la catena di blocco resta finché non si chiude la transazione capofila.
  • Dieci indici mancanti “ad alto impatto” suggeriti → non applicarli tutti: i suggerimenti ignorano indici esistenti, sovrapposizioni e costo in scrittura.
  • Job di manutenzione degli indici da pianificare su Azure SQL Database → elastic job: SQL Server Agent lì non esiste; su Managed Instance e su SQL Server in VM è invece la scelta naturale.
  • Serve una diagnosi già interpretata, non metriche grezze → Intelligent Insights: DMV ed Extended Events lasciano tutta l’interpretazione al DBA.
  • Indice “mai usato” da eliminare → prima verificare l’uptime: i contatori d’uso ripartono da zero dopo riavvii e failover, frequenti nei servizi PaaS, e i carichi periodici possono non essere ancora passati.