SELECT TOP 100
       'longest query' AS msg,
       GETDATE() AS [Collection Date],
       [qs].[execution_count] AS [Execution Count],
       SUBSTRING(
                    [qt].[text],
                    [qs].[statement_start_offset] / 2 + 1,
                    (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
                ) AS [Query Text],
       DB_NAME([qt].[dbid]) AS [DB Name],
       [qs].[total_worker_time] AS [Total CPU Time],
       [qs].[total_worker_time] / [qs].[execution_count] AS [Avg CPU Time (ms)],
       [qs].[total_physical_reads] AS [Total Physical Reads],
       [qs].[total_physical_reads] / [qs].[execution_count] AS [Avg Physical Reads],
       [qs].[total_logical_reads] AS [Total Logical Reads],
       [qs].[total_logical_reads] / [qs].[execution_count] AS [Avg Logical Reads],
       [qs].[total_logical_writes] AS [Total Logical Writes],
       [qs].[total_logical_writes] / [qs].[execution_count] AS [Avg Logical Writes],
       [qs].[total_elapsed_time] AS [Total Duration],
       [qs].[total_elapsed_time] / 1000000.0 AS [Total Duration seconds],
       [qs].[total_elapsed_time] / [qs].[execution_count] / 1000000.0 AS [Avg Duration seconds],
       [qp].[query_plan] AS [Plan],
       [qs].[creation_time],
       [qs].[last_execution_time]
FROM [sys].[dm_exec_query_stats] AS [qs]
    CROSS APPLY [sys].dm_exec_sql_text([qs].[sql_handle]) AS [qt]
    CROSS APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) AS [qp]
WHERE
--/* most often used */ [qs].[execution_count]                            > 50
--/* most CPU intensive */ [qs].[total_worker_time] / [qs].[execution_count]    > 100  --[Avg CPU Time (ms)]
--/* most read IO */ [qs].[total_physical_reads] / [qs].[execution_count] > 1000 --[Avg Physical Reads]
--/* most intesive in reads */ [qs].[total_logical_reads] / [qs].[execution_count]  > 1000 --[Avg Logical Reads]
--/* most IO intensive in write */ [qs].[total_logical_writes] / [qs].[execution_count] > 1000 --[Avg Logical Writes]
/* longest queries */ [qs].[total_elapsed_time] / [qs].[execution_count] > 1000 --[Avg Duration (ms)]
AND DB_NAME([qt].[dbid]) NOT IN ( 'master', 'msdb', 'model', 'tempDb' )
ORDER BY
--[Avg Logical Reads] DESC 
--[qs].[execution_count] DESC
--,[qs].[total_worker_time] / [qs].[execution_count] DESC
--,[qs].[total_physical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_writes] / [qs].[execution_count] DESC;
/* average duration */ [qs].[total_elapsed_time] / [qs].[execution_count] DESC;


SELECT TOP 100
       'most cpu' AS msg,
       GETDATE() AS [Collection Date],
       [qs].[execution_count] AS [Execution Count],
       SUBSTRING(
                    [qt].[text],
                    [qs].[statement_start_offset] / 2 + 1,
                    (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
                ) AS [Query Text],
       DB_NAME([qt].[dbid]) AS [DB Name],
       [qs].[total_worker_time] AS [Total CPU Time],
       [qs].[total_worker_time] / [qs].[execution_count] AS [Avg CPU Time (ms)],
       [qs].[total_physical_reads] AS [Total Physical Reads],
       [qs].[total_physical_reads] / [qs].[execution_count] AS [Avg Physical Reads],
       [qs].[total_logical_reads] AS [Total Logical Reads],
       [qs].[total_logical_reads] / [qs].[execution_count] AS [Avg Logical Reads],
       [qs].[total_logical_writes] AS [Total Logical Writes],
       [qs].[total_logical_writes] / [qs].[execution_count] AS [Avg Logical Writes],
       [qs].[total_elapsed_time] AS [Total Duration],
       [qs].[total_elapsed_time] / 1000000.0 AS [Total Duration seconds],
       [qs].[total_elapsed_time] / [qs].[execution_count] / 1000000.0 AS [Avg Duration seconds],
       [qp].[query_plan] AS [Plan],
       [qs].[creation_time],
       [qs].[last_execution_time]
FROM [sys].[dm_exec_query_stats] AS [qs]
    CROSS APPLY [sys].dm_exec_sql_text([qs].[sql_handle]) AS [qt]
    CROSS APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) AS [qp]
