Relative load per DB in CPU, IO and CLR

What is the relative load per DB in CPU, IO and CLR?

--****************************************************************************************
-- What is the relative load per DB in CPU, IO and CLR
--****************************************************************************************
-- Versie:  1.0
-- Author:  Theo Ekelmans
-- Date:    2009-06-11
--
--****************************************************************************************
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_reads]) AS [Total Logical Reads]
	, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
	, 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

Leave a Reply

Your email address will not be published. Required fields are marked *