Hangfire: 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

[ravenet]

Hi,

Seem Hangfire version 1.6.17 is killing whole application as below error.

ERROR 2018-01-10 05:50:34,175 105805ms Worker LogException - Error occurred during execution of 'Worker #316fdd97' process. Execution will be retried (attempt #8) in 00:00:51 seconds.
System.Data.SqlClient.SqlException (0x80131904): 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.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior)
at Dapper.SqlMapper.d__1251.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at Dapper.SqlMapper.QueryT
at Hangfire.SqlServer.SqlServerJobQueue.DequeueUsingTransaction(String[] queues, CancellationToken cancellationToken)
at Hangfire.SqlServer.SqlServerJobQueue.Dequeue(String[] queues, CancellationToken cancellationToken)
at Hangfire.SqlServer.SqlServerConnection.FetchNextJob(String[] queues, CancellationToken cancellationToken)
at Hangfire.Server.Worker.Execute(BackgroundProcessContext context)
at Hangfire.Server.AutomaticRetryProcess.Execute(BackgroundProcessContext context)
ClientConnectionId:afd9f08b-6364-49f1-9e18-6e7979c8ef7d
Error Number:8622,State:1,Class:16

Can you please help us to fix this annoying error.

Thank you

[castroal]

I have the same issue usingMicrosoft SQL Server 2012 - 11.0.5058.0 (X64) when I try to add a recurring job (Hangfire.RecurringJob.AddOrUpdate())

Hangfire version 1.6.12 (net45)

Update:
With Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) seems to work correctly.

[iSatishYadav]

Is your issue resolved? Have you created all the indexes necessary for Hangfire SQL Storage?

[AlexeyBogdan95]

@castroal did you manage to fix the issue?

[castroal]

After updating to the latest version of Hangfire it seemed that the problem was solved but, after a while, I had the same error.

After a little bit of investigation I found that the error occurs if there are a lot (millions) of records in the '[Hangfire].[State]' table. Deleting all the records solved the issue for me.

[AlexeyBogdan95]

@castroal Am I right that you're using Hangfire.SqlServer 1.6.23?

[odinserj]

Please tell me exact error messages you see to understand what query produce the error. Originally it was related to the fetching query, and is likely caused by missing indexes or different collations. But since deleting all the State entries helped you, the problem is likely to be caused by another query.

[AlexeyBogdan95]

@odinserj The issue is reproduced in SQL Server 2012. As for SQL Server 2012, it works fine. Currently, I'm using Hangfire.Core 1.6.22 and Hangfire.SqlServer 1.6.22
Exception is

ERROR Hangfire.Server.Worker - Error occurred during execution of 'Worker #0bd485e7' process. Execution will be retried (attempt #178) in 00:05:00 seconds.
System.Data.SqlClient.SqlException (0x80131904): 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.
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
  at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
  at System.Data.SqlClient.SqlDataReader.get_MetaData()
  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
  at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior)
  at Dapper.SqlMapper.<QueryImpl>d__125`1.MoveNext()
  at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
  at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
  at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
  at Hangfire.SqlServer.SqlServerJobQueue.DequeueUsingTransaction(String[] queues, CancellationToken cancellationToken)
  at Hangfire.SqlServer.SqlServerJobQueue.Dequeue(String[] queues, CancellationToken cancellationToken)
  at Hangfire.Server.Worker.Execute(BackgroundProcessContext context)
  at Hangfire.Server.AutomaticRetryProcess.Execute(BackgroundProcessContext context)
ClientConnectionId:f9e974ba-5c1e-4510-9714-92638597c43b
Error Number:8622,State:1,Class:16
2019-02-27 05:01:32,438 [Worker #8e66cdbf] ERROR Hangfire.Server.Worker - Error occurred during execution of 'Worker #8e66cdbf' process. Execution will be retried (attempt #178) in 00:05:00 seconds.

[odinserj]

This error may be caused by the following things, please tell me which one of the following conditions is true for your case.

• IX_HangFire_JobQueue_QueueAndFetchedAt that should be used for a seek operation is absent.
• Disabled row locks for an index, ensure all of the indexes on the JobQueue allow row locks to be taken (index details).
• Collation of the column differs from the DATABASE_DEFAULT value.

[castroal]

@AlexeyBogdan95 I am using Hangfire.SqlServer.1.6.21
@odinserj Yes the problem might be in another query, but why are you using query hints? Just let SqlServer decide on its own.

[odinserj]

@castroal, if you have any of these problems, SQL Server will be unable to produce the correct query anyway. If row locks disabled, then our fetch transaction will block around 200 jobs instead of one, and if you have long-running jobs, then your processing will be stalled from time to time. If you don't have the corresponding index or using different collation, then INDEX SEEK operation will not be performed, and SQL Server will scan entire table on each dequeue that's very bad if you are using multiple queues.

And the worst thing is that in this case those errors will be silently ignored by SQL Server, and it's very hard to consider what's going on.

[AlexeyBogdan95]

@odinserj I fixed the issue.
My steps:
1) drop all the HangFire tables in DB
2) update to 1.6.23

When I ran the app again, the tables were created and I didn't see the error

[pictoric]

I'm getting this same error on 1.7.6 on SQL Server 2016 all of a sudden. The index is there and seems to be correct.

[NageshKorimi]

After re-installing the script the error got fixed

[berdem]

For the ones drop in here and still stuck with the error, another thing to check is Compatibility level of you database.
Increase your compatibility level of db at least 100 (SQL Server2008).
If your database compatibility level is lets say 80 (SQL Server 2000) you will see this issue. Increasing it to 100 (ensure everything works ok with 100 ) will solve the problem.

ALTER DATABASE [DBNAME]
SET COMPATIBILITY_LEVEL = 100