I found this WMI code here http://craftydba.com/?p=1114 and it works for me. I modified it for every 60 seconds and when CPU is higher than 25% for testing.
EXEC msdb.dbo.sp_add_alert @name=N'Alert High CPU % (WMI)',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@category_name=N'[Alert By Performance]',
@wmi_namespace=N'\\.\ROOT\CIMV2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 60
WHERE TargetInstance ISA ''Win32_Processor'' AND TargetInstance.LoadPercentage > 25',
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
I have this alert call the code below from http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/ with some modification in a stored procedure to save the information of any running query to a table for future analysis.
--http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/
SELECT
@@servername ServerName
,s.session_id spid
,DB_NAME(database_id) dbName
,s.login_time
,r.start_timestartTime
,s.last_request_end_time
,r.STATUS
,r.command
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) ElapsedTimeSec
,s.host_name
,s.program_name
,s.nt_user_name
,s.login_name
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.wait_type
,r.wait_time / (1000.0) WaitTimeSec
,wait_resource
,r.blocking_session_id blockedby
,s.host_process_id
,r.open_transaction_count
,getdate() DateCollected
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id