Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
Prabhat
Hi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>
|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with[vbcol=seagreen]
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same
>
|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>
|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the[vbcol=seagreen]
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on
>
|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||Prabhat wrote:

>Hi Steve,
>My requrement is to search for "nath" (I know both the queries are
>different). In 1st case i am listing All > nath and in 2nd case like nath.
>That Does not matter.
>
But that's why the estimated execution costs are different. You haven't
shown the plans, but the plans may be identical, and just have different
costs because of the difference in the estimated number of rows
returned. If the queries return the same results, it's possible that
the actual running times are the same. Have you run the queries with
set statistics io on to see if there's a difference?
Sorry, but I still don't understand why if you want to search for
"nath", you are comparing plans that do something else.
SK

>Suppose I have 2 same queries with 2 diferent approach with that 2 Execution
>Plan, Then Which Plan I should go for?
>Some Additional Hint:
>1st Query Cost 98.35% relative to the batch
>while the 2nd Query Cost 1.65% relative to the Bacth.
>Thanks
>Prabhat
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eupyWfOwEHA.2624@.TK2MSFTNGP11.phx.gbl...
>
>country,
>
>country,
>
>refers
>
>
>