Top Slowest Queries or Resource Extensive Queries
Whenever your database is facing any performance issue or resource scarcity,
the first step should be finding the culprits.
I mean finding the slowest or most resource consuming queries and usually
10% queries are responsible for 90% of problems. You can use this very simple
query to get the list of those scripts and start your work to optimize their performance
or other decision making like adding more memory, CPU, partitioning or any other
optimization approach.
You can change Order By clause as per your requirement or use MS excel to
play with data.
SELECT TOP 10
total_worker_time/execution_count AS 'Avg_CPU_Time',
T.text AS 'Sql_Script',
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats
AS Q
CROSS APPLY sys.dm_exec_sql_text(Q.sql_handle) AS T
ORDER BY Avg_CPU_Time DESC;
Please provide your feedback for improvement.
