September 2, 2016 at 7:51 am
I have a process that is pretty simple in that it left joins two tables, A left joined to B. I have an non-clustered index on A that causes an index seek in the execution plan, however, the execution plan is recommending another non-clustered index be added to table A for a 61% improvement. Why would the engine think that an index seek needs improvement? I created the recommended index and now the engine uses the original index but is doing an index scan but doesn't recommend anything. I was taught that index seeks are as good as it gets. What am I missing?
September 2, 2016 at 8:04 am
Index seeks are not always the best option, especially when they're accompanied by a key lookup. Sometimes, an index scan is better when a wide range of rows is selected.
Please read the following article for a more specific answer: http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2016 at 1:00 pm
Your article link goes to an article on how to post about performance problems. It does not talk about why the engine might choose to suggest an index that it will scan over an index seek. There are no key lookups on the plan. That would have already been a sign to me that something was wrong. But thank you for replying anyway. why would a large range of records cause a scan to be better?
September 2, 2016 at 1:11 pm
I pointed to that article because if you share the details mentioned in there, it would give us an idea of what's happening. Without details, we're just guessing.
On the index scan vs seek, here are some reasons:
From the article
for small tables data retrieval via an index or table scan is faster than using the index itself for selection. This is because the added overhead of first reading the index, then reading the pages containing the rows returned by the index, does not offer any performance improvement for a table with only a few rows.Other reasons to use an index scan would be when an index is not selective enough, and when a query will return a large percentage (greater than 50%) of rows from the table. In such cases the additional overhead of first using the index may result in a small degradation of performance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply