SQL Server: reperire la lista delle query in esecuzione, con durata ed execution plan

Quando si lavora ad un’applicazione o sull’analisi dei dati con SQL Server (Azure SQL incluso), può capitare di incontrare operazioni molto lente o query che vengono bloccate o rallentate da altre operazioni in corso sul DB. In questi casi torna utile quindi verificare quali sono le query in esecuzione in un determinato momento, da quanto stanno girando e, visto che ci siamo, anche  avere il relativo execution plan per indagare eventuali problemi prestazionali.

La query seguente risponde proprio a questa esigenza:

SELECT
    req.session_id
    ,req.start_time
    ,req.status
    ,req.command
    ,req.total_elapsed_time / 1000 AS elapsed_secs
    ,req.total_elapsed_time / 60000 AS elapsed_mins
    ,sqltext.text AS batch_text
    ,SUBSTRING(sqltext.text, (req.statement_start_offset / 2) + 1,
    CASE
        WHEN statement_end_offset = -1 OR
        statement_end_offset = 0 THEN (DATALENGTH(sqltext.text) - req.statement_start_offset / 2) + 1
    ELSE (req.statement_end_offset - req.statement_start_offset) / 2 + 1
    END) AS statement_text
    ,execplan.query_plan
FROM sys.dm_exec_requests req
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext --get batch text
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS execplan --get batch exec plan
WHERE session_id != @@spid --filter out this query

Oltre alle colonne ovvie ed auto-esplicative, vale la pena menzionare queste:

• batch_text – è il testo SQL dell’intero batch (in caso di una stored procedure, ad esempio) o di una query singola.
• statement_text – in caso di un batch composto da più istruzioni, è il testo di quella attualmente in esecuzione. Nel caso di un batch composto da una query singola, corrisponde a batch_text.
• query_plan – è l’XML dell’execution plan per l’intero batch. Cliccandolo in SSMS, si apre nell’exec plan viewer grafico. Se il batch è composto da più istruzioni, contiene gli exec plan di  tutte.

Bonus: qualora abbiate individuato una query in esecuzione che volete terminare, basta utilizzare l’istruzione KILL con il session_id restituito dalla query precedente:

KILL <session_id>

Riferimenti:

• sys.dm_exec_requests
• sys.dm_exec_sql_text
• dm_exec_query_plan

Questo articolo è disponibile anche in: English