I wrote this query that helps us find executed queries with most number of execution counts. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.
SELECT TOP 5 SQLtxt.text AS 'SQL', qstats.execution_count AS 'Execution Count', qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS 'Logical Writes Per Second', qstats.total_physical_reads AS [Total Physical Reads], qstats.total_worker_time/qstats.execution_count AS [Average WorkerTime], qstats.total_worker_time AS 'Total Worker Time', DATEDIFF(hour, qstats.creation_time, GetDate()) AS 'TimeInCache in Hours' FROM sys.dm_exec_query_stats AS qstats CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS SQLtxt WHERE SQLtxt.dbid = db_id() ORDER BY qstats.execution_count DESC
Related articles
- Find Top 5 expensive Queries from a Write IO perspective (appliedsql.wordpress.com)
- Find Top 5 expensive Queries from a Read IO perspective (appliedsql.wordpress.com)
