Why am I seeing such an expensive index scan on tblMainAccounts

Andre Ranieri 2013-07-02 20:50:53

I'm a first timer to SQL Sentry Plan Explorer, but I immediately noticed that my plan includes a very expensive index scan of the entire tblMainAccounts table. The estimated row count is 330,000 yet the actual row count is 1. Statistics are all updated as of two nights ago. Why is it unable to do a nonclustered index seek of this table?

SQLkiwi 2013-07-03 01:09:58
### The Scan

It's not that it is unable to perform a seek, the optimizer considered both alternatives and concluded that the merge join would be cheapest. This decision is influenced by the FAST 100 query hint. You are asking the optimizer for a plan that will produce the first 100 rows quickly (though the overall time to process the query might be longer than without the hint). If you are not sure why the hint is there, remove it and see what effect that has. Hints should only be added as a last resort, to fix a specific problem, and when the consequences are fully understood.

The Estimate

An index scan does not necessarily mean a full index scan is always performed. Query plans (generally) stream a row at a time through operators (pipelined execution). Merge join stops processing rows from its inputs as soon as either one runs out of rows. In this particular case, the other input to the merge join produced no rows, so the Index Scan stopped early.

Andre Ranieri 2013-09-06 22:23:14
Paul – I don't know why I didn't see your reply initially. My apologies, and a belated thank you.

Cheers,

Andre Ranieri

SQLkiwi 2013-09-18 09:39:52
No worries, you're welcome. Thanks for a good question.