How can resolve the SQL exception 'Query processor could not produce a query plan because of the hints defined in this query.' caused by HangFire

Question

I am getting a SQL Exception every time I try to access the HangFire dashboard.

The message I am getting is

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

The culprit causing it is Hangfire.SqlServer.SqlServerMonitoringApi.GetStatistics() I found that the GetStatistics() method is running the following query

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT COUNT(Id)
FROM HangFire.Job WITH (NOLOCK, FORCESEEK)
WHERE StateName = N'Enqueued';

SELECT COUNT(Id)
FROM HangFire.Job WITH (NOLOCK, FORCESEEK)
WHERE StateName = N'Failed';

SELECT COUNT(Id)
FROM HangFire.Job WITH (NOLOCK, FORCESEEK)
WHERE StateName = N'Processing';

SELECT COUNT(Id)
FROM HangFire.Job WITH (NOLOCK, FORCESEEK)
WHERE StateName = N'Scheduled';

SELECT COUNT(Id)
FROM HangFire.Server WITH (NOLOCK);

SELECT SUM(s.[Value])
FROM
    (SELECT SUM([Value]) AS [Value]
     FROM HangFire.Counter WITH (NOLOCK, FORCESEEK)
     WHERE [Key] = N'stats:succeeded'
     UNION ALL
     SELECT [Value]
     FROM HangFire.AggregatedCounter WITH (NOLOCK, FORCESEEK)
     WHERE [Key] = N'stats:succeeded') AS s;

SELECT SUM(s.[Value])
FROM
    (SELECT SUM([Value]) AS [Value]
     FROM HangFire.Counter WITH (NOLOCK, FORCESEEK)
     WHERE [Key] = N'stats:deleted'
     UNION ALL
     SELECT [Value]
     FROM HangFire.AggregatedCounter WITH (NOLOCK, FORCESEEK)
     WHERE [Key] = N'stats:deleted') AS s;

SELECT COUNT(*)
FROM HangFire.[Set] WITH (NOLOCK, FORCESEEK)
WHERE [Key] = N'recurring-jobs';

Now when I copy the code and run it on my SSMS I get the same error and when I run it without the FORCESEEK hint a get result back.

Now my issue is that I can't do much about the code in the package, so is the something perhaps I can do on the SQL Server side to prevent this from happening or maybe the is something I can do to resolve this issue.

Environment

• Server: Windows Server 2019
• Runtime: .NET Core 3.1

HangFire

• Hangfire.AspNetCore 1.7.12
• Hangfire.SqlServer 1.7.12

Answer 1

Quite old post but may be helpful.

We need to recreate the Job indexes.

SET ANSI_PADDING ON
GO

/****** Object:  Index [CX_HangFire_Counter]    Script Date: 10/3/2023 12:39:34 PM ******/
CREATE CLUSTERED INDEX [CX_HangFire_Counter] ON [HangFire].[Counter]
(
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [IX_HangFire_Job_StateName]    Script Date: 10/3/2023 12:55:53 PM ******/
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [HangFire].[Job]
(
    [StateName] ASC
)
WHERE ([StateName] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object:  Index [IX_HangFire_Job_ExpireAt]    Script Date: 10/3/2023 12:55:33 PM ******/
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire].[Job]
(
    [ExpireAt] ASC
)
INCLUDE([StateName]) 
WHERE ([ExpireAt] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO