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...
>> 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.
>
>.
>|||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...
> 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...
> >> 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.
> >
> >
> >.
> >|||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:
> >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)
>

Indexes and File groups

Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John|||Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.|||Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John|||Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

johnbandettini@.yahoo.co.uk wrote:
> Jens
> Yes, rebuilding the clustered index will move the table. You can also
> do it through enterprise manager, using design table, this rebuilds
the
> index for you.
>
> Regards
>
> John