SELECT *
FROM sys.dm_exec_query_stats
If you want to find out the query details:
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'%something unique about your query%'
ORDER BY s.last_execution_time DESC;
If you want to find out the query plan details:
SELECT
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(total_physical_reads/execution_count) AS avg_phys_reads,
(total_worker_time/execution_count) AS avg_cpu_over_head,
total_logical_reads, total_logical_writes, total_physical_reads,
total_worker_time, execution_count, total_elapsed_time AS Duration,
plan_generation_num AS num_recompiles,
statement_start_offset AS stmt_start_offset,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
(SELECT query_plan FROM sys.dm_exec_query_plan(plan_handle)) AS query_plan
FROM sys.dm_exec_query_stats a
--JUST CHANGE THE ORDER BY TO GET THE OTHER RESOURCES
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC
If you want to find out the query execution count details:
SELECT TOP 1000 execution_count, statement_text
FROM (
SELECT QS.*,
SUBSTRING(
ST.text,
(QS.statement_start_offset/2) + 1,
((
CASE 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
) AS query_stats
--WHERE statement_text LIKE 'UPDATE%'
--ORDER BY execution_count DESC
Reference:
https://thomaslarock.com/2012/03/march-madness-sql-azure-sys-dm_exec_query_stats/
https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio
Add a comment