HangFire – SQLServer – “Lock Timeouts”

Background

When running an Application that relies on SQL Server has a backend it is a good idea to profile the application.

Profiling Tools

We use SQL Server Activity Monitor, Extended Events, and SQL Server Profiler to do so.

Activity Monitor

Image

 

Explanation

  1. Resource Waits
    • Lock = 46742
    • Network I/O = 2429
    • Backup = 1467
    • Buffer I/O = 1151
    • Logging = 705
    • Buffer Latch = 216

 

Extended Events

Live Data

Image

 

Tabulate

Field Value
 sql_text (@queues1 nvarchar(4000))
delete top (1) JQ output DELETED.Id, DELETED.JobId, DELETED.Queue
from [HangFire].JobQueue JQ with (readpast, updlock, rowlock, forceseek)
where Queue in (@queues1)

 

 

Explanation

  1. Fetch data from HangFire.JobQueue
  2. Hint
    • Hint Locks
      • readpast
        • If another session has the row locked, then skip it and go on to the next one
      • updlock
        • Request an Update Lock
      • rowlock
        • The lock granularity should be Row
      • forceseek
        • Seek rather than scan

Findings

  1. In an hour, 60 Minutes, session
    • We identified the following errors
      • error_reported=12
      • lock_timeout=78

TroubleShooting

Query

The identified query

(@queues1 nvarchar(4000))
delete top (1) JQ output DELETED.Id, DELETED.JobId, DELETED.Queue
from [HangFire].JobQueue JQ with (readpast, updlock, rowlock, forceseek)
where Queue in (@queues1)

is used to poll the Queue.

By default it runs 15 seconds.

 

Remediation

Polling Interval

Increase Pooling Interval

Configuration

app.config
   <appSettings>
   
    <add key="maxDurationInMinutes" value="60" />

    <add key="queuePollInterval" value="60" />
		
  </appSettings>

c# – Read Configuration file

Read queuePollInterval entry from App.Config file


static private void getAppConfigQueuePollInterval()
{
	
	String strQueuePollInterval;
	bool    bRC = false;
	
	
	if (ConfigurationManager.AppSettings["queuePollInterval"] != null)
	{
		
		strQueuePollInterval = ConfigurationManager.AppSettings["queuePollInterval"];
		
		try
		{
			bRC = Int32.TryParse(strQueuePollInterval, out iQueuePollInterval);
			
			Console.WriteLine("queuePollInterval :- " + strQueuePollInterval);					
			
		}
		catch			
		{
		
		}
		
	} //if (ConfigurationManager.AppSettings["queuePollInterval"] != null)
	
} //getAppConfigQueuePollInterval()

c# – Change Polling Interval from default of 15 seconds to value indicated in configuration file

Read queuePollInterval entry from App.Config file



SqlServerStorageOptions optSqlServerStorageOptions = new SqlServerStorageOptions();
{

   if (iQueuePollInterval != queuePollIntervalDefault)
   {
		
	//Documentation Configuration Using SQL Server
	//http://docs.hangfire.io/en/latest/configuration/using-sql-server.html
	//QueuePollInterval = TimeSpan.FromSeconds(15) // Default value
	optSqlServerStorageOptions.QueuePollInterval
		= TimeSpan.FromSeconds(iQueuePollInterval);
			
   }	
	
};
		
GlobalConfiguration.Configuration.UseSqlServerStorage
(
	  connectionStringHFID
	, optSqlServerStorageOptions
);

 

Results – Output

Results – Extended Events

Interpretation

We reduced our lockouts to 78 to 65.

And, all our tasks were still completed successfully.

 

Source Control

GitHub

Updated the gitHub repository and here is the URL.

 

Summary

1 test does not make a science, will run numerous tests and come back and update our post.

Changing the Polling Interval came Googling and as always test everything out.

References

  1. Hangfire keeps running SQL queries even when inactive
    Link
  2. Handling long running tasks (+ long invisibility timeout) + server restarts
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s