August 4, 2008 at 2:57 pm
HI,
I have a field, i have defined a non-clustered UNIQUE index on this field.
Now in some queries sql server is doing index scan on this field while in some queries it is doing index seek on this field.....why is that so? any idea??
also on a general note.........when does it do index scan?.........and.......when index seek?
is the Unique constraint impacting any thing?
August 4, 2008 at 3:01 pm
The Optimizer chooses the near best way to get the data. Depending on the statistics for the index/column and the criteria entered. Now you have specified a non-clustered unique index so if you have where column_name = @variable, you will likely get a seek, while searching on a range (column between a and b) will get a scan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 5, 2008 at 7:44 am
It also depends on how much data will be returned. Usually when a lot of data will be returned it is faster to do a scan (even a table scan). This is because table scans will eliminate the need to do a Bookmark Lookup in order to get the non-indexed columns.
September 5, 2008 at 11:01 am
Jack Corbett (8/4/2008)
while searching on a range (column between a and b) will get a scan.
If either the start or the end of the range is sargable, a range search may still result in a seek. A scan operator means that the entire index/table was scanned. If the index tree is used at all, even if there's a partial scan involved, it appears as an index seek in the query plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2008 at 11:26 am
So, how can you determine if there is a partial scan when the plan is showing an index seek? Will that show up when using set showplan_full on? Curious. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 5, 2008 at 11:58 am
It won't. All the versions of showplan show the same data, just the methods of storing and showing it change.
Look at the seek predicates. If you've got a partial scan, you'll see something like this in the seek predicate.
Start Range: SomeDate >= '2008-01-01 00:00:00.000'
End Range: SomeDate < '2009-01-01 00:00:00.000'
(2008's slightly different. It appears just as Start: SomeDate >= '2008-01-01 00:00:00.000' and End:SomeDate < '2009-01-01 00:00:00.000')
I have an example of an exec plan like that - Example exec plan[/url]
interesting thing is, even if all of the leaf pages are included in the criteria, as long as the index tree is used it qualifies as a seek. Like in this amusing example
CREATE TABLE SeekOrScan (
ID int identity (1,1),
Somename varchar(50)
)
create index idx_SeekOrScan on SeekOrScan (ID)
insert into SeekOrScan (somename) select name from sys.columns
select ID from SeekOrScan where ID > 0
Now, since it's an identity column started at 1, we know that that there are no values below 1. Hence that query will return all the rows in the table. It still shows up as an index seek.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2008 at 12:01 pm
Nice! More stuff to look at in my tuning efforts now. Thanks for the help and I will review your blog post for more details!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 5, 2008 at 7:17 pm
I think it also depends on the number of records present in the table. IF fewer records are there i think the optimzer would prefer a scan rather than a seek as that would be cost effective.
[font="Verdana"]- Deepak[/font]
September 6, 2008 at 4:07 am
Deepak (9/5/2008)
I think it also depends on the number of records present in the table. IF fewer records are there i think the optimzer would prefer a scan rather than a seek as that would be cost effective.
It's usually the other way round, though only if a bookmark lookup is required. The optimiser will only pick a plan with a bookmark lookup if it feels that the number of rows that will need to be looked up is somewhere in the range of 0.1% to 1% of the table. (There are other factor that affect the exact percentage)
If the query is covered by a nonclustered index, then there are no such concerns about the lookups and the seek is the normal operator.
I tested out my query on 3 rows and on 2 million. In both cases the exec plan showed an index seek, not a scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2008 at 2:47 pm
i agree with gail
September 9, 2008 at 8:07 am
It's usually the other way round, though only if a bookmark lookup is required. The optimiser will only pick a plan with a bookmark lookup if it feels that the number of rows that will need to be looked up is somewhere in the range of 0.1% to 1% of the table. (There are other factor that affect the exact percentage)
I thought the number was around 8%, plus or minus.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 9, 2008 at 1:18 pm
I got those numbers from Kimberly Tripp, and she did demos at the time that confirmed the aprox percentage where the switch was.
It does vary, as it depends on where the optimiser thinks the break-point is between the random IOs on the bookmark lookup and the sequential on the scan
I'll conjure up a test, but it won't be tonight.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply