Indexing

I have a 25gb database. one of the tables has 45Million
rows. It is located on a datafile all on its own and on a
seperate drive. Now I have 7 indexes one of which is a
cluster. Can some one please tell me why every query run
defaults to the clustered index. Is there a bug or what.
System is WIN2K ADV ED with 8Gb RAM Quad 4.2 Xeon AWE
enabled. 7GB allocated to SQL. SQL 2000 ENT ED both SP3
Thanks inadvace.Query Optimizer selects proper index to every query if you don't use index
hint.
I think you may check the query plans of all queries and the statistics of
non-clustered indexes.
Hanky
"Ryan" <anonymous@.discussions.microsoft.com> wrote in message
news:45f601c4801a$06c252c0$a301280a@.phx.gbl...
> I have a 25gb database. one of the tables has 45Million
> rows. It is located on a datafile all on its own and on a
> seperate drive. Now I have 7 indexes one of which is a
> cluster. Can some one please tell me why every query run
> defaults to the clustered index. Is there a bug or what.
> System is WIN2K ADV ED with 8Gb RAM Quad 4.2 Xeon AWE
> enabled. 7GB allocated to SQL. SQL 2000 ENT ED both SP3
> Thanks inadvace.|||I dont use hints and the stats are updated regularly but
still it uses the cluster, which is not the optimal index.
>--Original Message--
>Query Optimizer selects proper index to every query if
you don't use index
>hint.
>I think you may check the query plans of all queries and
the statistics of
>non-clustered indexes.
>Hanky
>
>"Ryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:45f601c4801a$06c252c0$a301280a@.phx.gbl...
a[vbcol=seagreen]
>
>.
>|||Could you inform me the query plan on option 'set showplan_all on' and the
result of 'exec sp_helpindex TableName'?
<anonymous@.discussions.microsoft.com> wrote in message
news:4a5c01c48047$5290ef40$a501280a@.phx.gbl...[vbcol=seagreen]
> I dont use hints and the stats are updated regularly but
> still it uses the cluster, which is not the optimal index.
> you don't use index
> the statistics of
> message
> a|||On Wed, 11 Aug 2004 20:11:14 -0700, Ryan wrote:

>I have a 25gb database. one of the tables has 45Million
>rows. It is located on a datafile all on its own and on a
>seperate drive. Now I have 7 indexes one of which is a
>cluster. Can some one please tell me why every query run
>defaults to the clustered index. Is there a bug or what.
>System is WIN2K ADV ED with 8Gb RAM Quad 4.2 Xeon AWE
>enabled. 7GB allocated to SQL. SQL 2000 ENT ED both SP3
>Thanks inadvace.
Hi Ryan,
Are the queries using ONLY the clustered index, or arre they using the
clustered index in addition to one or more of the other indexes? I think
it's the latter.
The clustered index defines how the data in the table is physically
organised. All non-clustered indexes combine the data from the index
columns with the corresponding data in the clustered index. That data is
then used to locate the row. A non-clustered index will never be used on
it's own, but always in combination with the clustered index (unless all
rows requested are in either the index or the clustered index; in that
case there's no need to locate the rest of the data).
If you run the following query with the option to show the execution plan,
you'll see (checking the plan from right to left) that an index seek on
the non-clustered index is used to find the rows matching the where
clause, followed by a bookmark lookup on the clustered index to return the
whole row.
use pubs
go
select * from authors
where au_lname = 'White'
and au_fname = 'Johnson'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,
Hugo
"Hugo Kornelis" wrote:

> On Wed, 11 Aug 2004 20:11:14 -0700, Ryan wrote:
>
> Hi Ryan,
> Are the queries using ONLY the clustered index, or arre they using the
> clustered index in addition to one or more of the other indexes? I think
> it's the latter.
> The clustered index defines how the data in the table is physically
> organised. All non-clustered indexes combine the data from the index
> columns with the corresponding data in the clustered index. That data is
> then used to locate the row. A non-clustered index will never be used on
> it's own, but always in combination with the clustered index (unless all
> rows requested are in either the index or the clustered index; in that
> case there's no need to locate the rest of the data).
> If you run the following query with the option to show the execution plan,
> you'll see (checking the plan from right to left) that an index seek on
> the non-clustered index is used to find the rows matching the where
> clause, followed by a bookmark lookup on the clustered index to return the
> whole row.
> use pubs
> go
> select * from authors
> where au_lname = 'White'
> and au_fname = 'Johnson'
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>