Applied SQL…

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)