Narcis Miclaus

5 min di lettura

SQL Server, dalle fondamenta Lezione 39 / 40

sp_BlitzCache e sp_WhoIsActive: cosa sta succedendo PROPRIO ADESSO

sp_WhoIsActive di Adam Machanic è la procedura per la risposta in emergenza. Più sp_BlitzFirst per le wait stat puntuali.

Squilla il telefono alle 3 di notte. “Il database è lento. Tutto va in timeout.”

Apri il portatile, ti connetti, esegui una query. Ti dice esattamente cosa sta girando, chi blocca chi, quali query si stanno mangiando la CPU, quali sono in attesa sul disco, quali tengono i lock. Spendi i prossimi cinque minuti a killare la sessione giusta e la vita torna alla normalità.

Quella query è sp_WhoIsActive. È la procedura open source di Adam Machanic, ha 20 anni, ed è il singolo strumento di risposta in emergenza più utile nell’arsenale di un DBA. sp_BlitzFirst (parte del First Responder Kit) è la versione un filo più amichevole del team di Brent Ozar.

Installale entrambe. Poi ringraziami.

sp_WhoIsActive: il classico

Adam Machanic l’ha scritta verso il 2007. È il gold standard delle procedure “chi sta eseguendo query sul mio server proprio adesso”. Download: whoisactive.com o cerca “sp_WhoIsActive github.”

Installazione: scarica il file .sql, eseguilo contro master (o il tuo database DBA).

Chiamata di base:

EXEC sp_WhoIsActive;

Restituisce una riga per ogni sessione utente attiva, con le colonne:

  • session_id — lo SPID.
  • start_time — quando è partita questa request.
  • sql_text — cosa sta eseguendo (XML cliccabile, apre la query intera).
  • login_name / host_name / program_name — chi sta chiamando.
  • database_name.
  • wait_info — su cosa è in attesa, da quanto tempo.
  • CPU, reads, writes — uso di risorse fino a ora.
  • blocking_session_id — chi sta bloccando questa sessione (se qualcuno).
  • status — running, suspended, eccetera.

Ordina per CPU o reads desc; le righe in cima sono le tue query più pesanti.

Diagnosi in una riga:

  • wait_info mostra LCK_M_X(42) — in attesa che la sessione 42 rilasci un lock esclusivo.
  • blocking_session_id = 42 — lo conferma.
  • Esegui EXEC sp_WhoIsActive @session_id = 42 per vedere cosa sta facendo la 42.
  • Oppure killa la 42: KILL 42.

Parametri utili

  • @get_plans = 1 — include l’XML del piano d’esecuzione.
  • @get_locks = 1 — mostra tutti i lock tenuti da ciascuna sessione.
  • @find_block_leaders = 1 — ordina le sessioni in modo che il “block leader” (quello che causa una catena di blocking) sia in cima.
  • @sort_order = '[CPU] DESC' — scegli l’ordinamento.
  • @get_task_info = 2 — dettagli verbose sui task.

La mia chiamata abituale in emergenza:

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @get_plans          = 1,
    @get_locks          = 1;

Tutto quello che ti serve in una schermata.

Loggare sp_WhoIsActive su una tabella

Per il monitoraggio continuo:

-- Crea la tabella di destinazione
DECLARE @schema NVARCHAR(MAX);
EXEC sp_WhoIsActive @return_schema = 1, @schema = @schema OUTPUT;
SET @schema = REPLACE(@schema, '<table_name>', 'DBA.dbo.WhoIsActive_log');
EXEC (@schema);

-- Schedalo via Agent ogni 60 secondi
DECLARE @dest NVARCHAR(4000) = 'DBA.dbo.WhoIsActive_log';
EXEC sp_WhoIsActive @destination_table = @dest;

Adesso hai un log a rotazione di tutto ciò che è girato. Quando qualcuno chiede “cos’è successo alle 2:15?”, lo sai.

sp_BlitzFirst: il fratello del “proprio adesso”

L’equivalente di Brent nel First Responder Kit. Stile diverso da sp_WhoIsActive; entrambi sono utili.

