Query Stats Ranking

use master



Select
SQL as SqlStatement
,DBNAME
,OBJECTNAME

,100. * SUM(total_execution_time_ms) / SUM(SUM(total_execution_time_ms)) OVER() AS PCTOFTOTAL_TOTAL_EXECUTION_TIME_MS
,ROW_NUMBER() OVER(ORDER BY SUM(total_execution_time_ms) DESC) AS TOTAL_EXECUTION_TIME_MS_RANK

,100. * SUM(total_cpu_time_ms) / SUM(SUM(total_cpu_time_ms)) OVER() AS PCTOFTOTAL_TOTAL_CPU_TIME_MS_MS
,ROW_NUMBER() OVER(ORDER BY SUM(total_cpu_time_ms) DESC) AS TOTAL_CPU_TIME_MS_RANK

,100. * SUM(total_logical_reads) / SUM(SUM(total_logical_reads)) OVER() AS PCTOFTOTAL_TOTAL_LOGICAL_READS
,ROW_NUMBER() OVER(ORDER BY SUM(total_logical_reads) DESC) AS TOTAL_LOGICAL_READS_RANK

,100. * SUM(total_logical_reads) / SUM(SUM(total_logical_reads)) OVER() AS PCTOFTOTAL_TOTAL_LOGICAL_READS
,ROW_NUMBER() OVER(ORDER BY SUM(total_logical_reads) DESC) AS TOTAL_LOGICAL_READS_RANK

,100. * SUM(total_logical_writes) / SUM(SUM(total_logical_writes)) OVER() AS PCTOFTOTAL_TOTAL_LOGICAL_WRITES
,ROW_NUMBER() OVER(ORDER BY SUM(total_logical_writes) DESC) AS TOTAL_LOGICAL_WRITES_RANK

,cast(100.00 * SUM(execution_count) / SUM(SUM(execution_count)) OVER() as numeric(32,2) ) AS PctofTotalExecution_count
,ROW_NUMBER() OVER(ORDER BY SUM(execution_count) DESC) AS Execution_Count_Rank

,total_execution_time_ms
,total_cpu_time_ms
,total_logical_reads
,total_logical_writes
,execution_count
,query_hash
,query_plan_hash
,cast(query_plan as XML) as queryplan
from (

SELECT TOP 200
        SUBSTRING(text, statement_start_offset/2 + 1,
             (CASE WHEN statement_end_offset = -1
                             THEN LEN(CONVERT(nvarchar(max), text)) * 2
                             ELSE statement_end_offset
                END - statement_start_offset)/2) as SQL              
        ,coalesce( db_name(query_text.DBID) , DB_Name(F_DB.DatabaseID) ) as DBNAME
        , Object_name(query_text.objectid,query_text.DBID) as OBJECTNAME
   ,s.total_worker_time / 1000 AS total_cpu_time_ms
      ,s.total_elapsed_time / 1000 AS total_execution_time_ms
,s.total_logical_reads
,s.total_logical_writes

,s.execution_count as execution_count
,s.query_hash
,s.query_plan_hash
,c.query_plan
,((s.total_elapsed_time)/ s.execution_count)/ 1000 AS avg_execution_time_ms
,  ((s.total_worker_time * 1.00)/ s.execution_count)/ 1000 AS avg_cpu_time_ms
,  ((s.total_logical_reads * 1.00)/ s.execution_count) AS avg_logical_reads
,  ((s.total_logical_writes * 1.00)/ s.execution_count) AS avg_logical_writes
FROM sys.dm_exec_query_stats s
  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query_text
      cross apply sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) c
       CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(s.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
--ORDER BY total_worker_time DESC
ORDER BY total_elapsed_time DESC
--ORDER BY total_logical_reads DESC


 ) as a
Group BY SQL,DBNAME,OBJECTNAME, total_cpu_time_ms, total_execution_time_ms, total_logical_reads, total_logical_writes, execution_count,query_hash,query_plan_hash
,query_plan

Order by
--a.total_cpu_time_ms desc
a.total_execution_time_ms desc
--a.total_logical_reads desc