Index Seek

Question

Index Seek

Accepted Answer

Interviewers love this one because it reveals whether you understand how the database avoids reading everything. Question: What is an index seek? Answer: An index seek is when the database uses an index to jump straight to the matching rows, instead of scanning the whole table. Think of the index like a sorted lookup book: the engine finds the starting point quickly, then reads only the needed key range. It is usually fast when the filter is selective, such as = or a prefix search like LIKE 'Sm%' . Interview-Ready Answer: I would say an index seek is when the optimizer uses an index as a shortcut to jump to the exact key or key range I asked for, rather than reading the whole table. In SQL Server, that usually means fewer logical reads and much less I/O. It works best on selective, sargable predicates, and if the index covers the query, it can avoid extra lookups too. What it means An index is a separate data structure that keeps keys in sorted order. In many relational databases, that structure is a B-tree or B+ tree: a balanced tree that lets the engine move from the top to the right leaf page quickly. A seek means the engine does not start at row 1; it navigates directly to the starting key, then reads only the matching range. How it works under the hood The optimizer, meaning the cost-based query planner, looks at your WHERE , JOIN , and ORDER BY clauses and checks the table statistics. It asks: can this predicate be turned into a direct search on an index? If yes, it is sargable (search-argument-able), which means the database can use the index efficiently. The engine walks the B-tree from root page to branch page to leaf page. In SQL Server, pages are typically 8 KB, and a large index still often has only 3 to 4 levels, so the traversal is very short. Once it reaches the leaf level, it reads the matching key or key range. For example, LastName = 'Smith' seeks one point; LastName LIKE 'Sm%' seeks a range starting at Sm . If the query asks for columns not stored in that index, the engine may do a key lookup (also called a bookmark lookup): it follows a pointer back to the base row to fetch the missing columns. After the seek, the engine may still apply a residual predicate , which is an extra filter that could not be handled by the index alone. Seek vs scan vs lookup Plan What it does Best for Weak spot Index seek Jump to matching keys Few matching rows Bad if many rows match Index/table scan Read everything Large result sets Expensive for small filters Key lookup Fetch missing columns Small result after seek Slow when repeated a lot When and why to use it Design indexes on columns you filter, join, or sort by often. A seek is most valuable when the predicate is selective, meaning it matches a small fraction of the table. For a million-row table, returning 20 rows with a seek is usually great; returning 800,000 rows may be cheaper as a scan because the engine avoids thousands of random row fetches. Big idea: a seek is not “the best plan” by default; it is the best plan when the index can narrow the search quickly. The optimizer chooses based on estimated cost, not on the mere presence of an index. Performance notes and edge cases Complexity: the search part is roughly O(log n) , then reading matching rows is O(k) for k matches. A scan is closer to O(n) . Low selectivity: if 40% of rows match, a scan may beat a seek because sequential reads are cheaper than many random reads. Non-sargable predicates: functions on the column, implicit conversions, and leading wildcards often block a seek. Example: UPPER(LastName) = 'SMITH' usually cannot use a normal seek on LastName . Composite indexes: the order matters. A seek on the first key column is easy; skipping the leading column often prevents a true seek on the rest. Covering indexes: if all needed columns are already in the index, the engine does not need extra lookups, which can make a seek dramatically faster. Real-world story Imagine a checkout service on an e-commerce site that lets support agents search orders by customer last name. The orders table has 50 million rows, and the team adds an index on last_name . One day someone changes the query to WHERE UPPER(last_name) = 'SMITH' so the search becomes case-insensitive. That tiny change removes the index seek and turns the query into a scan. What happens next? CPU jumps, logical reads explode, and the page that used to load in 80 ms now times out after 15 seconds. In the query plan, the DBA sees a scan instead of a seek, and the app logs show timeout errors during peak traffic. The fix is to rewrite the predicate into a seekable form, or add the right computed/indexed support so the database can search without reading the whole table. What goes wrong when people misunderstand this: they assume “there is an index, so it must be fast.” In production, the symptom is often slow pages, high I/O waits, and user complaints like “search is stuck.” The root cause is usually not the index itself, but the shape of t

Plan	What it does	Best for	Weak spot
Index seek	Jump to matching keys	Few matching rows	Bad if many rows match
Index/table scan	Read everything	Large result sets	Expensive for small filters
Key lookup	Fetch missing columns	Small result after seek	Slow when repeated a lot