Afficher l'historique des requêtes SQL
Vous n'êtes plus certains d'avoir exécuter certaines requêtes et vous voulez vérifier. Il n'y a pas juste dans un interpréteur de commande que c'est possible. La base de données SQL Server le permet également.
Affiche tous l'historique
Si vous souhaitez afficher l'historique sans trop de détail, vous devez exécutez la requête SQL suivante :
- SELECT t.[text], s.last_execution_time
- FROM sys.dm_exec_cached_plans AS p
- INNER JOIN sys.dm_exec_query_stats AS s
- ON p.plan_handle = s.plan_handle
- CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
- ORDER BY s.last_execution_time DESC;
Recherche dans l'historique
L'historique est souvent très volumineux et il difficile de trouver quelque chose de précis. Toutefois, il y a une solution à se problème. Il suffit d'ajouter un champ de recherche pour trouver la requête d'historique que vous souhaitez retrouver en remplaçant marecherche :
- SELECT t.[text], s.last_execution_time
- FROM sys.dm_exec_cached_plans AS p
- INNER JOIN sys.dm_exec_query_stats AS s
- ON p.plan_handle = s.plan_handle
- CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
- WHERE t.[text] LIKE N'%marecherche%'
- ORDER BY s.last_execution_time DESC;
Affiche tous l'historique avec les statistiques
Dans certaines situations, vous aurez besoin de savoir le temps d'exécution et les détails techniques sur les requêtes exécuté, vous tapez donc la requête SQL suivante :
- SELECT top(100)
- creation_time,
- last_execution_time,
- execution_count,
- total_worker_time/1000 as CPU,
- convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
- qs.total_elapsed_time/1000 as TotDuration,
- convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
- total_logical_reads as [Reads],
- total_logical_writes as [Writes],
- total_logical_reads+total_logical_writes as [AggIO],
- convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
- [sql_handle],
- plan_handle,
- statement_start_offset,
- statement_end_offset,
- plan_generation_num,
- total_physical_reads,
- convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
- convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
- convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
- query_hash,
- query_plan_hash,
- total_rows,
- convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
- total_dop,
- convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
- total_grant_kb,
- convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
- total_used_grant_kb,
- convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
- total_ideal_grant_kb,
- convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
- total_reserved_threads,
- convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
- total_used_threads,
- convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
- CASE
- WHEN sql_handle IS NULL then ' '
- ELSE(substring(st.text,(qs.statement_start_offset+2)/2,(
- CASE
- WHEN qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
- ELSE qs.statement_end_offset
- END - qs.statement_start_offset)/2 ))
- END AS query_text,
- db_name(st.dbid) as database_name,
- object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) AS [object_name],
- sp.[query_plan]
- FROM sys.dm_exec_query_stats AS qs with(readuncommitted)
- CROSS apply sys.dm_exec_sql_text(qs.[sql_handle]) AS st
- CROSS apply sys.dm_exec_query_plan(qs.[plan_handle]) AS sp
Recherche dans l'historique avec les statistiques
L'historique avec statistiques est souvent très volumineux et il difficile de trouver quelque chose de précis. On peut trouver la requête d'historique que vous souhaitez en remplaçant marecherche :
- SELECT top(100)
- creation_time,
- last_execution_time,
- execution_count,
- total_worker_time/1000 as CPU,
- convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
- qs.total_elapsed_time/1000 as TotDuration,
- convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
- total_logical_reads as [Reads],
- total_logical_writes as [Writes],
- total_logical_reads+total_logical_writes as [AggIO],
- convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
- [sql_handle],
- plan_handle,
- statement_start_offset,
- statement_end_offset,
- plan_generation_num,
- total_physical_reads,
- convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
- convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
- convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
- query_hash,
- query_plan_hash,
- total_rows,
- convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
- total_dop,
- convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
- total_grant_kb,
- convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
- total_used_grant_kb,
- convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
- total_ideal_grant_kb,
- convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
- total_reserved_threads,
- convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
- total_used_threads,
- convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
- case
- when sql_handle IS NULL then ' '
- else(substring(st.text,(qs.statement_start_offset+2)/2,(
- case
- when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
- else qs.statement_end_offset
- end - qs.statement_start_offset)/2 ))
- end as query_text,
- db_name(st.dbid) as database_name,
- object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
- sp.[query_plan]
- FROM sys.dm_exec_query_stats as qs with(readuncommitted)
- CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) as st
- CROSS APPLY sys.dm_exec_query_plan(qs.[plan_handle]) as sp
- WHERE st.[text] LIKE '%marecherche%'
Voir également
Dernière mise à jour : Mercredi, le 6 octobre 2021