December 7, 2007 at 5:44 am
What is the difference between index seek and index scan?
December 7, 2007 at 6:35 am
an index seek is better...an index is stored in order. so if a query tries to look for say, PROD_ID=7, it knows to use the index to SEEK the PROD_ID of 7, so it's the fastest method of all.
an INDEX SEEK might be something like it's looking for PRODUCTNAME="BANANAS", and knows that following the index to find(seek) to get the leaf of information that is related to what it is looking for is the next best method.
if there was a different index on PRODUCTNAME, it might use that instead, but it will try to use the clustered index(in this example on my primary key PROD_ID) most of the time.
in this example, dropping the clustered index and recreating it to have two columns, PROD_ID and PRODUCTNAME might increase performance if there's alot of queries that include the product name
Lowell
December 7, 2007 at 6:51 am
A seek means that you are looking for specific value(s) and the index provides you with the best way to do it. This also applies to specific ranges of data.As Lowell described it's very fast.
A scan indicates that the entire table/index is read in. A scan isn't always bad, because it it might just mean that the data is organized the way your query needs it (meaning - there's no data to exclude, and the data is organized the way the query optimizer needs it). It might however also mean that there IS a criteria, but that can't be satisfied using SEEKS. Because of the less specific nature of the operation, it tends to be a little slower, and thus often described as "not as good". It may however be the best operation specific to what you ask for.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 7, 2007 at 6:51 am
December 10, 2007 at 4:25 am
whether index scan reads the data from index or table? if it reads from the table then there is no advantage of this index scan because , it is reading the data in the normal manner that is from the table and performance will not be improved......
If i'm misleading, correct me!!!!!
December 10, 2007 at 5:31 am
Read Indexes from SQL Server BOL.
Links :
http://msdn2.microsoft.com/en-us/library/aa174541(SQL.80).aspx
"More Green More Oxygen !! Plant a tree today"
December 10, 2007 at 6:12 am
Ananth (12/10/2007)
whether index scan reads the data from index or table? if it reads from the table then there is no advantage of this index scan because , it is reading the data in the normal manner that is from the table and performance will not be improved......If i'm misleading, correct me!!!!!
An index scan is a complete scan of all the pages in a non-clustered index.
A clustered index scan is a complete scan of all pages in a clustered index (ie, the table itself)
Neither scan uses the b-tree structure of the index, but just reads the leaf pages in order, using each page's reference to the next in the chain.
An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.
A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf.
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
May 5, 2010 at 1:27 am
Is there any difference between table scan and index scan?
Cheers
May 5, 2010 at 1:44 am
Yes.
A table scan (only present when you don't have a clustered index) is a scan of the data pages of the table.
An index scan reads the leaf pages of the index. If it's a nonclustered index, it'll be a lot fewer pages than the table.
A clustered index scan (only present if there's a clustered index) reads the leaf pages of the clustered index, which are the data pages of the table. It's virtually the same same as a table scan. There are some minor differences as to how SQL does the scan, but it is the entire table, like with a table 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
May 5, 2010 at 1:55 am
got the difference .. thankx...:-)
Cheers
October 16, 2012 at 3:26 am
While troubleshooting a poor performing query this morning I came across something which surprised me - actually, two things:
1) one of the tables being queried defined no primary key or clustered index
2) the optimiser chose an index seek over an index scan for the query
For argument's sake, let's put aside the first shocking point for now. In fact, look past all the poor design decisions. It's the optimiser's behaviour in the given scenario which interests me.
The heap being queried comprises ~9.5M records in a single partition occupying ~36GB of space or ~5M pages (it's a wide denormalised table). A non-clustered index exists on a char(1) column called 'TransType' with density of 0.25:
RANGE_HI_KEY EQ_ROWS
9,581,682
A 264
H 263
X 98
It also defines 4 INCLUDED fields: PolNo, PolSeq, PolYear, PolMonth and occupies 3 levels over 41,482 pages.
Finally, here's the portion of the query which surprised me:
SELECT
PolNo,
PolSeq,
PolYear,
PolMonth
FROM
dbo.PolHistory
WHERE
TransType = ' '; --41,575 logical reads
The resulting plan consists of a single Index Seek operator. The index stats utilised a FULLSCAN and are up-to-date.
Sure, the index is covering, but given the value being seeked has such poor selectivity, why isn't a scan of the leaf nodes chosen over 9.5M traversals the index structure?
October 16, 2012 at 4:16 am
The index is covering, so one seek operation to the start of the range and a partial scan is all that's necessary.
It's not traversing the index 9.5 million times. If it was, the logical reads would be ~27 million. It's traversing the index tree once, reaching the start of the range of rows that match then reading along the index leaf level until it's got all the rows. It's more efficient than a scan as it doesn't have to read the (few) rows that don't match.
p.s. Please post new questions in a new thread in future. Thanks
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
October 16, 2012 at 5:58 am
GilaMonster (10/16/2012)
The index is covering, so one seek operation to the start of the range and a partial scan is all that's necessary.It's not traversing the index 9.5 million times. If it was, the logical reads would be ~27 million. It's traversing the index tree once, reaching the start of the range of rows that match then reading along the index leaf level until it's got all the rows. It's more efficient than a scan as it doesn't have to read the (few) rows that don't match.
p.s. Please post new questions in a new thread in future. Thanks
I followed this up with a private message to Gail as follows:
Thanks Gail
That makes a lot of sense.
Are there instances when cardinality does play a part in the optimiser's choice of 'scan vs seek' in the presence of a covering index and all SARGable predicates?
Thanks again
Andy
To which she answered:
No.
The only reason cardinality can flip a seek to a scan of the cluster is because of the cost of the key lookups. A covering index doesn't need key lookups and so there's no reason at all to switch to a more expensive scan.
p.s. can you post your PM and my reply in the forum thread, for anyone who encounters it via google
June 11, 2013 at 2:19 am
This blog http://www.crazyfrog.me/2013/06/index-seek-vs-scan-microsoft-sql-2008.html has detailed explanation of Index seek and scan.
June 11, 2013 at 2:23 am
domalti (6/11/2013)
This blog http://www.crazyfrog.me/2013/06/index-seek-vs-scan-microsoft-sql-2008.html has detailed explanation of Index seek and scan.
Not sure I'd call a bunch of pictures with no descriptions or explanations 'detailed', but that's just me.
p.s. 6 year old thread
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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply