Find Most Executed Queries

This DMV will show the actual queries sorted by highest execution count since the last time SQL Server was restarted. These queries could be either adhoc or queries that reside within stored procedures. These are important because often times optimizing these will yield huge results in performance. In order to find specific procedures that have been executed a lot, refer to the Most Executed Procedures DMV.

[cc lang=”sql”]
SELECT TOP 50
qs.execution_count
,OBJECT_NAME(objectid)
,query_text = SUBSTRING(
qt.text,
qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN len(convert(nvarchar(max), qt.text)) * 2
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)
,qt.dbid
,dbname = db_name(qt.dbid)
,qt.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.execution_count DESC
[/cc]