One of my friends ask me about index scan/seek and when it happened. This article give an idea related to index scan and index seek.
Index scan
Index scan means the SQL server reads all rows on the table and returns those rows that satisfy the search criteria. All the rows of the left leaf of the index are scanned. That means all the rows of the index is searched instead of table directly. Please don't confuse with table scan. The time this takes is proportional to the size of the index. Generally the index scan is occurred when the query not finding a suitable index for a particular columns of the table.
When it used?
It is preferable when the table is very small and using index is over head
When a large percentage of the records match with searched criteria (10-15%).
Index seek
The SQL server uses the B-Tree structure of index to seek directly to the matching records. Time taken is only proportional to the number of matching records.
When it used?
Index seek is preferred when the number of matching records is proportionately much lower than the total number of records (greeter then the 50%).
Here is a general example where Index scan is used.
The Architecture
Table-A Contains 5 columns (Col-A, Col-B, Col-C, Col-D, Col-E)
The Index named Index-1 is activated on Table-A Col-A and Col-E
The Query
A SQL Query is fired with
WHEN Col-A='xxxx' AND Col-C='yyyyy'
The Output
As because the Index named Index-1 is on COL-A and COL-C and in the SQL statement we used COL-A and COL-C, it generate the Index Scan not Index Seek
SQL Server 2008 introduces a new hint, the FORCESEEK hint that allows to "kindly"
suggest Query Optimizer to use a seek operation instead of a scan.
suggest Query Optimizer to use a seek operation instead of a scan.
.
Example:
SELECT *
FROM Table-A AS h
INNER JOIN Table-B AS d WITH (FORCESEEK)
ON h.ID = d.ID
WHERE h.DueAmt > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000);
Hope the article is quite informative and thanking you to provide your valuable time on it.
Posted by: MR. JOYDEEP DAS
An index scan is generated when an index has more than one field defined in the index and any field other than the first one is used in your selection criteria. Or when none of the fields are used in selection criteria but only fields defined in the index are used in a result set. The term used for the second case is a "covering index" and the table is never touched to get the results.
ReplyDeleteI'm not exactly sure what is meant by "left leaf", with a scan generally all leaves are read. The exception is included in the article where the first and third field in a 5 field index is used in the search. It's called a scan, but it is a modified hybrid of seek and scan. It will only touch index leaves that hold the first field's value, but it will scan all the leaves that do hold its value. So, if the first field's value identifies at most 10% of the index, only 10% of the index will be touched but every leaf that holds that first value is scanned.
Thanks "Ken"
ReplyDelete