SQL Top Queries

Top By Query Hash.sql

DECLARE @MaxResultCount int = 500;

SELECT AvgCPU, AvgDuration, AvgReads, AvgCPUPerMinute,
       TotalCPU, TotalDuration, TotalReads,
       PercentCPU, PercentDuration, PercentReads, PercentExecutions,
       ExecutionCount,
       ExecutionsPerMinute,
       PlanCreationTime, LastExecutionTime,
       SUBSTRING(st.text,
                 (StatementStartOffset / 2) + 1,
                 ((CASE StatementEndOffset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE StatementEndOffset
                   END - StatementStartOffset) / 2) + 1) AS QueryText,
        st.Text FullText,
        query_plan AS QueryPlan,
        QueryHash
        PlanHandle,
        StatementStartOffset,
        StatementEndOffset,
        MinReturnedRows,
        MaxReturnedRows,
        AvgReturnedRows,
        TotalReturnedRows,
        LastReturnedRows
FROM (SELECT TOP (@MaxResultCount) 
             Sum(total_worker_time) / Sum(execution_count) AS AvgCPU,
             Sum(total_elapsed_time) / Sum(execution_count) AS AvgDuration,
             Sum(total_logical_reads) / Sum(execution_count) AS AvgReads,
             Cast(Sum(total_worker_time) / Sum(age_minutes) As BigInt) AS AvgCPUPerMinute,
             Sum(execution_count) / Sum(age_minutes) AS ExecutionsPerMinute,
             Cast(Sum(total_worker_time) / Sum(age_minutes_lifetime) As BigInt) AS AvgCPUPerMinuteLifetime,
             Sum(execution_count) / Sum(age_minutes_lifetime) AS ExecutionsPerMinuteLifetime,
             Sum(total_worker_time) AS TotalCPU,
             Sum(total_elapsed_time) AS TotalDuration,
             Sum(total_logical_reads) AS TotalReads,
             Sum(execution_count) ExecutionCount,
             CAST(ROUND(100.00 * Sum(total_worker_time) / Sum(t.TotalWorker), 2) AS MONEY) AS PercentCPU,
             CAST(ROUND(100.00 * Sum(total_elapsed_time) / Sum(t.TotalElapsed), 2) AS MONEY) AS PercentDuration,
             CAST(ROUND(100.00 * Sum(total_logical_reads) / Sum(t.TotalReads), 2) AS MONEY) AS PercentReads,
             CAST(ROUND(100.00 * Sum(execution_count) / Sum(t.TotalExecs), 2) AS MONEY) AS PercentExecutions,
             Min(qs.creation_time) AS PlanCreationTime,              -- Fudged
             Max(qs.last_execution_time) AS LastExecutionTime,       -- Fudged
             Max(qs.plan_handle) AS PlanHandle,
             qs.query_hash AS QueryHash,
             Min(qs.statement_start_offset) AS StatementStartOffset, -- Should match
             Max(qs.statement_end_offset) AS StatementEndOffset,     -- Should match
             Min(qs.min_rows) AS MinReturnedRows,
             Max(qs.max_rows) AS MaxReturnedRows,
             CAST(Sum(qs.total_rows) as MONEY) / Sum(execution_count) AS AvgReturnedRows,
             Sum(qs.total_rows) AS TotalReturnedRows,
             Max(qs.last_rows) AS LastReturnedRows,
             Max(qs.sql_handle) AS SqlHandle
        FROM (SELECT *, 
                     CAST((CASE WHEN DATEDIFF(second, creation_time, GETDATE()) > 0 And execution_count > 1
                                THEN DATEDIFF(second, creation_time, GETDATE()) / 60.0
                                ELSE Null END) as MONEY) as age_minutes, 
                     CAST((CASE WHEN DATEDIFF(second, creation_time, last_execution_time) > 0 And execution_count > 1
                                THEN DATEDIFF(second, creation_time, last_execution_time) / 60.0
                                ELSE Null END) as MONEY) as age_minutes_lifetime
                FROM sys.dm_exec_query_stats) AS qs
             CROSS JOIN(SELECT SUM(execution_count) TotalExecs,
                               SUM(total_elapsed_time) TotalElapsed,
                               SUM(total_worker_time) TotalWorker,
                               SUM(total_logical_reads) TotalReads
                          FROM sys.dm_exec_query_stats) AS t
      WHERE qs.last_execution_time > GETDATE() - 1
   GROUP BY qs.query_hash) sq
    CROSS APPLY sys.dm_exec_sql_text(SqlHandle) AS st
    CROSS APPLY sys.dm_exec_query_plan(PlanHandle) AS qp

