Re: Index Seek (or) Index Scan in Execution Plan
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 11/02/04
- Next message: Shaun: "Help !! Nested Stored Procedure, is this the best way?????"
- Previous message: Steve Kass: "Re: Comparision on Char Column With Alphanumeric Values"
- In reply to: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Next in thread: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Reply: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 2 Nov 2004 11:15:36 -0500
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
- Next message: Shaun: "Help !! Nested Stored Procedure, is this the best way?????"
- Previous message: Steve Kass: "Re: Comparision on Char Column With Alphanumeric Values"
- In reply to: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Next in thread: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Reply: Prabhat: "Re: Index Seek (or) Index Scan in Execution Plan"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
