Query processor could not produce a query plan because of the hints defined in this query.

Question

Query processor could not produce a query plan because of the hints defined in this query.

dndaughtery

SSChampion

Points: 11290
More actions
November 30, 2015 at 10:14 am

#322935

I have created a filtered query and I'm trying to use it in my query in the following way:
Select count(*)
FROM dbo.MyTableAS MC
WITH (NOLOCK, INDEX (FDX_spG_NU_QA_V12))
JOIN dbo.Join_Table (NOLOCK)AS NU
ON MC.col1 = NU.col1
AND MC.col2 = NU.col2
AND MC.col3 = NU.col3
But I get the following error:
Msg 8622, Level 16, State 1, Line 2
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 someone help me out with what I'm doing wrong?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Do you really need the hints? Do you understand the problems of NOLOCK? Why do you need to specify the index? Wouldn't the query processor use it? Why not?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dndaughtery SSChampion Points: 11290 More actions · Answer 2

Yeah we need to use NOLOCK. Don't really need the query hint for the index. Tried without it and they were used....was just trying to make sure the new one's were used.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You shouldn't use an index hint to be sure that the index is being used. If it's useful, it should be used automatically. If it's not used, maybe the query becomes more expensive or you need to find the cause of not recreating the execution plan.

NOLOCK is dangerous as it can return incorrect information. The sad part is that many companies mandate to use it in every query on every table. That's simply a bad decision that some have to follow.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

You are missing the word WITH on the second nolock.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 5

Luis Cazares (11/30/2015)
Do you really need the hints? Do you understand the problems of NOLOCK? Why do you need to specify the index? Wouldn't the query processor use it? Why not?

Quick question, can you explain why you need to use NOLOCK?

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Short answer, you've created a filtered index and tried to force SQL to use it to execute a query that does not have a predicate matching the index's filter predicte, hence the index hint makes it impossible for the query optimiser to generate a plan. It cannot use the index you've told it that it must use.

For a query to be able to use a filtered index, the query must contain a predicate that is a match or a subset of the predicate specified in the index. Your query has no WHERE clause predicate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101121 More actions · Answer 7

In more detail, index "FDX_spG_NU_QA_V12" does not contain at least one of the columns:

MC.col1 / MC.col2 / MC.col3

If you believe such an index could be helpful enough to this query, you should look at adding (INCLUDEing) the missing column(s) in that index. Then you can re-try the query.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ScottPletcher (11/30/2015)
In more detail, index "FDX_spG_NU_QA_V12" does not contain at least one of the columns:
MC.col1 / MC.col2 / MC.col3

That won't cause the reported error. He said it's a filtered index, but the query has no filter and hence can't match a filtered index. Hinting a non-covering index doesn't throw that error, it just means that the optimiser adds a key lookup to the plan.

CREATE TABLE t1 (

Col1 INT,

Col2 INT

)

CREATE INDEX idx_Noncovering ON t1 (Col1)

CREATE INDEX idx_Filtered ON t1 (Col1) WHERE col1 < 0

SELECT Col1, Col2 FROM dbo.t1 WITH (INDEX (idx_Noncovering)) -- runs fine

SELECT Col1, Col2 FROM dbo.t1 WITH (INDEX (idx_Filtered)) -- throws error 8622

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass