Full Scan, Partial Scan, Index Seek in Performance Perspective
Full Scan, Partial Scan, Index Seek in Performance Perspective
By : Kasim Wirama, MCDBA
This article, I would like to share about scanning and seek in SQL Server. Those three terminologies from above title contribute significant database performance. You can see the information from textual or graphical execution plan. Each step is represented by operator in execution plan. Full scan is represented by Clustered Index Scan, or TableScan operator. Index seek could be in clustered/nonclustered index seek. And partial scan is between two of them.
Clustered Index scan is considered good when you access all or almost all data, so SQL Server engine access the table directly rather to index and expensive bookmark lookup. As information, bookmark lookup is found at SQL Server 2000/7.0, and new name for the operator in SQL Server 2005 is Key Lookup. If you have no clustered index, the operator name is RID Lookup instead of key lookup. A table that has no clustered index is called heap table. A table will perform better if it has clustered index, especially there is extensive update activity rather than heap table.
Other operator is seek operator. Generally seek operator is considered more efficient than scan operator because it doesn't have to access from beginning item to end of item of an index. For selective item (returning small result from a big table), it has good performance if SQL Server chooses index seek rather than index scan.
Actually there is a subtle operator, it is partial scan. A partial scan is scanning within a range, but no all record in that range is really relevant to search query. If you see in execution plan, they are identified as index seek, but if you examine more detail in text-based execution plan, usually it has "residual" keyword. Partial scan will perform a bit less efficient compared to index seek when the number of relevant rows is almost as many as number of rows in end results and vice versa.
In real scenario, from my experience, it is hard to achieve index seek for all problematic query, so usually I try to achieve partial scan if seek is not possible in some cases.