Re: Index Seek (or) Index Scan in Execution Plan

From: Prabhat (not_a_mail_at_hotmail.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 22:12:53 +0530

Hi David,

Thanks for the Details. I have seen that WORD Many places but did not know
about that. Thanks again.
Prabhat

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:u#tfycPwEHA.1148@TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> > 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:
> >>> 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
>
> A covering index has all the requested columns listed everywhere in the
> query in an index. Because all the data requested (or used in the WHERE
> clause, etc.) is in the index, SQL Server does not have to go to the
> underlying table to get the actual data, thus eliminating the bookmark
> lookup operation. Plus, even if an index scan operation is used,
> scanning an index is normally much faster than scanning the underlying
> table because it is much smaller.
>
> You can test this and see using a simple two column table with an INT
> IDENTITY and a second column( anything you want). Insert a 1,000 rows or
> so into the table using a loop. First, run a simpel query on the table
> for a single identity value and examine the execution plan. You'll see a
> table scan operation. Then add a clustered index to the identity column
> and you'll see a clustered index seek. Then drop the clustered index and
> add nonclustered on the identity and you'll see an index seek followed
> by a bookmark lookup operation (you need to include the second unindexed
> column in the query to see this). Then drop the nonclustered and change
> it to include both columns and run the query and you should see an index
> seek without the bookmark lookup.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Relevant Pages

  • Re: Index Seek (or) Index Scan in Execution Plan
    ... > Prabhat wrote: ... >> Thanks David for your Suggestion. ... run a simpel query on the table ... > by a bookmark lookup operation (you need to include the second unindexed ...
    (microsoft.public.sqlserver.server)
  • Re: Open a form from a record in Querry result or Table
    ... David assume for our discussion that you have a form and it is set to ... datasheet view and this form is based on the select query you mentioned ... the first form so that it opens your tab form and only display the one ... Private Sub Form_DblClick ...
    (microsoft.public.access.gettingstarted)
  • Re: ADVANCED: Suppressing "Save Layout?" dialog on form close
    ... I have the "Allow Design Changes" property set to ... Since the fields inside the query always load with the same width, ... OnUnload events both on the query's datasheet and the form but when the ...
    (microsoft.public.access.formscoding)
  • Re: Correlated Query problem with large tables
    ... I get the green 'Running Query' message which fills to ... "david epsom dot com dot au" wrote: ... I'm trying find out stock numbers from ReducedStockList that Customers ...
    (microsoft.public.access.queries)
  • Re: Index View
    ... Start with the execution plan. ... Prabhat wrote: ... >> you can't use this for your query. ... >> Best, Hugo ...
    (microsoft.public.sqlserver.programming)