How to view and change SQL Server Agent properties with T-SQL queries

Few days ago, after a reboot, we had this warning on the Agent Error Logs on many servers:
Warning [396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect

“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