“The CPU idle definition influences how Microsoft SQL Server Agent responds to events. For example, suppose that you define the CPU idle condition as when the average CPU usage falls below 10 percent and remains at this level for 10 minutes. Then if you have defined jobs to execute whenever the server CPU reaches an idle condition, the job will start when the CPU usage falls below 10 percent and remains at that level for 10 minutes. “ dixit Microsoft documentation here.
To resolve this warning, you need to go to the Agent Properties>Advanced and check “Define idle CPU condition”
The query used to check it is:
USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @cpu_poller_enabled=1 GO
With this issue, I will also give you some helpful queries to have a look on the Agent properties.
The best way to retrieve the information about the Agent properties is to use the Store Procedure: msdb.dbo.sp_get_sqlagent_properties
All information about the Agent Properties are in the Registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
You can of course read directly the value in the Registry with the query:
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N' CoreEngineMask', @cpu_poller_enabled OUTPUT, N'no_output'
In my case the information is on the Value Name CoreEngineMask and to have the value, you need to do a filter like this:
IF (@cpu_poller_enabled IS NOT NULL) SELECT @cpu_poller_enabled = CASE WHEN (@cpu_poller_enabled & 32) = 32 THEN 0 ELSE 1 END
To finish this article, I will give you the query that I use to put the information from the Stored Procedure in a Table to retrieve the information that need more easily:
CREATE TABLE #sqlagent_properties ( auto_start INT, msx_server_name sysname NULL, sqlagent_type INT, startup_account NVARCHAR(100) NULL, sqlserver_restart INT, jobhistory_max_rows INT, jobhistory_max_rows_per_job INT, errorlog_file NVARCHAR(255) NULL, errorlogging_level INT, errorlog_recipient NVARCHAR(255) NULL, monitor_autostart INT, local_host_server sysname NULL, job_shutdown_timeout INT, cmdexec_account VARBINARY(64) NULL, regular_connections INT, host_login_name sysname NULL, host_login_password VARBINARY(512) NULL, login_timeout INT, idle_cpu_percent INT, idle_cpu_duration INT, oem_errorlog INT, sysadmin_only NVARCHAR(64) NULL, email_profile NVARCHAR(64) NULL, email_save_in_sent_folder INT, cpu_poller_enabled INT, alert_replace_runtime_tokens INT ) INSERT INTO #sqlagent_properties EXEC msdb.dbo.sp_get_sqlagent_properties GO SELECT cpu_poller_enabled FROM #sqlagent_properties DROP TABLE #sqlagent_properties
I hope this can help you when you search the Agent Properties and want to change it on your SQL Server environment