Get the statistics of the most executed queries on the Microsoft SQL Server Database.

query.sql

SELECT TOP 100
    DatabaseName = db_name(dest.dbid), 
    OBJECT_NAME(objectid) AS ObjectName,
    last_execution_time,
    execution_count, 
    total_cpu_time = total_worker_time / 1000, 
    avg_cpu_time = (total_worker_time / execution_count) / 1000.0,
    min_cpu_time = min_worker_time / 1000.0,
    max_cpu_time = max_worker_time / 1000.0,
    last_cpu_time = last_worker_time / 1000.0,
    total_time_elapsed = total_elapsed_time / 1000, 
    avg_time_elapsed = (total_elapsed_time / execution_count) / 1000.0, 
    min_time_elapsed = min_elapsed_time / 1000.0, 
    max_time_elapsed = max_elapsed_time / 1000.0, 
    avg_physical_reads = total_physical_reads / execution_count,
    avg_logical_reads = total_logical_reads / execution_count,
    QueryText = SUBSTRING(dest.text, (deqs.statement_start_offset/2) + 1,
                  (
                        (
                              CASE statement_end_offset
                                    WHEN -1 THEN DATALENGTH(dest.text)
                                    ELSE deqs.statement_end_offset
                              END 
                              - deqs.statement_start_offset
                        ) /2
                  ) + 1
            ) 
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where last_execution_time> getdate()-1
ORDER BY deqs.execution_count DESC