Microsoft SQL Server forum - question on index used by optimiser

Author

question on index used by optimiser

paul

2005-06-28, 9:23 am

Hi, i have a table like this
CREATE TABLE dbo.test
(
num int NOT NULL,
ename char(80),
eadress char(200),
archived char(1)
PRIMARY KEY CLUSTERED (num)
)

create index i_archived on dbo.test(archived)

the are 500000 rows in this table, and the archived field contain 15000 'Y'
and 485000 'N'

When i issue a select * from test where archived='Y',
the path choosed is the index scan clustered and not the index i_archived

the stats are updated every day.

did i miss something ?

thx

Gert-Jan Strik

2005-06-28, 8:23 pm

paul wrote:
>
> Hi, i have a table like this
> CREATE TABLE dbo.test
> (
> num int NOT NULL,
> ename char(80),
> eadress char(200),
> archived char(1)
> PRIMARY KEY CLUSTERED (num)
> )
>
> create index i_archived on dbo.test(archived)
>
> the are 500000 rows in this table, and the archived field contain 15000 'Y'
> and 485000 'N'
>
> When i issue a select * from test where archived='Y',
> the path choosed is the index scan clustered and not the index i_archived
>
> the stats are updated every day.
>
> did i miss something ?
>
> thx

You probably did not miss anything. Does the tables have less than 15000
pages? In that case, SQL-Server probably thinks it is cheaper to scan
the table than it is to lookup 15000 rows based on the index. Which plan
is fastest will in practice depend on the amount of cached data. With a
cold (empty) cache, scanning the table will probably be faster. If you
know you have a hot cache for this query, then you could consider adding
an index hint.

HTH,
Gert-Jan

Erland Sommarskog

2005-06-28, 8:24 pm

paul (nomail@nomail.com) writes:
> Hi, i have a table like this
> CREATE TABLE dbo.test
> (
> num int NOT NULL,
> ename char(80),
> eadress char(200),
> archived char(1)
> PRIMARY KEY CLUSTERED (num)
> )
>
> create index i_archived on dbo.test(archived)
>
> the are 500000 rows in this table, and the archived field contain 15000
> 'Y' and 485000 'N'
>
>
> When i issue a select * from test where archived='Y',
> the path choosed is the index scan clustered and not the index i_archived
>
> the stats are updated every day.
>
> did i miss something ?

The fine print that a non-clustered index is not always useful.

The row size is 286 bytes (columns + null bit mask). That makes for 28
28 rows per page when full. For 500000 rows that's 17854 pages to
scan. For the non-clustered index, there's 15000 bookmark lookups + the
index to scan, but that is not likely be more than some 100 pages.
Really why the optimizer goes for the clustered index, I don't know,
but such things like read-ahead reads being possible may be part of
it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links