WHERE
--/* most often used */ [qs].[execution_count]                            > 50
/* most CPU intensive */ [qs].[total_worker_time] / [qs].[execution_count] > 100 --[Avg CPU Time (ms)]
--/* most read IO */ [qs].[total_physical_reads] / [qs].[execution_count] > 1000 --[Avg Physical Reads]
--/* most intesive in reads */ [qs].[total_logical_reads] / [qs].[execution_count]  > 1000 --[Avg Logical Reads]
--/* most IO intensive in write */ [qs].[total_logical_writes] / [qs].[execution_count] > 1000 --[Avg Logical Writes]
--/* longest queries */[qs].[total_elapsed_time] / [qs].[execution_count]   > 1000 --[Avg Duration (ms)]
AND DB_NAME([qt].[dbid]) NOT IN ( 'master', 'msdb', 'model', 'tempDb' )
ORDER BY
--[Avg Logical Reads] DESC 
--[qs].[execution_count] DESC
/* cpu intensive */ [qs].[total_worker_time] / [qs].[execution_count] DESC;
--,[qs].[total_physical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_writes] / [qs].[execution_count] DESC;
--/* average duration */,[qs].[total_elapsed_time] / [qs].[execution_count] DESC


SELECT TOP 100
       'most physical reads' AS msg,
       GETDATE() AS [Collection Date],
       [qs].[execution_count] AS [Execution Count],
       SUBSTRING(
                    [qt].[text],
                    [qs].[statement_start_offset] / 2 + 1,
                    (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
                ) AS [Query Text],
       DB_NAME([qt].[dbid]) AS [DB Name],
       [qs].[total_worker_time] AS [Total CPU Time],
       [qs].[total_worker_time] / [qs].[execution_count] AS [Avg CPU Time (ms)],
       [qs].[total_physical_reads] AS [Total Physical Reads],
       [qs].[total_physical_reads] / [qs].[execution_count] AS [Avg Physical Reads],
       [qs].[total_logical_reads] AS [Total Logical Reads],
       [qs].[total_logical_reads] / [qs].[execution_count] AS [Avg Logical Reads],
       [qs].[total_logical_writes] AS [Total Logical Writes],
       [qs].[total_logical_writes] / [qs].[execution_count] AS [Avg Logical Writes],
       [qs].[total_elapsed_time] AS [Total Duration],
       [qs].[total_elapsed_time] / 1000000.0 AS [Total Duration seconds],
       [qs].[total_elapsed_time] / [qs].[execution_count] / 1000000.0 AS [Avg Duration seconds],
       [qp].[query_plan] AS [Plan],
       [qs].[creation_time],
       [qs].[last_execution_time]
FROM [sys].[dm_exec_query_stats] AS [qs]
    CROSS APPLY [sys].dm_exec_sql_text([qs].[sql_handle]) AS [qt]
    CROSS APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) AS [qp]
WHERE
--/* most often used */ [qs].[execution_count]                            > 50
--/* most CPU intensive */ [qs].[total_worker_time] / [qs].[execution_count]    > 100  --[Avg CPU Time (ms)]
/* most read IO */ [qs].[total_physical_reads] / [qs].[execution_count] > 1000 --[Avg Physical Reads]
--/* most intesive in reads */ [qs].[total_logical_reads] / [qs].[execution_count]  > 1000 --[Avg Logical Reads]
--/* most IO intensive in write */ [qs].[total_logical_writes] / [qs].[execution_count] > 1000 --[Avg Logical Writes]
--/* longest queries */[qs].[total_elapsed_time] / [qs].[execution_count]   > 1000 --[Avg Duration (ms)]
AND DB_NAME([qt].[dbid]) NOT IN ( 'master', 'msdb', 'model', 'tempDb' )
ORDER BY
--[Avg Logical Reads] DESC 
--[qs].[execution_count] DESC
--,[qs].[total_worker_time] / [qs].[execution_count] DESC
/* most disk activity */ [qs].[total_physical_reads] / [qs].[execution_count] DESC;
--,[qs].[total_logical_reads] / [qs].[execution_count] DESC
--,[qs].[total_logical_writes] / [qs].[execution_count] DESC;
--/* average duration */,[qs].[total_elapsed_time] / [qs].[execution_count] DESC