Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I've just added some caching to my Sql server to speed up a particularly important SELECT query. This cache will be refreshed every ~10 minutes. The time between activations does not have to be predictable, so long as there is not a long delay between activations.

Is using the Sql server agent the right solution here? This seems to be more aimed at large database maintenance tasks. Should I use a different SQL Server method, or perhaps trigger the update from my client application?

Also, my dev machine does not have the server agent available, so using a different method is attractive for that reason.

share|improve this question
In cases where I have had a moderately expensive update to perform on a cache, but a fair likelihood that no one will view it, I have allowed the application to trigger the update. The stored procedure that retrieves the cache contents checks the last refresh date/time. If it exceeds the limit, e.g. 10 minutes, then that lucky user gets to wait for the refresh. – user92546 Feb 15 '12 at 16:12

migrated from stackoverflow.com Feb 16 '12 at 5:46

2 Answers

up vote 2 down vote accepted

I've used SQL Server Agent to run jobs every 10 seconds. It's OK.

If you don't have it, you can use the Windows Scheduler to run osql or sqlcmd as required. Or say Quartz.net in your app. Or anything that can call a database on a schedule (AutoSys etc)

share|improve this answer
Ah, that's ok then. I was mainly wondering if the Server Agent is the wrong tool for jobs run very frequently. – Oliver Feb 15 '12 at 13:55
Just make sure that you're setting up the history retention so that you're not keeping a lot. I don't remember what the default is, but it's worth looking into! – Ben Thul Feb 15 '12 at 16:51
up vote 1 down vote

You can contain the entire solution within a database and not rely on SQL Agent at all: conversation timers and internal activation. The main advantage is the self containment inside the DB: the 'scheduled' job will run after a failover, will run if you restore DB on a different machine, will run if SQL Agent is busy or even disabled, will run on SQL Express that lack an agent to begin with.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.