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.

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

2 comments
Vasu 07 Sep 2010 at 6:50 am

How to read and analyse the Execution plan step by step

Derek Dieter 07 Sep 2010 at 3:17 pm

Hi Vasu, funny you mention this, I am currently writing a how to for optimization. Look for it by the end of this week.

Thanks

Featured Articles

 Site Author

  • Thanks for visiting!