Weird Optimizer Choice

[Philip Yale]

I'm very puzzled by the choice of NC index being made by the optimizer
in this example. I don't actually think it should use an NC index at
all.

I have:

Table: CustomerStatus_ T
Single data page
19 records
Clustered Index on CustomerStatusI D:

CREATE TABLE [CustomerStatus_ T] (
[CustomerStatusI D] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[code] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CodeAlt] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Ordinal] [int] NULL ,
[Default] [int] NULL ,
[Display] [bit] NOT NULL ,
[StatusType] [varchar] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[DateCreated] [smalldatetime] NULL ,
[DateUpdated] [smalldatetime] NULL ,
[DateArchived] [smalldatetime] NULL ,
CONSTRAINT [PK_ROMS_Custome rStatus] PRIMARY KEY CLUSTERED
(
[CustomerStatusI D]
) ON [PRIMARY]
) ON [PRIMARY]
If I run the following query, it does exactly what I expect and scans
the clustered index:

SELECT customerStatusI D, [Name] FROM CustomerStatus_ T
WHERE dateArchived IS NULL
AND Display = 1
AND StatusType = 'Q‘

and gives the following QEP and IO statistics:

|--Clustered Index Scan
(OBJECT:([Reach_Roms].[dbo].[CustomerStatus_ T].[PK_ROMS_Custome rStatus]),
WHERE:(([CustomerStatus_ T].[DateArchived]=NULL AND
[CustomerStatus_ T].[StatusType]='Q') AND
Convert([CustomerStatus_ T].[Display])=1))
Table 'CustomerStatus _T'. Scan count 1, logical reads 2, physical
reads 0,
read-ahead reads 0.
If I now put a NC index on the statustype column:
create index ix_nci_statusty pe on customerstatus_ t(statustype)

the query plan changes to:

SELECT customerStatusI D, [Name] FROM CustomerStatus_ T
WHERE dateArchived IS NULL
AND Display = 1
AND StatusType = 'Q‘
|--Filter(WHERE:([CustomerStatus_ T].[DateArchived]=NULL AND
Convert([CustomerStatus_ T].[Display])=1))
|--Bookmark Lookup(BOOKMARK :([Bmk1000]),
OBJECT:([Reach_Roms].[dbo].[CustomerStatus_ T]))
|--Index
Seek(OBJECT:([Reach_Roms].[dbo].[CustomerStatus_ T].[ix_nci_statusty pe]),
S EEK:([CustomerStatus_ T].[StatusType]='Q') ORDERED
FORWARD)
Table 'CustomerStatus _T'. Scan count 1, logical reads 7,
physical reads 0, read-ahead reads 0.

For some bizarre reason, the optimizer thinks that a NC index lookup
on a single-page table, which ultimately costs 7 IOs, is cheaper than
a table (or Clustered Index) scan of a single page. Why? The
showplan cost also shows that it expects the NC index to be cheaper
(which is presumably why it goes and uses it), but even after running
UPDATE STATISTICS on the table it still chooses the same idiotic query
plan.

Any thoughts, or has anyone seen similar behaviour before, and can
anyone please explain it to me?

p.s. I don't actually WANT to put a NC index on this table, but I
noticed the behaviour by accident which is why I'm asking the question
:-)

[Gert-Jan Strik]

With such a small table, the results mean almost nothing.

The way the optimizer works is that it first looks for any obvious query
plans, and a if well performing query plan (based on estimations) is
found, it is used. In that case no excessive search is done to find an
even better plan.

And that is probably what happens here.

For any serious evaluation, make sure your table has a lot of pages, say
100 or so...

Gert-Jan
Philip Yale wrote:

I'm very puzzled by the choice of NC index being made by the optimizer
in this example. I don't actually think it should use an NC index at
all.

I have:

Table: CustomerStatus_ T
Single data page
19 records
Clustered Index on CustomerStatusI D:

CREATE TABLE [CustomerStatus_ T] (
[CustomerStatusI D] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[code] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CodeAlt] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Ordinal] [int] NULL ,
[Default] [int] NULL ,
[Display] [bit] NOT NULL ,
[StatusType] [varchar] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[DateCreated] [smalldatetime] NULL ,
[DateUpdated] [smalldatetime] NULL ,
[DateArchived] [smalldatetime] NULL ,
CONSTRAINT [PK_ROMS_Custome rStatus] PRIMARY KEY CLUSTERED
(
[CustomerStatusI D]
) ON [PRIMARY]
) ON [PRIMARY]

