Search This Blog

Friday, September 28, 2007

Why SQL Optimizer uses table scan when an index is available?

Sql server is smart enough , that its optimizer determines which execution plan costs lesser and if choosing index is much costlier than table scan , it would opt for table scan.

This happens in following situations.
1. Index keys are very wide and they are distributed arbitrarily or to be precise statistics are outdated,hence use UPDATE STATISTICS to update the same.

2. Estimated rowcount is more than 5 % of the table data.

No comments: