Hangfire causing locks in SQL Server

Question

We are using Hangfire 1.7.2 within our ASP.NET Web project with SQL Server 2016. We have around 150 sites on our server, with each site using Hangfire 1.7.2. We noticed that when we upgraded these sites to use Hangfire, the DB server collapsed. Checking the DB logs, we found out there were multiple locking queries. We have identified one RPC Event “sys.sp_getapplock;1” In the all blocking sessions. It seems like Hangfire is locking our DB rendering whole DB unusable. We noticed almost 670+ locking queries because of Hangfire.

This could possibly be due to these properties we setup:

   SlidingInvisibilityTimeout = TimeSpan.FromMinutes(30),
   QueuePollInterval = TimeSpan.FromHours(5)

Each site has around 20 background jobs, a few of them run every minute, whereas others every hour, every 6 hours and some once a day.

I have searched the documentation but could not find anything which could explain these two properties or how to set them to avoid DB locks.

Looking for some help on this.

EDIT: The following queries are executed at every second:

exec sp_executesql N'select count(*) from [HangFire].[Set] with (readcommittedlock, forceseek) where [Key] = @key',N'@key nvarchar(4000)',@key=N'retries'

select distinct(Queue) from [HangFire].JobQueue with (nolock)

exec sp_executesql N'select count(*) from [HangFire].[Set] with (readcommittedlock, forceseek) where [Key] = @key',N'@key nvarchar(4000)',@key=N'retries'

irrespective of various combinations of timespan values we set. Here is the code of GetHangfirServers we are using:

  public static IEnumerable<IDisposable> GetHangfireServers()
    {
        // Reference for GlobalConfiguration.Configuration: http://docs.hangfire.io/en/latest/getting-started/index.html
        // Reference for UseSqlServerStorage: http://docs.hangfire.io/en/latest/configuration/using-sql-server.html#configuring-the-polling-interval
        GlobalConfiguration.Configuration
            .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)

            .UseSimpleAssemblyNameTypeSerializer()
            .UseRecommendedSerializerSettings()
            .UseSqlServerStorage(ConfigurationManager.ConnectionStrings["abc"]
                .ConnectionString, new SqlServerStorageOptions
            {
                CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                SlidingInvisibilityTimeout = TimeSpan.FromMinutes(30),
                QueuePollInterval = TimeSpan.FromHours(5), // Hangfire will poll after 5 hrs to check failed jobs.
                UseRecommendedIsolationLevel = true,
                UsePageLocksOnDequeue = true,
                DisableGlobalLocks = true
            });

        // Reference: https://docs.hangfire.io/en/latest/background-processing/configuring-degree-of-parallelism.html
        var options = new BackgroundJobServerOptions
        {
            WorkerCount = 5
        };

        var server = new BackgroundJobServer(options);

        yield return server;
    }

The worker count is set just to 5.

There are just 4 jobs and even those are completed (SELECT * FROM [HangFire].[State]):

Do you have any idea why the Hangfire is hitting so many queries at each second?

Answer 1

We faced this issue in one of our projects. The hangfire dashboard is pretty read heavy and it polls the hangfire db very frequently to refresh job status.

Best solution that worked for us was to have a dedicated hangfire database. That way you will isolate the application queries from hangfire queries and your application queries won't be affected by the hangfire server and dashboard queries.