Section courante

A propos

Section administrative du site

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 :

  1. SELECT t.[text], s.last_execution_time
  2. FROM sys.dm_exec_cached_plans AS p
  3. INNER JOIN sys.dm_exec_query_stats AS s
  4.    ON p.plan_handle = s.plan_handle
  5. CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
  6. 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 :

  1. SELECT t.[text], s.last_execution_time
  2. FROM sys.dm_exec_cached_plans AS p
  3. INNER JOIN sys.dm_exec_query_stats AS s
  4.    ON p.plan_handle = s.plan_handle
  5. CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
  6. WHERE t.[text] LIKE N'%marecherche%'
  7. 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 :

  1. SELECT  top(100)
  2.         creation_time,
  3.         last_execution_time,
  4.         execution_count,
  5.         total_worker_time/1000 as CPU,
  6.         convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
  7.         qs.total_elapsed_time/1000 as TotDuration,
  8.         convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
  9.         total_logical_reads as [Reads],
  10.         total_logical_writes as [Writes],
  11.         total_logical_reads+total_logical_writes as [AggIO],
  12.         convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
  13.         [sql_handle],
  14.         plan_handle,
  15.         statement_start_offset,
  16.         statement_end_offset,
  17.         plan_generation_num,
  18.         total_physical_reads,
  19.         convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
  20.         convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
  21.         convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
  22.         query_hash,
  23.         query_plan_hash,
  24.         total_rows,
  25.         convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
  26.         total_dop,
  27.         convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
  28.         total_grant_kb,
  29.         convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
  30.         total_used_grant_kb,
  31.         convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
  32.         total_ideal_grant_kb,
  33.         convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
  34.         total_reserved_threads,
  35.         convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
  36.         total_used_threads,
  37.         convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
  38.         CASE 
  39.             WHEN sql_handle IS NULL then ' '
  40.             ELSE(substring(st.text,(qs.statement_start_offset+2)/2,(
  41.                 CASE
  42.                     WHEN qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
  43.                     ELSE qs.statement_end_offset    
  44.                 END - qs.statement_start_offset)/2  ))
  45.         END AS query_text,
  46.         db_name(st.dbid) as database_name,
  47.         object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) AS [object_name],
  48.         sp.[query_plan]
  49. FROM sys.dm_exec_query_stats AS qs with(readuncommitted)
  50. CROSS apply sys.dm_exec_sql_text(qs.[sql_handle]) AS st
  51. 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 :

  1. SELECT  top(100)
  2.         creation_time,
  3.         last_execution_time,
  4.         execution_count,
  5.         total_worker_time/1000 as CPU,
  6.         convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
  7.         qs.total_elapsed_time/1000 as TotDuration,
  8.         convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
  9.         total_logical_reads as [Reads],
  10.         total_logical_writes as [Writes],
  11.         total_logical_reads+total_logical_writes as [AggIO],
  12.         convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
  13.         [sql_handle],
  14.         plan_handle,
  15.         statement_start_offset,
  16.         statement_end_offset,
  17.         plan_generation_num,
  18.         total_physical_reads,
  19.         convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
  20.         convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
  21.         convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
  22.         query_hash,
  23.         query_plan_hash,
  24.         total_rows,
  25.         convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
  26.         total_dop,
  27.         convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
  28.         total_grant_kb,
  29.         convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
  30.         total_used_grant_kb,
  31.         convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
  32.         total_ideal_grant_kb,
  33.         convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
  34.         total_reserved_threads,
  35.         convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
  36.         total_used_threads,
  37.         convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
  38.         case 
  39.             when sql_handle IS NULL then ' '
  40.             else(substring(st.text,(qs.statement_start_offset+2)/2,(
  41.                 case
  42.                     when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
  43.                     else qs.statement_end_offset    
  44.                 end - qs.statement_start_offset)/2  ))
  45.         end as query_text,
  46.         db_name(st.dbid) as database_name,
  47.         object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
  48.         sp.[query_plan]
  49. FROM sys.dm_exec_query_stats as qs with(readuncommitted)
  50. CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) as st
  51. CROSS APPLY sys.dm_exec_query_plan(qs.[plan_handle]) as sp
  52. WHERE st.[text] LIKE '%marecherche%'

Voir également

Référence des opérations

Dernière mise à jour : Mercredi, le 6 octobre 2021