Most efficient use of SQL Server for hourly scheduled jobs

Tags: #<Tag:0x00007fce4c789b88> #<Tag:0x00007fce4c789ac0>

I’m trying to understand the meaning of the SQL Server config options. I don’t see a document that just explains them - some are in release notes, others in examples.

I’m using Hangfire to schedule sending out some emails, and they will be sent perhaps twice a day - for example, 7 AM and 7 PM. So I don’t need fine grained checking of a schedule.

It looks like the recommended/default is:

            CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
            SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
            QueuePollInterval = TimeSpan.Zero,

Does this mean it will constantly be polling the database for the tasks? Every 5 minutes? I’m not getting with QueuePollInterval of TimeSpan.Zero means.

Should I be doing something like QueuePollInterval = TimeSpan.FromMinutes(30) or TimeSpan.FromMinutes(60) if I’m using a somewhat sparse schedule like this? Is this what would make it hit poll the database minimally? Are there any downsides?

Thanks!

Replying to myself…

I have set:

            CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
            SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
            QueuePollInterval = TimeSpan.FromSeconds(60),

But when I use SQL Profiler, it still looks like it is polling the database every 12-13 milliseconds. Nothing I change has any effect on it.

It’s an ASP.NET Core 3 app, and in ConfigureServices, I have:

        var options = new SqlServerStorageOptions
        {
            SchemaName = "MySchema",
            CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
            SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
            QueuePollInterval = TimeSpan.FromSeconds(60),
            UseRecommendedIsolationLevel = true,
            DisableGlobalLocks = true
        };
        services.AddHangfire(configuration => configuration
            .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
            .UseSimpleAssemblyNameTypeSerializer()
            .UseRecommendedSerializerSettings()
            .UseSqlServerStorage(connString, options));

        services.AddHangfireServer();
        services.AddControllersWithViews();

Nothing I change has any effect.