Il ciclo del performance tuning

Il performance tuning su SQL non è un intervento una tantum ma un ciclo: misurare, individuare il collo di bottiglia, applicare una modifica mirata, verificare. Lo strumento centrale è Query Store, che cattura e persiste lo storico delle query, i loro execution plan e le runtime statistics (durata, CPU, letture logiche, memoria). Query Store è abilitato di default su Azure SQL Database e serve a individuare i regressed queries — query diventate più lente dopo un cambio di piano — e, quando serve, a fare plan forcing per costringere l’optimizer a riusare un piano noto come buono. È anche la base dell’automatic tuning, che può forzare l’ultimo piano valido in autonomia.

Quando Query Store segnala una query problematica, l’analisi scende sull’execution plan. Distingui sempre l’estimated plan (previsione, senza esecuzione) dall’actual plan (con conteggi reali di righe). I segnali da riconoscere: scan al posto di seek, key lookup ripetuti, sort o hash con spill in tempdb, e forti divergenze fra righe stimate ed effettive — sintomo tipico di statistiche obsolete o di parameter sniffing.

Indici: leva primaria, ma con trade-off

L’indice giusto è spesso la correzione con il ritorno maggiore. Un clustered index definisce l’ordine fisico dei dati; i nonclustered index aggiungono strutture di ricerca. Un covering index (con colonne aggiuntive in INCLUDE) evita i key lookup servendo la query interamente dall’indice. Il compromesso da ricordare: ogni indice accelera le letture ma rallenta INSERT/UPDATE/DELETE e occupa storage, quindi non si indicizza tutto. Per carichi analitici su grandi volumi valuta un columnstore index invece di molti indici rowstore. I missing index suggeriti da execution plan e DMV sono spunti, non prescrizioni: vanno validati contro il carico reale.

Concorrenza e isolation level

Gli isolation level regolano il compromesso fra consistenza e concorrenza, controllando quali fenomeni sono ammessi: dirty read, non-repeatable read e phantom read. READ COMMITTED è il default e, con il locking classico, usa lock condivisi di breve durata. REPEATABLE READ e SERIALIZABLE aumentano la protezione trattenendo più lock, riducendo la concorrenza e aumentando il rischio di blocking. SNAPSHOT usa invece il row versioning (version store in tempdb): i reader vedono una versione consistente senza bloccare i writer, al prezzo di consumo di tempdb.

Attenzione alla distinzione più insidiosa. SNAPSHOT è un isolation level impostabile a livello di sessione con SET TRANSACTION ISOLATION LEVEL (previa opzione ALLOW_SNAPSHOT_ISOLATION a livello di database). READ_COMMITTED_SNAPSHOT (RCSI) invece NON è un isolation level di sessione: è un’opzione di database che cambia il comportamento di READ COMMITTED facendogli usare il row versioning al posto dei lock. Su Azure SQL Database RCSI è attivo di default.

Diagnosticare blocking e deadlock

Blocking e deadlock sono problemi diversi. Il blocking è transitorio: una transazione trattiene un lock che un’altra attende, e si risolve quando la prima rilascia. Lo diagnostichi con le DMV — sys.dm_exec_requests (colonna blocking_session_id), sys.dm_tran_locks, sys.dm_os_waiting_tasks — per risalire alla catena di attesa e alla query bloccante.

Il deadlock è invece un’attesa ciclica: due transazioni trattengono ciascuna un lock che serve all’altra e nessuna può proseguire. SQL lo rileva automaticamente, sceglie una deadlock victim (in base al costo di rollback o a DEADLOCK_PRIORITY) e la annulla con l’errore 1205, lasciando l’altra procedere. Il deadlock graph si legge dagli Extended Events: la sessione system_health cattura i deadlock di default. Mitigazioni tipiche: accedere agli oggetti in ordine coerente fra le transazioni, tenere le transazioni brevi, aggiungere indici per ridurre le righe bloccate e adottare snapshot/RCSI per abbattere il blocking fra reader e writer.

Trappole tipiche d’esame

  • Abilitare lo snapshot sul READ COMMITTED di default → RCSI, opzione di database: READ_COMMITTED_SNAPSHOT si attiva con ALTER DATABASE, non con SET TRANSACTION ISOLATION LEVEL; SNAPSHOT invece è l’isolation level di sessione.
  • Query in attesa senza errore 1205 → è blocking, non deadlock: cerca il blocking_session_id nelle DMV, non un deadlock graph; il deadlock verrebbe risolto da solo con una victim.
  • Piano “corretto” ma stime sbagliate → actual execution plan: l’estimated plan non mostra le divergenze righe stimate/effettive; servono l’actual plan e statistiche aggiornate.
  • Query lenta dopo un deploy → Query Store e plan forcing: individua il regressed plan e forza l’ultimo piano buono, invece di riscrivere subito la query.
  • Aggiungere indici finché “va veloce” → occhio al costo in scrittura: ogni indice rallenta le DML e occupa storage; un covering index mirato batte molti indici ridondanti.