Heavy SQL usage reported by host

sql-server
Tags: #<Tag:0x00007f8ba000b2d8>

#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?


#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()))