Find Top 5 executed queries ordered by execution count

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',
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


Tagged: , , , , , , ,

One thought on “Find Top 5 executed queries ordered by execution count

  1. MALIA March 20, 2014 at 5:46 am Reply

    Hey there, You have performed an incredible job. I’ll definitely digg it and in my view recommend to my friends. I’m sure they will be benefited from this website.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: