Get SQL Server CPU between dates

Question

Hello,

I have this query that returns the CPU per databases

SELECT 
	DB_Name(p.DatabaseID) AS DbName
	,SUM(q.total_worker_time) AS CpuTimeInMiliSeconds
FROM 
	sys.dm_exec_query_stats q 
	CROSS APPLY 
	(
		SELECT 
			CONVERT (INT, value) AS [DatabaseID]
		FROM 
			sys.dm_exec_plan_attributes(q.plan_handle)
		WHERE 
			attribute = N'dbid'
	) as p
GROUP BY
	p.DatabaseID
order by
	CPUTimeInMiliSeconds desc

Is there a way to add a WHERE filter between two dates, so i will get only the sum of "CpuTimeInMiliSeconds" between these dates ?

Answer 1

Hi,

Please find below updated query (you can have last*executiontime or creation_*time):

SELECT 
	DB_Name(p.DatabaseID) AS DbName
	,SUM(q.total_worker_time) AS CpuTimeInMiliSeconds
FROM 
	sys.dm_exec_query_stats q 
	CROSS APPLY 
	(
		SELECT 
			CONVERT (INT, value) AS [DatabaseID]
		FROM 
			sys.dm_exec_plan_attributes(q.plan_handle)
		WHERE 
			attribute = N'dbid'
	) as p
where convert(date,q.last_execution_time) between '02/28/2023' and '03/01/2023'
--where convert(date,q.creation_time) between '02/28/2023' and '03/01/2023'
GROUP BY
	p.DatabaseID
order by
	CPUTimeInMiliSeconds desc