Understanding Index Scan and Index Seek

Understanding Index Scan & Index Seek


When we examine the execution plan then we find operators like index scan and index seek, let see what it is

Index Scan – It means SQL Server reads all rows on the table from first data page to last data page and shows rows that satisfy the search criteria. Index scan happens when query optimizer unable to find useful index to locate particular record or the query is fetching large amount of data which means more than 50 percent of table data. The query optimizer may choose index or table scan in fact for small table’s data retrieval via index or table scan is faster than using the index itself for selection. This is because the added overhead of first reading index then reading data pages doesn’t increase any performance.


Index Seek – The SQL Server uses B-Tree structure of index to seek directly to the matching records. An index is most beneficial while retrieving 10-15% of data from the whole table. Query optimizer try to use index seek which indicate it found useful index to fetch the desired records, if it fails to use index or the amount of data it is returning is proportional to table rows then it uses index scan.