Top Individual.sql

Declare @MaxResultCount int = 100;

SELECT AvgCPU, AvgDuration, AvgReads, AvgCPUPerMinute,
       TotalCPU, TotalDuration, TotalReads,
       PercentCPU, PercentDuration, PercentReads, PercentExecutions,
       ExecutionCount,
       ExecutionsPerMinute,
       PlanCreationTime, LastExecutionTime,
       SUBSTRING(st.text,
                 (StatementStartOffset / 2) + 1,
                 ((CASE StatementEndOffset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE StatementEndOffset
                   END - StatementStartOffset) / 2) + 1) AS QueryText,
        st.Text FullText,
        query_plan AS QueryPlan,
        PlanHandle,
        StatementStartOffset,
        StatementEndOffset,
        MinReturnedRows,
        MaxReturnedRows,
        AvgReturnedRows,
        TotalReturnedRows,
        LastReturnedRows,
        DB_NAME(DatabaseId) AS CompiledOnDatabase
FROM (SELECT TOP (@MaxResultCount) 
             total_worker_time / execution_count AS AvgCPU,
             total_elapsed_time / execution_count AS AvgDuration,
             total_logical_reads / execution_count AS AvgReads,
             Cast(total_worker_time / age_minutes As BigInt) AS AvgCPUPerMinute,
             execution_count / age_minutes AS ExecutionsPerMinute,
             Cast(total_worker_time / age_minutes_lifetime As BigInt) AS AvgCPUPerMinuteLifetime,
             execution_count / age_minutes_lifetime AS ExecutionsPerMinuteLifetime,
             total_worker_time AS TotalCPU,
             total_elapsed_time AS TotalDuration,
             total_logical_reads AS TotalReads,
             execution_count ExecutionCount,
             CAST(ROUND(100.00 * total_worker_time / t.TotalWorker, 2) AS MONEY) AS PercentCPU,
             CAST(ROUND(100.00 * total_elapsed_time / t.TotalElapsed, 2) AS MONEY) AS PercentDuration,
             CAST(ROUND(100.00 * total_logical_reads / t.TotalReads, 2) AS MONEY) AS PercentReads,
             CAST(ROUND(100.00 * execution_count / t.TotalExecs, 2) AS MONEY) AS PercentExecutions,
             qs.creation_time AS PlanCreationTime,
             qs.last_execution_time AS LastExecutionTime,
             qs.plan_handle AS PlanHandle,
             qs.statement_start_offset AS StatementStartOffset,
             qs.statement_end_offset AS StatementEndOffset,
             qs.min_rows AS MinReturnedRows,
             qs.max_rows AS MaxReturnedRows,
             CAST(qs.total_rows as MONEY) / execution_count AS AvgReturnedRows,
             qs.total_rows AS TotalReturnedRows,
             qs.last_rows AS LastReturnedRows,
             qs.sql_handle AS SqlHandle,
			 Cast(pa.value as Int) DatabaseId
        FROM (SELECT *, 
                     CAST((CASE WHEN DATEDIFF(second, creation_time, GETDATE()) > 0 And execution_count > 1
                                THEN DATEDIFF(second, creation_time, GETDATE()) / 60.0
                                ELSE Null END) as MONEY) as age_minutes, 
                     CAST((CASE WHEN DATEDIFF(second, creation_time, last_execution_time) > 0 And execution_count > 1
                                THEN DATEDIFF(second, creation_time, last_execution_time) / 60.0
                                ELSE Null END) as MONEY) as age_minutes_lifetime
                FROM sys.dm_exec_query_stats) AS qs
             CROSS JOIN(SELECT SUM(execution_count) TotalExecs,
                               SUM(total_elapsed_time) TotalElapsed,
                               SUM(total_worker_time) TotalWorker,
                               SUM(total_logical_reads) TotalReads
                          FROM sys.dm_exec_query_stats) AS t
             CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
     WHERE pa.attribute = 'dbid'
       AND qs.last_execution_time > GETDATE() - 1) sq
    CROSS APPLY sys.dm_exec_sql_text(SqlHandle) AS st
    CROSS APPLY sys.dm_exec_query_plan(PlanHandle) AS qp
ORDER BY AvgCPUPerMinute Desc