Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Getting SQLAgent to Respond Faster

By Johan Bijnens,

HowTo have a faster response to your alert when using SQLAgent

HowTo have a faster response to your alert when using SQLAgent

We use alerts to launch jobs which are not owned by the standard SQL Server users, but by the system administrators because of the need for xp_cmdshell. With SQL2005 we are implementing SQLAgent proxies, but the applications run with less authority and don't own any jobs. So for the moment the alert-system is still in use. You can find more information in my script at : help to tighten use of cmdshell or sp_start_job

Since our implementation of SQL2005, we received notifications in SQLAgent.OUT like : 2007-07-23 14:02:49 - + [425] delay_between_response attribute (5 sec) of alert (18) is less than poll interval for this alert (20 sec)

So this means that SQLAgent will only respond to events in the eventlog every 20 seconds.
I couldn't find info about it in Books Online, so I launched threads in several forums to find out how this can be altered, but received no satisfying answer.
Finally I started digging into SQLAgent's settings for the quest of the holy poll.

There are many registry keys SQLAgent has in use, and one of the is called : EventLogPeekInterval. I knew, that if I wanted SQLAgent to respond to an alert, I needed to have it logged into the Windows eventlog, so this might be the knob to switch.

This registry key had a value of HEX 14 (20 decimal).
I altered it to HEX a (10 decimal).
I stopped SQLAgent and started it immediately after it did shut down.
The result that I received was the one I hoped for.
Now I got the message : 2007-07-23 14:10:39 - + [425] delay_between_response attribute (5 sec) of alert (18) is less than poll interval for this alert (10 sec)

So it was indeed the switch I had been looking for.

I experimented a bit more and I found out that the Microsoft's SQLAgent team did build in some restrictions: This registry key's value needs to be bigger than 9. If you put a value in it, lower than HEX a (10 decimal), SQLAgent neglects it at startup time and uses the default 20 second peek interval.

I tested the value for an upper limit to find out when it would switch back to 20 seconds. When I provided HEX e11 (3601 decimal) it finally re-applied it's default. The value HEX e10 (3600 decimal) still worked fine.

So you can set SQLAgent to poll the eventlog between 10 and 3600 seconds. (between HEX a and HEX e10)

If you need responses in an interval lower than 10 seconds, maybe Service Broker is the way to go. My code is shown below:

-- Script to alter the needed registry key
declare @RegValue int
set @RegValue = 10 -- provide dec. value in seconds between 10 and 3600
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
	@key=N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
	@value_name='EventLogPeekInterval',
	@type='REG_DWORD',
	@value=@RegValue

My conclusion

On our SQL Servers I'll lower this setting to 10 seconds only on servers where this alert system is in use.

Keep in mind this info is provided as is. It is up to you to determine if you want to use it and how you want to use it. In any way, test it before you implement it on a production box !

Total article views: 5336 | Views in the last 30 days: 7
 
Related Articles
FORUM

SQLAgent - Alert Engine

sp_sqlagent_get_perf_counters

BLOG

What is the difference between Money and Decimal(19,4)

One of my co-workers and I were discussing the difference between the data type MONEY and the data t...

FORUM

Remove Decimals Without Rounding

How do I remove decimals

FORUM

Decimal Truncation in division

Decimal Truncation in division

FORUM

Decimal Truncation in division

Decimal Truncation in division

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones