Query processor could not produce a query plan

question
1

Hello,

I see this error when I go to my recurring jobs:

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

Can anyone tell me the best way to diagnose the error?

Thanks,
Scott

2

Scott,

I ran into the same problem you are having a few weeks back. When selecting ‘Recurring Jobs’ dashboard item, it appears that HangFire is performing a FORCESEEK (I cannot remember if this is the exact hint). I initially thought it had to do with missing unique key references on the [HangFire].[Hash] table however, setting the [Key] field of the [Hangfire].[Hash] table to NVARCHAR(100) resolved this issue.

I am still looking into exactly why this is causing a problem but I hope that this solution will help resolve your problem.

  • Joshua
3

Hi Joshua,

I’m getting the same issue on all the side menu items in the dashboard, but the [Key] field in [Hangfire].[Hash] is already set to NVARCHAR(100).

Any other ideas that could help to resolve this?

4

Hey Nils,

What version of Hangfire are you using? I recently upgraded to v.1.5.2 and had similar issues which required me to added specific nonclustered indexes and make most of the NVARCHAR fileds that I converted to VARCHAR back to NVARCHAR…

To give you some context, I manage the HangFire schema manually in order to publish my database project with a db_owner and run my application as a db_read db_write. With this in mind, make sure to have the ‘prepareHangfireSchemaIfNecessary’ set to false on application start. This is a sqlServerStorageOptions property.

Take a look at the Hangfire source code Install.sql and make sure your schema contains the unique nonclustered indexes that are present in this script file. Also check to see if there are other fields within the Hangfire schema that is VARCHAR and change them one at a time to NVARCHAR. Adding the UNCI and change my fields back to NVARCHAR resolved this issue.

Please let me know if you have any questions.

  • Joshua
5

Hi Joshua

Sorry for the late reply - I did eventually get back to this.

I also manage the HangFire schema manually so that I can control the db permissions.

I worked my way through the Install.sql script and your instinct was correct - This key was missing from my database:

CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [HangFire].[Job] ([StateName] ASC);

That fixed the issue.

Thanks for your help!

  • Nils
1 Like
6

I read install script and execute only the create index and now Dashboard is working.
I am get error after migrate sql server from 2008 to 2012.