SQL Script to Check Most Executed Queries in a SQL Server Database

  • sys.dm_exec_query_stats
  • sys.dm_exec_sql_text to fetch the exact query
  • sys.dm_exec_query_plan to fetch the query plan
SELECT TOP(50) LEFT(t.[text], 50) AS [Short Query Text], qs.execution_count AS [Execution Count]
	, qs.total_logical_reads AS [Total Logical Reads]
	, qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads]
	, qs.total_worker_time AS [Total Worker Time]
	, qs.total_worker_time/qs.execution_count AS [Avg Worker Time]
	, qs.total_elapsed_time AS [Total Elapsed Time]
	, qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time]
	, CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
	, qs.creation_time AS [Creation Time]
	--, t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment to see the query plan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC 
OPTION (RECOMPILE)

And again Thanks to Glenn Berry for sharing the script.