Schedule a Job or wait 2 seconds.

Question

Schedule a Job or wait 2 seconds.

tom.groszko

SSC Enthusiast

Points: 126
More actions
June 22, 2009 at 8:41 am

#222825

I have a task that I want to run about every 2 seconds. The job scheduler will allow me to schedule this often or I could within a procedure wait 2 seconds in a while loop and run again. This is a very small procudure that uses few resources. What are the trade offs between these two methods? Is there another way to do this?
Thanks
Tom

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 1

You can't schedule more often than every minute, can you? At least in SQL Agent.

Every 2 seconds sounds quite often. I'd be tempted to run this as a job, but have it run as a proc with a WAITFOR that loops over and over. With notification on complete (success or failure), you'll get notified if it stops.

tom.groszko SSC Enthusiast Points: 126 More actions · Answer 2

Both 2005 and 2008 allow seconds. I have always satisfied this type of requirement with a loop in the past but I am interested to hear if there is a good reason to do something else.

Thanks for your thoughts on the topic.

Tom

LutzM SSC Guru Points: 107049 More actions · Answer 3

I'm not sure if you need that job because you get data that often or if you just have to react "immediately" in case of some external events.

If it's the latter you might want to look into ServiceBroker.

So, what's the purpose of this high-frequency job?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tom.groszko SSC Enthusiast Points: 126 More actions · Answer 4

I want to save information when I find certain conditions for active sessions. I have considdered Service Broker for starting and stopping this activity. I even have the code written to implement it that way but I am still considering other options. Service broker allows excellent control from an application but there is a good possibility that this needs to continue running across server restarts which requires some sort of job, at least I think it does. SQL server is so full of great features that sometimes it is hard to know that you have considdered all the options. That what is so nice about this group. Considering more options that I might know about.

Thanks for your ideas.

Tom