May 12, 2006 at 1:01 am
I am looking for some documentation on how to frame a query so that it uses Index seek rather than Indes scan. Exactly how dose SQL Server decides what to use - Index Seek or Index Scan?
Can anybody please help?
May 12, 2006 at 10:50 am
It uses histogram statistics. These are samples of the data throughout the table at regular intervals. The values at each sample enable the optimizer to get a view of the approximate number of rows that a particular search argument value in a query will match.
When a query is passed to the optimizer at runtime, it consults the statistics, and if it estimates that only a few records will match, then it will use a suitable index if one exists.
December 16, 2008 at 4:37 am
Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.
Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.
December 16, 2008 at 4:47 am
Amit Garg (5/12/2006)
I am looking for some documentation on how to frame a query so that it uses Index seek rather than Indes scan. Exactly how dose SQL Server decides what to use - Index Seek or Index Scan?Can anybody please help?
Depends on the query - not all where clause predicates are SARGable, able to use index seeks. If you've got functions on the columns or implicit conversion, then SQL has to scan.
Depends on the indexes. If SQL has to do bookmark lookups (ie the indexes aren't covering) then it will rather scan if you select more than about 1% of the table.
Post the query, the table schema and index definitions and maybe we can tell you why you're getting scans.
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
December 17, 2008 at 12:57 am
Some times, you can force an index seek without having to write any query hints.
In some cases you can add
AND Col1 > 0
if Col1 has positive values only.
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply