14,500 questions
Not really what you are asking for, but here is a query to get the load history. I'm not sure from where I got it; it might have been Glenn Berry's diagnostic queries. You could tailor it to fit your needs and poll it.
-- Listing 6-1. Getting CPU Load History
DECLARE
@now BIGINT;
SELECT @now = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK);
;WITH RingBufferData([timestamp], rec)
AS
(
SELECT [timestamp], CONVERT(XML, record) AS rec
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND
record LIKE N'%<SystemHealth>%'
)
,Data(id, SystemIdle, SQLCPU, [timestamp])
AS
(
SELECT
rec.value('(./Record/@id)[1]', 'int')
,rec.value
('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')
,rec.value
('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')
,[timestamp]
FROM RingBufferData
)
SELECT TOP 256
dateadd(MS, -1 * (@now - [timestamp]), getdate()) AS [Event Time]
,SQLCPU AS [SQL Server CPU Utilization]
,SystemIdle AS [System Idle]
,100 - SystemIdle - SQLCPU AS [Other Processes CPU Utilization]
FROM Data
ORDER BY id desc
OPTION (RECOMPILE, MAXDOP 1);
--
;WITH DBCPU
AS
(
SELECT
pa.DBID, DB_NAME(pa.DBID) AS [DB]
,SUM(qs.total_worker_time/1000) AS [CPUTime]
FROM
sys.dm_exec_query_stats qs WITH (NOLOCK)
CROSS APPLY
(
SELECT CONVERT(INT, value) AS [DBID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid'
) AS pa
GROUP BY pa.DBID
)
SELECT
[DB]
,[CPUTime] AS [CPU Time (ms)]
,CONVERT(decimal(5,2), 1. *[CPUTime] /
SUM([CPUTime]) OVER() * 100.0) AS [CPU Percent]
FROM DBCPU
WHERE DBID <> 32767 -- ResourceDB
ORDER BY [CPUTime] DESC;
