Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)
If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)
Showing posts with label ignored. Show all posts
Showing posts with label ignored. Show all posts
Monday, March 19, 2012
index on computed column ignored
Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end
)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end
)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)
index on computed column ignored
Hello,
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)
I have a non-unique, non-clustered index on a computed column:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
[cweb].[tbVehicleMainTest]
(
[ComputedYear] ASC
)
INCLUDE ( [Make],
[Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
The column computedYear is a "Presisted computed column" as:
(case when [DateFirstRegistered]<=[DateOfManufacture] then
datepart(year,[DateFirstRegistered]) when
[DateFirstRegistered]>[DateOfManufacture] then datepart(year,
[DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
It basically year for some datetime columns and choose the year.
When I do query:
set QUOTED_IDENTIFIER on;
set ANSI_NULLS on;
set ANSI_PADDING on;
set ANSI_WARNINGS on;
set ARITHABORT on;
set CONCAT_NULL_YIELDS_NULL on;
set NUMERIC_ROUNDABORT off;
select [Make],[Model] from cweb.tbVehicleMainTest where
[ComputedYear]=1
Sql Server uses index scan instead of index seek.
Would someone mind to point out what is wrong with my method? (I am
using sql 2005)If you use an index hint to force it to use that index does it perform
better? If I am understanding your code, I think you should also INCLUDE for
date columns.
Jason Massie
http://statisticsio.com
<DAXU@.hotmail.com> wrote in message
news:9c39ea60-7c62-42b5-8385-763b0e94f90b@.a28g2000hsc.googlegroups.com...
> Hello,
> I have a non-unique, non-clustered index on a computed column:
> SET ARITHABORT ON
> SET CONCAT_NULL_YIELDS_NULL ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE NONCLUSTERED INDEX [IX_tbVehicleMain_ComputedYearTest] ON
> [cweb].[tbVehicleMainTest]
> (
> [ComputedYear] ASC
> )
> INCLUDE ( [Make],
> [Model]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
> SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
> ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
> [PRIMARY]
> The column computedYear is a "Presisted computed column" as:
> (case when [DateFirstRegistered]<=[DateOfManufacture] then
> datepart(year,[DateFirstRegistered]) when
> [DateFirstRegistered]>[DateOfManufacture] then datepart(year,
> [DateOfManufacture]) else datepart(year,[DateFirstRegistered]) end)
> It basically year for some datetime columns and choose the year.
> When I do query:
> set QUOTED_IDENTIFIER on;
> set ANSI_NULLS on;
> set ANSI_PADDING on;
> set ANSI_WARNINGS on;
> set ARITHABORT on;
> set CONCAT_NULL_YIELDS_NULL on;
> set NUMERIC_ROUNDABORT off;
> select [Make],[Model] from cweb.tbVehicleMainTest where
> [ComputedYear]=1
> Sql Server uses index scan instead of index seek.
> Would someone mind to point out what is wrong with my method? (I am
> using sql 2005)
Monday, March 12, 2012
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:31Xam, "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
|||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-78388091880a.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-783-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.
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:31Xam, "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
|||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-78388091880a.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-783-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.
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=e
> 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-78388091880a.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-783-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:
> > 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
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.
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=e
> 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-78388091880a.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-783-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:
> > 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
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.
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.
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.
Subscribe to:
Posts (Atom)