# Heavy SQL usage reported by host

**URL:** <https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078>\
**Category:** bug?\
**Tags:** sql-server\
**Created:** [February 20, 2020, 9:17pm UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078 "2020-02-20T21:17:20Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Martijn](https://dub1.discourse-cdn.com/flex017/user_avatar/discuss.hangfire.io/martijn/32/1981_2.png) [@Martijn](https://discuss.hangfire.io/u/Martijn)\
**Post date:** [February 20, 2020, 9:17pm UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078/1 "2020-02-20T21:17:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![odinserj](https://dub1.discourse-cdn.com/flex017/user_avatar/discuss.hangfire.io/odinserj/32/648_2.png) [@odinserj](https://discuss.hangfire.io/u/odinserj)\
**Post date:** [February 21, 2020, 2:31am UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078/2 "2020-02-21T02:31:03Z")

</div>

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](https://europe1.discourse-cdn.com/flex017/uploads/hangfire/original/2X/4/4df7d16149fc0ba3d34300c8e1aca00f67dfdbe1.png)

---

<div class="post-metadata">

**Author:** ![Roberto\_Marconi](https://dub1.discourse-cdn.com/flex017/user_avatar/discuss.hangfire.io/roberto_marconi/32/2096_2.png) [@Roberto\_Marconi](https://discuss.hangfire.io/u/Roberto_Marconi)\
**Post date:** [May 26, 2020, 7:50pm UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078/3 "2020-05-26T19:50:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![IdWeb](https://avatars.discourse-cdn.com/v4/letter/i/ea666f/32.png) [@IdWeb](https://discuss.hangfire.io/u/IdWeb)\
**Post date:** [March 24, 2021, 9:43am UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078/4 "2021-03-24T09:43:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![odinserj](https://dub1.discourse-cdn.com/flex017/user_avatar/discuss.hangfire.io/odinserj/32/648_2.png) [@odinserj](https://discuss.hangfire.io/u/odinserj)\
**Post date:** [March 24, 2021, 1:09pm UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078/5 "2021-03-24T13:09:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![IdWeb](https://avatars.discourse-cdn.com/v4/letter/i/ea666f/32.png) [@IdWeb](https://discuss.hangfire.io/u/IdWeb)\
**Post date:** [March 24, 2021, 3:59pm UTC](https://discuss.hangfire.io/t/heavy-sql-usage-reported-by-host/7078/6 "2021-03-24T15:59:53Z")

</div>

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