SQL Server Performance Forum – Threads Archive

performance alerts

Question: You have created an alert on a SQL Server 2000 machine that will notify you by e-mail if the available memory falls below 200MB and stays below 200MB for over 10 seconds. What may happen if the memory utilization falls below 200MB and stays that way for 15 seconds? Answer: You may not receive notification of the low memory availability. Explanation: Unless a performance counter maintains a value for at least 20 seconds, the SQL Server Agent may fail to see a computer cross the specified threshold. For you to be alerted each time the memory falls below 200MB for over 10 seconds, the memory availability counter would need to maintain its value for 20 seconds each time. This is a question from a book along with a supposedly correct answer and an explanation.
Is there any fixed period of time that tells how often SQL Server Agent checks performance counters?
Can anybody explain this to me?

---

I’ve just found the answer to my own question. In Enterprise Manager, when configuring ‘Idle CPU condition’, it is not possible to set a value of ‘And remains below this level for:’ parameter that is smaller than 20 seconds. Anything below this value is automatically changed to 20 seconds.

---

A little bit more about this problem (what can a free Sunday evening do to productivity!): USE msdb
GO sp_helptext ‘sp_set_sqlagent_properties’
GO /*
CREATE PROCEDURE dbo.sp_set_sqlagent_properties
@auto_start INT = NULL, — 1 or 0
— Non-SQLDMO exposed properties
@sqlserver_restart INT = NULL, — 1 or 0
@jobhistory_max_rows INT = NULL, — No maximum = -1, otherwise must be > 1
@jobhistory_max_rows_per_job INT = NULL, — 1 to @jobhistory_max_rows
@errorlog_file NVARCHAR(255) = NULL, — Full drivepath
ame of errorlog file
@errorlogging_level INT = NULL, — 1 = error, 2 = warning, 4 = information
@error_recipient NVARCHAR(30) = NULL, — Network address of error popup recipient
@monitor_autostart INT = NULL, — 1 or 0
@local_host_server NVARCHAR(30) = NULL, — Alias of local host server
@job_shutdown_timeout INT = NULL, — 5 to 600 seconds
@cmdexec_account VARBINARY(64) = NULL, — CmdExec account information
@regular_connections INT = NULL, — 1 or 0
@host_login_name sysname = NULL, — Login name (if regular_connections = 1)
@host_login_password VARBINARY(512) = NULL, — Login password (if regular_connections = 1)
@login_timeout INT = NULL, — 5 to 45 (seconds)
@idle_cpu_percent INT = NULL, — 1 to 100
@idle_cpu_duration INT = NULL, — 20 to 86400 seconds
@oem_errorlog INT = NULL, — 1 or 0
@sysadmin_only INT = NULL, — 1 or 0
@email_profile NVARCHAR(64) = NULL, — Email profile name
@email_save_in_sent_folder INT = NULL, — 1 or 0
@cpu_poller_enabled INT = NULL — 1 or 0
AS
…
*/
The most essential line is:
@idle_cpu_duration INT = NULL, — 20 to 86400 seconds

---

]]>