Heavy SQL usage reported by host

bug?
1

My Host just came to me with the message our application is using all CPU on the SQL server.
Since the SQL server is hosted by them, i cannot give you many traces, but I know the following query is causing the trouble

(@queues1 nvarchar(4000),@timeoutSs int,@delayMs int,@endMs int)
set nocount on;set xact_abort on;set tran isolation level read committed;

declare @end datetime2 = DATEADD(ms, @endMs, SYSUTCDATETIME()),
@delay datetime = DATEADD(ms, @delayMs, convert(DATETIME, 0));

WHILE (SYSUTCDATETIME() < @end)
BEGIN
update top (1) JQ set FetchedAt = GETUTCDATE()
output INSERTED.Id, INSERTED.JobId, INSERTED.Queue, INSERTED.FetchedAt
from [HangFire].JobQueue JQ with (forceseek, paglock, xlock)
where Queue in (@queues1) and (FetchedAt is null or FetchedAt < DATEADD(second, @timeoutSs, GETUTCDATE()));

IF @@ROWCOUNT > 0 RETURN;
WAITFOR DELAY @delay;
END

However, I am unable to find what is happening causing this.

Situation:
A very non-intensive Hangfire server (1.7.9) doing one job (sometimes two) a day, so nothing heavy.
Jobs are succeeded and nothing is in the queue.

The setup is a follows:

    services.AddHangfire(configuration => configuration
        .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
        .UseSimpleAssemblyNameTypeSerializer()
        .UseRecommendedSerializerSettings()
        .UseSqlServerStorage(addr ?? Configuration.GetConnectionString("DefaultConnection"), new SqlServerStorageOptions
        {
            CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
            SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
            QueuePollInterval = TimeSpan.Zero,
            UseRecommendedIsolationLevel = true,
            UsePageLocksOnDequeue = true,
            DisableGlobalLocks = true
        }));

    services.AddHangfireServer();

Anyone any idea

2

Hm, that’s really strange, because that query doesn’t issue too much load even on the cheapest and slowest SQL Azure Basic B0 plan. How did you perform the investigation? May be there’s other thing that causes the high CPU usage?

image
image2456×1078 202 KB

3

same problem here. Many query

update top (1) JQ
set FetchedAt = GETUTCDATE()
output INSERTED.Id, INSERTED.JobId, INSERTED.Queue, INSERTED.FetchedAt
from [HangFire].JobQueue JQ with (forceseek, paglock, xlock)
where Queue in (@queues1) and
(FetchedAt is null or FetchedAt < DATEADD(second, @timeout, GETUTCDATE()))

4

Hi,
I’m trying the version 1.7.18 and I see always the WAITFOR DELAY @delay; query in the Active Expensive Query Tab. I see always this query:

(@queues1 nvarchar(4000),@timeoutSs int,@delayMs int,@endMs int)
set nocount on;set xact_abort on;set tran isolation level read committed;

declare @end datetime2 = DATEADD(ms, @endMs, SYSUTCDATETIME()),
@delay datetime = DATEADD(ms, @delayMs, convert(DATETIME, 0));

WHILE (SYSUTCDATETIME() < @end)
BEGIN
update top (1) JQ set FetchedAt = GETUTCDATE()
output INSERTED.Id, INSERTED.JobId, INSERTED.Queue, INSERTED.FetchedAt
from [HangFire].JobQueue JQ with (forceseek, readpast, updlock, rowlock)
where Queue in (@queues1) and (FetchedAt is null or FetchedAt < DATEADD(second, @timeoutSs, GETUTCDATE()));

IF @@ROWCOUNT > 0 RETURN;
WAITFOR DELAY @delay;

END

in the processes tab, suspended and with wait type WAITFOR.

I’m using also HF 1.6.* in other projects and this query never appears.
There is some configuration in 1.7.* to remove this query?

Thanks

5

Those WAITFOR intervals are kept to minimum, so in practice they shouldn’t cause anything bad. They will consume worker threads of SQL Server itself, but for relatively short time intervals, and can cause negative effects only if your SQL Server instance is overloaded with thousands of clients.

You can turn off this query by turning off the long polling fetching technique (so you’ll have delays between background job was queued and when worker picks it up) by setting SqlServerStorageOptions.QueuePollInterval to TimeSpan.FromSeconds(1) or more, but actually this will increase CPU consumption on your SQL Server.

6

Thanks odinnserj. I tried with QueuePollInterval and looks ok.
With many instance the SQL Server Activity monitor was hard to read…
The CPU consumption is in your todo list?
In meantime I’ll take a look to this possible issue.

Thanks