August 25, 2003 at 2:49 am
I'm uncertain about the following:
a query makes SQL server do an index seek on
1) clustered index
2) nonclustered index
Which one makes SQL server retrieve the most pages ?
thanks !
August 25, 2003 at 3:42 am
It all depends how many rows/pages your query returns. Clustered index seek is good for range selection (means more data/pages) and non-clustered for a particular (less) records.
August 25, 2003 at 4:13 am
himan0110,
I'm not quite with you ... Let's try it with an example.
say: 1 row = 1000 bytes
insert into ADStable
select * from ODStable1 a
where exists (select PK from ODStable2 b where a.PK = b.PK
and b.a_key_field='SOMETHING')
(b.a_key_field='SOMETHING' is less then 5% of the rows, so no table scan)
both ODStables have clustered index on PK
1.000.000 rows in ODStable
How many pages are retrieved for this index seek ? (and how many for the data transfer)
Only indexes or also datapages from both tables ?
thanks in advance
Edited by - hbkdba on 08/25/2003 05:42:44 AM
August 26, 2003 at 7:13 am
can anybody give info on this ?
It's an important matter to us.
Thanks a lot.
August 26, 2003 at 8:03 am
With a clustered index, the data is at the leaf level of the index, so the index and the datapages are one in the same. The index is not a separate object as with a non-clustered index.
August 26, 2003 at 11:57 pm
Hi hbkdba,
quote:
a query makes SQL server do an index seek on1) clustered index
2) nonclustered index
Which one makes SQL server retrieve the most pages ?
the bigger index. For objects like indexes in SQL Server are stored in 8kb pages, the smaller the index the less pages you need to store it.
So when your one of your indexes is bigger, SQL Server must read more pages all else being equal.
However, if I remember correct, a nonclustered index requires one read for the index and another one to retrieve the data page, while a clustered is with one read at at data
Cheers,
Frank
Edited by - Frank Kalis on 08/26/2003 11:57:56 PM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 27, 2003 at 12:44 am
hbkdba, Kalen Delaney's book "Inside Microsoft SQL Server 2000", chapter 8 may help a bit.
Lastly, although not ask for, you can rearrange your query to
insert into ADStable
select a.* from ODStable2 b
Inner Join ODStable1 a
ON b.a_key_field='SOMETHING' And
a.PK = b.PK
August 27, 2003 at 4:45 am
Also it is important how many data you need from the row...
If you need only the fields mentioned in the query you could use covering indexes....
If the rows are very large this will probably help speed up the query because if all data is found in the index SQL will not touch the table itself only the index.....
August 28, 2003 at 8:14 am
insert into ADStable
select * from ODStable1 a
where exists (select PK from ODStable2 b where a.PK = b.PK
and b.a_key_field='SOMETHING')
ODStable2 will be only accessed by index (seek), the data is not needed. Will the datapages be read (while seeking the index) ?
REMARK: both indexes (PK) are clustered and contain the same columns.
Thanks a lot,
August 29, 2003 at 12:49 am
quote:
Will the datapages be read (while seeking the index) ?
Yes, since b's index is clustered. If it was nonclustered, the index seek would be able to use the leaf page of the NC index for data retrieval and the bookmark lookup which Frank referred to earlier would not be needed. This is what is commonly called a covering index; where all data needed for the JOIN condition and/or SELECT is housed in the index itself. So, in this particular query, on this particular set of indexes, the non-clustered index would probably be faster.
September 3, 2003 at 12:23 pm
thanks for all the replies
September 4, 2003 at 4:42 am
Point blank a nonclustered index will always read more pages as oppossed to a clustered if there is a clustered index on the table as well.
The reason is the nonclustered index use reference points to the clustered index and must cross it to get to the exact leaf pages needed.
September 4, 2003 at 6:24 am
You shouldn't care about number of pages retrieved but you should care about logical IO which is the number of times SQL had to hit a page. To see this run "set statistics IO on", run your query and look at messages.
It is extremely more efficient to have a covering non-clustered index whether or not the table is clustered. As the clustered column(s) is used as the identifier in all NCI's, if you can get away with utilizing the fuller pages of the NCI without the need to go to the leaf level, it will be less logical IO and much faster.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 4, 2003 at 6:49 am
quote:
Point blank a nonclustered index will always read more pages as oppossed to a clustered if there is a clustered index on the table as well.The reason is the nonclustered index use reference points to the clustered index and must cross it to get to the exact leaf pages needed.
Except in the case of a covering index, in which case the bookmark lookup from the NC index to the clustered index leaf page (data) isn't required...
September 4, 2003 at 8:36 am
Darn it brain is fried with the new baby here. What do you mean by covering index, jpipes? For some reason my mind has gone blank (happening a lot recently too).
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply