TABLE SCAN Vs INDEX SCAN/SEEK

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Jimmy Kavanag » Thu, 24 Aug 2000 04:00:00



assume i have a tabel called Customer with the following column

 cuLastName, nvarchar(30)

and that there is an index on the column.

Can anyone explain why SQL Server does a table scan for
 SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

but does an Index seek/scan for
 SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

the addition of one more letter triggers SQL to use the index where
i think the index should be used in both situations......

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Brian Mora » Thu, 24 Aug 2000 04:00:00


I should probally know this, but I'm not sure SQL keeps stats within a col.
Of course 'ka%' is more restrivtive than 'k%' so it may be doing the right
thing, but I didn't think it kept stats at that level of detail... a good
place to start is to verify if the index should be used is by forcing the
index you think should be used with (INDEX = NAME) after the table name.
This way you can look at the IO and time for the query with and without the
index to see if the index is in fact being helpful.

--
Brian Moran
CrossTier.com
MS SQL Server MVP
SQL Server Mag Columnist


Quote:> assume i have a tabel called Customer with the following column

>  cuLastName, nvarchar(30)

> and that there is an index on the column.

> Can anyone explain why SQL Server does a table scan for
>  SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

> but does an Index seek/scan for
>  SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

> the addition of one more letter triggers SQL to use the index where
> i think the index should be used in both situations......


 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Roy Harve » Thu, 24 Aug 2000 04:00:00


Jimmy,

Quote:>assume i have a tabel called Customer with the following column

> cuLastName, nvarchar(30)

>and that there is an index on the column.

From what you say later, a NON-CLUSTERED index.

Quote:>Can anyone explain why SQL Server does a table scan for
> SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

>but does an Index seek/scan for
> SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

The optimizer's estimates the selectivity of the two alternatives, and
comes up with different answers.  In the case of 'ka%' it decides that
there will be fewer I/O operations to read them via the index than to
read the entire table.  In the case of 'k%' it concludes that the
number of I/O operations is great enough to exceed the I/O of a table
scan.

Quote:>the addition of one more letter triggers SQL to use the index where
>i think the index should be used in both situations......

Make sure your index statistics are up to date, as well as your
service packs.

Roy

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Jimmy Kavanag » Fri, 25 Aug 2000 04:00:00


Hi Brian
I did as you suggested in QA:

select * from customer (INDEX = IX_Customer_Name) where cuLastname like 'K%'

This did make SQL do an Index seek for cuLastName >= 'K'.
I have looked at Roys answer as well and would like to state the following
as well:

The table in question only has 1200 rows in it. Therefore SQL may be
deciding it
is better to do a full table scan than the index seek for the

    WHERE cuLastName LIKE 'k%'

But i cannot see how running through 1000 records is going to be faster the
seeking
directly in the index......

I will next to a full reindex and see what happens.........


> I should probally know this, but I'm not sure SQL keeps stats within a
col.
> Of course 'ka%' is more restrivtive than 'k%' so it may be doing the right
> thing, but I didn't think it kept stats at that level of detail... a good
> place to start is to verify if the index should be used is by forcing the
> index you think should be used with (INDEX = NAME) after the table name.
> This way you can look at the IO and time for the query with and without
the
> index to see if the index is in fact being helpful.

> --
> Brian Moran
> CrossTier.com
> MS SQL Server MVP
> SQL Server Mag Columnist



> > assume i have a tabel called Customer with the following column

> >  cuLastName, nvarchar(30)

> > and that there is an index on the column.

> > Can anyone explain why SQL Server does a table scan for
> >  SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

> > but does an Index seek/scan for
> >  SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

> > the addition of one more letter triggers SQL to use the index where
> > i think the index should be used in both situations......

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Jimmy Kavanag » Fri, 25 Aug 2000 04:00:00


Thanks for your thoughts Roy.
In order to stop the thread splitting can u see my reply to Brians posting?

regards - Jimmy


Quote:> Jimmy,

> >assume i have a tabel called Customer with the following column

> > cuLastName, nvarchar(30)

> >and that there is an index on the column.

> From what you say later, a NON-CLUSTERED index.

> >Can anyone explain why SQL Server does a table scan for
> > SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

> >but does an Index seek/scan for
> > SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

> The optimizer's estimates the selectivity of the two alternatives, and
> comes up with different answers.  In the case of 'ka%' it decides that
> there will be fewer I/O operations to read them via the index than to
> read the entire table.  In the case of 'k%' it concludes that the
> number of I/O operations is great enough to exceed the I/O of a table
> scan.

> >the addition of one more letter triggers SQL to use the index where
> >i think the index should be used in both situations......

> Make sure your index statistics are up to date, as well as your
> service packs.

> Roy

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Tibor Karasz » Fri, 25 Aug 2000 04:00:00


The optimizer estimates that is has to return X number of rows. If it does that by
statistics, "magic values" or a combination (which is what I think) is another issue.

Let us say that the optimizer estimates that it will retrieve 20 rows.

"0 rows through a Non Clustered index means navigating the index tree (let us say that
this is 2 pages accesses) and 20 page accesses visiting each datapage which is pointed
out by the leaf level of the index.
But the table only occupies 10 pages. Why should SQL Server search though an index
resulting in 20 page accesses when it can do a table scan over 10 pages?

Above is how that optimizer reasons.

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.


> Hi Brian
> I did as you suggested in QA:

> select * from customer (INDEX = IX_Customer_Name) where cuLastname like 'K%'

> This did make SQL do an Index seek for cuLastName >= 'K'.
> I have looked at Roys answer as well and would like to state the following
> as well:

> The table in question only has 1200 rows in it. Therefore SQL may be
> deciding it
> is better to do a full table scan than the index seek for the

>     WHERE cuLastName LIKE 'k%'

> But i cannot see how running through 1000 records is going to be faster the
> seeking
> directly in the index......

> I will next to a full reindex and see what happens.........



> > I should probally know this, but I'm not sure SQL keeps stats within a
> col.
> > Of course 'ka%' is more restrivtive than 'k%' so it may be doing the right
> > thing, but I didn't think it kept stats at that level of detail... a good
> > place to start is to verify if the index should be used is by forcing the
> > index you think should be used with (INDEX = NAME) after the table name.
> > This way you can look at the IO and time for the query with and without
> the
> > index to see if the index is in fact being helpful.

> > --
> > Brian Moran
> > CrossTier.com
> > MS SQL Server MVP
> > SQL Server Mag Columnist



> > > assume i have a tabel called Customer with the following column

> > >  cuLastName, nvarchar(30)

> > > and that there is an index on the column.

> > > Can anyone explain why SQL Server does a table scan for
> > >  SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

> > > but does an Index seek/scan for
> > >  SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

> > > the addition of one more letter triggers SQL to use the index where
> > > i think the index should be used in both situations......

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Jimmy Kavanag » Fri, 25 Aug 2000 04:00:00


Hi Tibor
This would imply that if i change the query to

    SELECT cuLastName FROM Customer WHERE cuLastName LIKE 'k%'

SQLS7 should do an index seek, because all of the data it's looking for
(cuLastName)
is in the index pages........

I will check this out and see what the result is.....


> The optimizer estimates that is has to return X number of rows. If it does
that by
> statistics, "magic values" or a combination (which is what I think) is
another issue.

> Let us say that the optimizer estimates that it will retrieve 20 rows.

> "0 rows through a Non Clustered index means navigating the index tree (let
us say that
> this is 2 pages accesses) and 20 page accesses visiting each datapage
which is pointed
> out by the leaf level of the index.
> But the table only occupies 10 pages. Why should SQL Server search though
an index
> resulting in 20 page accesses when it can do a table scan over 10 pages?

> Above is how that optimizer reasons.

> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.



> > Hi Brian
> > I did as you suggested in QA:

> > select * from customer (INDEX = IX_Customer_Name) where cuLastname like
'K%'

> > This did make SQL do an Index seek for cuLastName >= 'K'.
> > I have looked at Roys answer as well and would like to state the
following
> > as well:

> > The table in question only has 1200 rows in it. Therefore SQL may be
> > deciding it
> > is better to do a full table scan than the index seek for the

> >     WHERE cuLastName LIKE 'k%'

> > But i cannot see how running through 1000 records is going to be faster
the
> > seeking
> > directly in the index......

> > I will next to a full reindex and see what happens.........



> > > I should probally know this, but I'm not sure SQL keeps stats within a
> > col.
> > > Of course 'ka%' is more restrivtive than 'k%' so it may be doing the
right
> > > thing, but I didn't think it kept stats at that level of detail... a
good
> > > place to start is to verify if the index should be used is by forcing
the
> > > index you think should be used with (INDEX = NAME) after the table
name.
> > > This way you can look at the IO and time for the query with and
without
> > the
> > > index to see if the index is in fact being helpful.

> > > --
> > > Brian Moran
> > > CrossTier.com
> > > MS SQL Server MVP
> > > SQL Server Mag Columnist



> > > > assume i have a tabel called Customer with the following column

> > > >  cuLastName, nvarchar(30)

> > > > and that there is an index on the column.

> > > > Can anyone explain why SQL Server does a table scan for
> > > >  SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

> > > > but does an Index seek/scan for
> > > >  SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

> > > > the addition of one more letter triggers SQL to use the index where
> > > > i think the index should be used in both situations......

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Jimmy Kavanag » Fri, 25 Aug 2000 04:00:00


Yes:
     SELECT cuLastName FROM Customer WHERE cuLastName LIKE 'k%'
does make it do an index seek......


> Hi Tibor
> This would imply that if i change the query to

>     SELECT cuLastName FROM Customer WHERE cuLastName LIKE 'k%'

> SQLS7 should do an index seek, because all of the data it's looking for
> (cuLastName)
> is in the index pages........

