SQL Server : trouver les transactions longues et les requêtes en cours
Requêtes DMV pour repérer les transactions qui retiennent le journal et les requêtes les plus longues.
Requêtes pour trouver les transactions ouvertes qui retiennent le journal de transactions, et les requêtes qui consomment le plus de temps d'exécution. Les articles de référence sur l'alerte de requêtes longues sont cités en fin de page.
Transactions longues et journal utilisé
SELECT b.session_id 'Session ID'
, CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name'
, c.command
, Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
((
CASE c.statement_end_offset
wHEN -1 THEN Datalength(st.TEXT)
ELSE c.statement_end_offset
END
-
c.statement_start_offset ) / 2 ) + 1) statement_text
, Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(
Object_schema_name(st.objectid,
st.dbid)) +
N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') command_text
, c.wait_type
, c.wait_time
, a.database_transaction_log_bytes_used / 1024.0 / 1024.0 'MB used'
, a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0 'MB used system'
, a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0 'MB reserved'
, a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 'MB reserved system'
, a.database_transaction_log_record_count 'Record count'
FROM
sys.dm_tran_database_transactions a
JOIN sys.dm_tran_session_transactions b ON a.transaction_id = b.transaction_id
JOIN sys.dm_exec_requests c CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st ON b.session_id = c.session_id
ORDER BY 'MB used' DESC
Plan d'exécution et statistiques d'une requête à partir de son handle
Remplacer 0x<SQL_HANDLE> par un sql_handle récupéré dans sys.dm_exec_requests ou sys.dm_exec_query_stats.
SELECT qp.query_plan, sql_handle, plan_handle, creation_time, last_execution_time, plan_generation_num, execution_count
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) AS qp
WHERE eqs.sql_handle=0x<SQL_HANDLE>
ORDER BY eqs.last_execution_time DESC
Requêtes en cours avec leur plan
SELECT
r.session_id
, r.start_time
, TotalElapsedTime_ms = r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName = DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, t.[text] AS [executing batch]
, SUBSTRING(
t.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2
) AS [executing statement]
, p.query_plan
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
r.total_elapsed_time DESC;
Autres outils
- Activity Monitor (SSMS)
- Rapports standard du serveur
- Rapports standard de la base
Remarques
- Le
ORDER BY 'MB used'trie sur une chaîne littérale et non sur la colonne. Pour classer par volume de journal, utiliserORDER BY a.database_transaction_log_bytes_used DESC. - Le
sql_handleest propre à chaque instance et à chaque plan en cache : il ne se réutilise pas d'un serveur à l'autre. - Articles de référence : SQL Server long running queries email alert et Set long running transaction alert.