Top 10 CPU, Disk IO, Memory consuming queries

--Top 10 total CPU consuming queries

SELECT TOP 10

       QT.TEXT AS STATEMENT_TEXT,

       QP.QUERY_PLAN,

       QS.TOTAL_WORKER_TIME AS CPU_TIME

FROM SYS.DM_EXEC_QUERY_STATS 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

ORDER BY TOTAL_WORKER_TIME DESC

--Top 10 average CPU consuming queries

SELECT TOP 10

       TOTAL_WORKER_TIME ,

       EXECUTION_COUNT ,

       TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,

QT.TEXT AS QUERYTEXT

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT

ORDER BY QS.TOTAL_WORKER_TIME DESC ;

--Top 10 I/O intensive queries

SELECT TOP 10

       TOTAL_LOGICAL_READS,

       TOTAL_LOGICAL_WRITES,

       EXECUTION_COUNT,

       TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],

       QT.TEXT AS QUERY_TEXT,

       DB_NAME(QT.DBID) AS DATABASE_NAME,

       QT.OBJECTID AS OBJECT_ID

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT

WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0

ORDER BY [IO_TOTAL] DESC

--Execution count of each query

SELECT

       QS.EXECUTION_COUNT,

       QT.TEXT AS QUERY_TEXT,

       QT.DBID,

       DBNAME= DB_NAME (QT.DBID),

       QT.OBJECTID,

       QS.TOTAL_ROWS,

       QS.LAST_ROWS,

       QS.MIN_ROWS,

       QS.MAX_ROWS

FROM SYS.DM_EXEC_QUERY_STATS AS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT

ORDER BY QS.EXECUTION_COUNT DESC