> I will check this out and see what the result is.....



> > The optimizer estimates that is has to return X number of rows. If it
does
> that by
> > statistics, "magic values" or a combination (which is what I think) is
> another issue.

> > Let us say that the optimizer estimates that it will retrieve 20 rows.

> > "0 rows through a Non Clustered index means navigating the index tree
(let
> us say that
> > this is 2 pages accesses) and 20 page accesses visiting each datapage
> which is pointed
> > out by the leaf level of the index.
> > But the table only occupies 10 pages. Why should SQL Server search
though
> an index
> > resulting in 20 page accesses when it can do a table scan over 10 pages?

> > Above is how that optimizer reasons.

> > --
> > Tibor Karaszi, SQL Server MVP
> > Please reply to the newsgroup only, not by email.



> > > Hi Brian
> > > I did as you suggested in QA:

> > > select * from customer (INDEX = IX_Customer_Name) where cuLastname
like
> 'K%'

> > > This did make SQL do an Index seek for cuLastName >= 'K'.
> > > I have looked at Roys answer as well and would like to state the
> following
> > > as well:

> > > The table in question only has 1200 rows in it. Therefore SQL may be
> > > deciding it
> > > is better to do a full table scan than the index seek for the

> > >     WHERE cuLastName LIKE 'k%'

> > > But i cannot see how running through 1000 records is going to be
faster
> the
> > > seeking
> > > directly in the index......

> > > I will next to a full reindex and see what happens.........



> > > > I should probally know this, but I'm not sure SQL keeps stats within
a
> > > col.
> > > > Of course 'ka%' is more restrivtive than 'k%' so it may be doing the
> right
> > > > thing, but I didn't think it kept stats at that level of detail... a
> good
> > > > place to start is to verify if the index should be used is by
forcing
> the
> > > > index you think should be used with (INDEX = NAME) after the table
> name.
> > > > This way you can look at the IO and time for the query with and
> without
> > > the
> > > > index to see if the index is in fact being helpful.

> > > > --
> > > > Brian Moran
> > > > CrossTier.com
> > > > MS SQL Server MVP
> > > > SQL Server Mag Columnist



> > > > > assume i have a tabel called Customer with the following column

> > > > >  cuLastName, nvarchar(30)

> > > > > and that there is an index on the column.

> > > > > Can anyone explain why SQL Server does a table scan for
> > > > >  SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

> > > > > but does an Index seek/scan for
> > > > >  SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

> > > > > the addition of one more letter triggers SQL to use the index
where
> > > > > i think the index should be used in both situations......

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Tibor Karasz » Fri, 25 Aug 2000 04:00:00


Absolutely!
(I'm quite impressed that you made that conclusion that quick :-)

And... Same goes if you would change the index to a clustered index (no need to "jump
to datapages" for each row).
--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.


Quote:> Hi Tibor
> This would imply that if i change the query to

>     SELECT cuLastName FROM Customer WHERE cuLastName LIKE 'k%'

> SQLS7 should do an index seek, because all of the data it's looking for
> (cuLastName)
> is in the index pages........

> I will check this out and see what the result is.....

 
 
 

TABLE SCAN Vs INDEX SCAN/SEEK

Post by Jimmy Kavanag » Fri, 25 Aug 2000 04:00:00


Stop! im blushing......

Thanks Tibor. Im not new to this game - i have been a programmer for many
years, but i am
brand spanking new to SQL Server and SQL databases in general.
Trying to get my head around ADO, T-SQL, SQL-DMO at the moment.

Thanks for the advice  - Jimmy


> Absolutely!
> (I'm quite impressed that you made that conclusion that quick :-)

> And... Same goes if you would change the index to a clustered index (no
need to "jump
> to datapages" for each row).
> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.



> > Hi Tibor
> > This would imply that if i change the query to

> >     SELECT cuLastName FROM Customer WHERE cuLastName LIKE 'k%'

> > SQLS7 should do an index seek, because all of the data it's looking for
> > (cuLastName)
> > is in the index pages........

> > I will check this out and see what the result is.....

 
 
 

1. Clustered Index Scan vs. Table Scan

This is a follow up post to the "Are Table Scans Always Bad?" post I posted
last week. I am in the process of re-indexing some tables, and I have been
cleaning things up so execution plans show no table scans. Performance seems
to be helped not hurt, so I was just curious, is it safe to say:

A Clustered Index Scan is always better then a Table Scan?

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

2. Sr. DBA / Systems Architect: Contract or Perm

3. Index scan vs Full table scan

4. excluding variables from a query

5. Index Full Scan Vs. Index Range Scan

6. Help! Drop / Create Table Problems

7. Index Fast Full Scan vs Index Full Scan?

8. Finding Composite Keys in Informix?

9. Table scan, Table scan, Table scan

10. Table scan vs index seek

11. Index Seek Vs Index Scan

12. Index Scan vs. Index Seek

13. clustered index seek vs. Scan