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.

  • 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

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)

| 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
  • See stackoverflow.com/questions/5569415/… – Vadzim Oct 28 '16 at 21:39

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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