Multiple queue priority(order) is not working

I am using SQL Server implementation.
I need to categorize jobs based on the priority and so I have created the queue like following
var options = new BackgroundJobServerOptions
{
ServerName = “Server1”,
WorkerCount = 1,
Queues = new[] { “critical”,“normal”,“low”}
};

But when the jobs is executed, it picks the critical one first and then pick from the queue > low, though I was expecting to pick the > normal queue next.
Then I have changed it to name the queues as a,b,c and it is working.

So my guess it is using the alphabetical order, not the order in which the queue is specified?

My tests with version 1.5.6 confirm what @reji79 described. I had to create queues “critical”, “default”, “low” to have alphabetical order same as meaning of the queues. Please look at the issue.

@odinserj is this expected?

With SqlServerStorage, the only reason it is ordering at all, is the index on the Queue column is in ascending order.
There appears to be no attempt at ordering in the SqlServerJobQueue.

I need this same priority handling, so I should report back with my attempt to change that template to something like:

WITH dq AS (SELECT TOP (1) FROM JobQueue 
    where (FetchedAt is null or FetchedAt < DATEADD(second, @timeout, GETUTCDATE())) 
    and Queue in @queues 
   -- here would be the change
   ORDER BY CASE Queue 
         WHEN 'critical' Then 0             
         WHEN 'normal' Then 1
         WHEN 'low' Then 2
    END
)
delete top (1) from dq [{_storage.SchemaName}].JobQueue with (readpast, updlock, rowlock) 
output DELETED.Id, DELETED.JobId, DELETED.Queue 

but I do no know how to parameterize those queue names in a CASE/WHEN statement when the length of the list of queues is unknown.

Yes, when using Hangfire.SqlServer, the priority is based on alphabetical order, definitely need to document this. The problem with more complex queries that they are complex, and will reduce the throughput, because TVE, table variable or temporary table is required to use any number of queues with correct sorting.

Hi, this really needs to be added to the documentation -
http://docs.hangfire.io/en/latest/background-processing/configuring-queues.html?highlight=queues

I was looking at the the source, in SqlServerMonitoringApi.Queues() in SqlServerMonitoringApi.cs is this why we get alpha sort for queues? The QueueProvider just provides them to the consumer in that order?

Ok, after spending more time in the code, I’ve noticed that the Monitoring API doesn’t seem to be called and the code given by hometoast above calls directly into sql for the queue. My understanding was that there isn’t a guarantee in mssql unless you specify the orderby. Also, don’t see a good extension to supply an alternative Dequee method.

Anyone else work anything out for this issue?

Is there any update to this issue or can it be expected to work differently on Hangfire.Pro.Redis?