LLI Wiki — My Digital Knowledge Base

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

  1. Activity Monitor (SSMS)
  2. Rapports standard du serveur
  3. Rapports standard de la base

Remarques

Mastodon LLIMastodon SocialFosstodon