USE DBA;
EXEC sp_BlitzFirst;

Gira per 5 secondi di default, campionando le wait stat in quella finestra. Ti dà la situazione puntuale invece della distribuzione storica delle wait stat.

L’output è in stile sp_Blitz, finding ordinati per priorità:

  • Top wait type attuali.
  • Query attualmente long-running.
  • Utilizzo CPU in questo momento.
  • Database con più attività.

@SinceStartup = 1 — invece di un campione di 5 secondi, mostra le statistiche cumulative dal riavvio del server.

@ExpertMode = 1 — più colonne, diagnosi più profonda.

La mia sequenza di triage di default quando squilla il telefono:

  1. sp_WhoIsActive @find_block_leaders = 1; — chi blocca chi?
  2. sp_BlitzFirst @ExpertMode = 1; — su cosa stiamo aspettando, collettivamente?
  3. Scegli il problema più grosso, agisci.

Dieci secondi di battuta sulla tastiera. Il novanta per cento delle volte la causa principale è ovvia da quelle due query.

Killare una sessione

Quando sp_WhoIsActive ti dice che la sessione 42 sta bloccando tutti:

KILL 42;

La sessione fa rollback della propria transazione in volo e si disconnette. Tutti quelli che bloccava si sbloccano. Problema risolto.

Avvertenza: KILL fa rollback della transazione. Se è una transazione grande che gira da un’ora, il rollback può prendere un’altra ora. Controlla sys.dm_exec_requests.percent_complete per il progresso del rollback.

Alternativa: KILL N WITH STATUSONLY; — riporta il progresso del rollback senza avviare un altro kill.

Non avere paura di KILL. È la risposta giusta per query impazzite, transazioni inchiodate e sessioni di un client morto che non ha mai chiuso la connessione.

I tre tipi di sessioni “zombie” che killerai regolarmente

  1. Sessioni sleeping con transazioni aperte. Qualcuno ha avviato un BEGIN TRAN in SSMS ed è andato a pranzo. La connessione dorme ma i lock sono tenuti. Killala.
  2. Sessioni suspended in attesa di lock per sempre. La causa principale è la sessione che le blocca; sp_WhoIsActive @find_block_leaders = 1 mostra il block leader. Killa il leader.
  3. Processi background che fanno cose strane. Raro. Di solito segnale di un problema più profondo. Non killare alla cieca; alcuni di loro sono thread interni di SQL Server.

Best practice di monitoraggio

  • sp_WhoIsActive ogni 30-60 secondi, loggato su una tabella. Ruota il log (tienine 7 giorni). Una delle mosse di monitoraggio col leverage più alto che puoi fare.
  • Alert su “sessioni bloccate per > N secondi.” Esegui sp_WhoIsActive @find_block_leaders = 1 dal check dell’alert; se c’è un block leader e la vittima è in attesa da > 30 secondi, suona la sveglia all’on-call.
  • Grafica CPU / wait stat da sys.dm_os_ring_buffers così hai visibilità su settimane, non solo sull’adesso.

Prova questo sulla tua macchina

-- 1. Installa sp_WhoIsActive da whoisactive.com
-- 2. Installa il First Responder Kit per sp_BlitzFirst

-- 3. Esegui una query che impieghi un po', in una sessione:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = Name + N' (updating)' WHERE CustomerId = 1;
-- Non committare. Lascia questa sessione aperta.

-- 4. In un'altra sessione:
EXEC sp_WhoIsActive @find_block_leaders = 1, @get_plans = 1;

-- 5. Vedi lo stato attuale delle wait
EXEC sp_BlitzFirst;

-- 6. Torna alla sessione 1 e committa (o rollback)
COMMIT;

In emergenza non hai modo di leggerti prima una lezione. Esercitati con questi strumenti in tempo di pace. La memoria muscolare è quello che ti salva alle 3 di notte.

Prossima, l’ultima lezione del corso: un health check di SQL Server di 30 minuti — la checklist ordinata che esegui su un server che non hai mai visto prima. Mette insieme tutto dalle lezioni 21 alla 40.

Cerca