Find most expensive cached queries

Below queries returns aggregate performance statistics for cached query plans.  The result contains one row per query within the cached plan. If the query plan is removed from the cache than that query is excluded from the output. sys.dm_exec_query_stats is a very useful DMV to get cached query details for performance & server load analysis.

Execution count of each query

SELECT QT.DBID, DB_NAME(QT.DBID) AS [Database Name], QT.ObjectID, QT.Text AS [Query Text], QS.Execution_Count AS [Execution Count], QS.Total_Elapsed_Time AS [Total Elapsed Time], QS.Max_Elapsed_Time AS [Max Elapsed Time], QS.Min_Elapsed_Time AS [Min Elapsed Time], QS.Last_Elapsed_Time AS [Last Elapsed Time] 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

Top 5 I/O intensive Queries

SELECT TOP 5 Total_Logical_Reads [Total Logical Read], Total_Logical_Writes AS [Total Logical Write], Total_Logical_Reads + Total_Logical_Writes AS [IO Total], Execution_Count AS [Execution Count], QT.Text AS [Query Text], DB_NAME(QT.DBID) AS [Databaes Name], QT.ObjectID AS [Object IO] 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

Top 5 CPU consuming Queries 

SELECT TOP 5 QT.TEXT AS [Statement Text], QS.TOTAL_WORKER_TIME AS [CPU Time], QP.QUERY_PLAN AS [Query Plan] 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 ORDER BY TOTAL_WORKER_TIME DESC

Top 5 Average CPU consuming Queries 

SELECT TOP 5 TOTAL_WORKER_TIME AS [Total Worker Time], EXECUTION_COUNT AS [Execution Count], TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU Time], QT.TEXT AS [Query Text] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT ORDER BY QS.TOTAL_WORKER_TIME DESC