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

  • Article

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 AS [Total Elapsed Time (mSecs)]

FROM

sys.dm_exec_query_stats AS qs

CROSS

APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a

WHERE

a.[attribute] = 'dbid'

GROUP

BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')

ORDER

BY [Total Worker Time (mSecs)] DESC