List all the statements in the Plan Cache along with the counts and CPU usage

SELECT t. AS [Adhoc Batch or Object Call], SUBSTRING(t., (qs.[statement_start_offset]/2) + 1, ((CASE qs.[statement_end_offset] WHEN -1 THEN DATALENGTH(t.) ELSE qs.[statement_end_offset] END – qs.[statement_start_offset])/2) + 1) AS [Executed Statement] , qs.[execution_count] AS [Counts] , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time] , qs.[total_physical_reads] AS [Total Physical Reads], (qs.[total_physical_reads] / qs.[execution_count]) AS…

1

Query to list all the databases in the order of CPU usage

USE master SELECT a.[value] AS [dbid] , ISNULL(DB_NAME(CONVERT(INT,a.[value])),’Resource’) AS [DB Name] , SUM(qs.[execution_count]) AS [Counts] , SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)] , SUM(qs.[total_physical_reads]) AS [Total Physical Reads] , SUM(qs.[total_logical_writes]) AS [Total Logical Writes] , SUM(qs.[total_logical_reads]) AS [Total Logical Reads] , SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)] , SUM(qs.[total_elapsed_time]) / 1000…

0