Historique des requêtes exécutées sur SQL SERVER

/************************************************************************************

-- DESCRIPTION : Historique des requêtes

-- SQL SERVER : 2005 - 2008

-- Date : 2010-04-20

************************************************************************************/

--Afficher les 50 dernières requêtes les plus consommatrices de CPU

SELECT TOP 50 total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;

--Afficher les 50 dernières requêtes exécutées

SELECT TOP 50 creation_time,

total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY creation_time DESC;

--Afficher les 50 requêtes les + fréquemment exécutées

SELECT TOP 50 creation_time,

total_worker_time/execution_count AS [Avg CPU Time],

total_logical_reads,

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_logical_reads DESC;