Index hints ignored

Under what conditions will index hints be ignored ? I forced a query to use
index hints and it gave me a warning as such
Warning: Index hints supplied for view 'dbo.view1' will be ignored.
and did not use the hints..On Dec 28, 7:31=A0am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to us=[/vbcol
]
e[vbcol=seagreen]
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on|||No, please read the documentation for SET FORCEPLAN. It is not related to
this issue at all. SET FORCEPLAN forces a particular join order and
(usually) a join type, but will not override the optimizers determination
that a hint cannot be used.
Hassan, there is no simple set of guidelines. In general, a hint will be
ignored it if is impossible or meaningless in the situation where you are
trying to use it. For your particular case, trying to force an index on a
view to be used, you must also use the NOEXPAND hint or the hint will be
ignored.
Please read the documentation on "Resolving Indexes on Views"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-7838
8091880a.htm
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"SB" <othellomy@.yahoo.com> wrote in message
news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
> Under what conditions will index hints be ignored ? I forced a query to
> use
> index hints and it gave me a warning as such
> Warning: Index hints supplied for view 'dbo.view1' will be ignored.
> and did not use the hints..
you can use set forcaplan on|||On Dec 28, 10:17 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> No, please read the documentation for SET FORCEPLAN. It is not related to
> this issue at all. SET FORCEPLAN forces a particular join order and
> (usually) a join type, but will not override the optimizers determination
> that a hint cannot be used.
> Hassan, there is no simple set of guidelines. In general, a hint will be
> ignored it if is impossible or meaningless in the situation where you are
> trying to use it. For your particular case, trying to force an index on a
> view to be used, you must also use the NOEXPAND hint or the hint will be
> ignored.
> Please read the documentation on "Resolving Indexes on Views"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/430593ce-5dec-48d5-86ed-78
3-88091880a.htm
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://sqlblog.com
> "SB" <othell...@.yahoo.com> wrote in message
> news:28d87e5a-3894-4bd7-945d-5ab728a0573d@.s8g2000prg.googlegroups.com...
> On Dec 28, 7:31 am, "Hassan" <has...@.test.com> wrote:
>
>
>
> you can use set forcaplan on
Maybe its join order but whenever the optimizer ignored index hints I
used forceplan and then it worked. Obviously there was a performance
gain otherwise I wouldn't go into so much trouble.