If I run the following query, it does exactly what I expect and scans
the clustered index:

SELECT customerStatusI D, [Name] FROM CustomerStatus_ T
WHERE dateArchived IS NULL
AND Display = 1
AND StatusType = 'Q‘

and gives the following QEP and IO statistics:

|--Clustered Index Scan
(OBJECT:([Reach_Roms].[dbo].[CustomerStatus_ T].[PK_ROMS_Custome rStatus]),
WHERE:(([CustomerStatus_ T].[DateArchived]=NULL AND
[CustomerStatus_ T].[StatusType]='Q') AND
Convert([CustomerStatus_ T].[Display])=1))
Table 'CustomerStatus _T'. Scan count 1, logical reads 2, physical
reads 0,
read-ahead reads 0.
If I now put a NC index on the statustype column:
create index ix_nci_statusty pe on customerstatus_ t(statustype)

the query plan changes to:

SELECT customerStatusI D, [Name] FROM CustomerStatus_ T
WHERE dateArchived IS NULL
AND Display = 1
AND StatusType = 'Q‘
|--Filter(WHERE:([CustomerStatus_ T].[DateArchived]=NULL AND
Convert([CustomerStatus_ T].[Display])=1))
|--Bookmark Lookup(BOOKMARK :([Bmk1000]),
OBJECT:([Reach_Roms].[dbo].[CustomerStatus_ T]))
|--Index
Seek(OBJECT:([Reach_Roms].[dbo].[CustomerStatus_ T].[ix_nci_statusty pe]),
S EEK:([CustomerStatus_ T].[StatusType]='Q') ORDERED
FORWARD)
Table 'CustomerStatus _T'. Scan count 1, logical reads 7,
physical reads 0, read-ahead reads 0.

For some bizarre reason, the optimizer thinks that a NC index lookup
on a single-page table, which ultimately costs 7 IOs, is cheaper than
a table (or Clustered Index) scan of a single page. Why? The
showplan cost also shows that it expects the NC index to be cheaper
(which is presumably why it goes and uses it), but even after running
UPDATE STATISTICS on the table it still chooses the same idiotic query
plan.

Any thoughts, or has anyone seen similar behaviour before, and can
anyone please explain it to me?

p.s. I don't actually WANT to put a NC index on this table, but I
noticed the behaviour by accident which is why I'm asking the question
:-)

--
(Please reply only to the newsgroup)

[Philip Yale]

Gert-Jan Strik <so***@toomuchs pamalready.nl> wrote in message news:<40******* ********@toomuc hspamalready.nl >...

With such a small table, the results mean almost nothing.

The way the optimizer works is that it first looks for any obvious query
plans, and a if well performing query plan (based on estimations) is
found, it is used. In that case no excessive search is done to find an
even better plan.

And that is probably what happens here.

For any serious evaluation, make sure your table has a lot of pages, say
100 or so...

Gert-Jan
Philip Yale wrote:

I'm very puzzled by the choice of NC index being made by the optimizer
in this example. I don't actually think it should use an NC index at
all......

Thanks for that, Gert. I can imagine that what you say is true.
However, I wonder at what point the optimizer decides that a table is
now big enough to warrant a bit more thought when selecting a plan?
Alternatively, when does a plan become expensive enough that the
optimizer looks for better ones? These are rhetorical questions, I
know, and the answer is probably hidden away somewhere where only
engineers know (or care) how to find it. Still, this sort of thing
interests me I'm afraid (sad, isn't it?)

[Gert-Jan Strik]

I have no hard evidence for it, but I have read in this newsgroup that
the limit for the obvious query plans is about 1 second (estimated
elapsed time).

Gert-Jan
Philip Yale wrote:
<snip>

Thanks for that, Gert. I can imagine that what you say is true.
However, I wonder at what point the optimizer decides that a table is
now big enough to warrant a bit more thought when selecting a plan?
Alternatively, when does a plan become expensive enough that the
optimizer looks for better ones? These are rhetorical questions, I
know, and the answer is probably hidden away somewhere where only
engineers know (or care) how to find it. Still, this sort of thing
interests me I'm afraid (sad, isn't it?)

--
(Please reply only to the newsgroup)