Just because you see an Index Seek operation, do not relax. All may not be well. Scan or seek, it all depends on the amount of work being done by SQL Server to fetch the data. Let’s say you have a non-clustered index on a column and the optimizer seeks on the index to fetch the data. If the predicate is highly selective (less number of records are returned), then most likely, things are well. If it’s low-selective (more number of records are returned), then even though it’s an index seek operation, it might be touching a larger portion of the leaf level increasing the IO. Therefore, seeking performance is directly proportionate to the number of pages the storage engine is touching. On another note, multi-column indexes may be very tricky. Depending on the predicate operator, the optimizer may be seeking on the first column of the index, but not the second column. In this case, the first column is the SEEK predicate while the second column is the residual predicate. The execution plan is non-intuitive and such nuances are not clearly visible. So, the summary is: when you see an Index Seek operation, look for finer details like IO cost (reads, etc), cardinality estimation & predicates. Sounds interesting? Watch the video below.
Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.
Thanks for reading & watching. If you liked the content, do share it. I am on Twitter if you wish to connect.
You can also subscribe to my exclusive newsletter ConnectWithAB.